SQL tuning issue

I have a query:

select count(1) CNT
from file_load_params a
where a.doc_type = (select b.doc_type
                    from file_load_header b
                    where b.indicator = 'XELFASI')
 order by a.line_no

Which explain plan is:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%C开发者_C百科PU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                     |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL           | FILE_LOAD_PARAMS    |    15 |   105 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| FILE_LOAD_HEADER    |     1 |    12 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | FILE_LOAD_HEADER_UK |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

I thought that I could optimize this query and write this one:

select count(1) CNT
from file_load_params a,file_load_header b
where  b.indicator = 'XELFASI'
and a.doc_type = b.doc_type
order by a.line_no

Its explain plan is:

-----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                     |     1 |    19 |            |          |
|   2 |   NESTED LOOPS                |                     |    15 |   285 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| FILE_LOAD_HEADER    |     1 |    12 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | FILE_LOAD_HEADER_UK |     1 |       |     0   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | FILE_LOAD_PARAMS    |    15 |   105 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Is it good? I think not,but I expected better result...Do you have any idea?

From the explain plans, these appear to be tiny tables and the cost of the query is negligible. How long do they take to run and how quickly do you need them to run ?

But remove the ORDER BY. Since you are selecting a single row COUNT aggregate it is pointless.

One of the possible optimizations i see from your explain plan is

TABLE ACCESS FULL           | FILE_LOAD_PARAMS  

This seems to indicate that table file_load_params possibly does not have any index on doc_type

If that is the case, can you add an index for doc_type. If you already have indexes, can you post your table schema for file_load_params

The result is not the same for the two queries. The IN operator automatically also applies a DISTINCT to the inner query. And in this case it is probably not a key you are joining on (if it is, then make it an unique key), so it cannot be optimized away.

As for optimizing the query, then do as InSane says, add an index on Doc_Type in FILE_LOAD_PARAMS