SQL tuning issue

Question

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 (%CPU)| 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?

Answer 1

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.