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?

Gary Myers · Accepted Answer · 2010-12-01 11:37:56Z

3

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.

answered Dec 1, 2010 at 11:37

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kupa Over a year ago

Yes these are tiny tables,that is why I can not identify which sql is better.Thank you for you advice to remove order by clause it really improved this query:):)(Cost now is 2) it is funny that i didn't notice it so far :D.I am searching sqls(especially top sqls) in my database executed by other users and my task is to optimize them as much as possible there are many sqls...And I am searching the way how to optimize,what to mention and so on..

Jagmag · Accepted Answer · 2010-12-01 07:31:38Z

2

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

answered Dec 1, 2010 at 7:31

Jagmag

10.4k1 gold badge37 silver badges60 bronze badges

5 Comments

kupa Over a year ago

Yes it has index on doc_type but when I hint it to use cost increases unfortunately,but I know why, because records in this table is just 30..So using index on the table with few records is not good choice.

kupa Over a year ago

In edition:unique index called "FILE_LOAD_PARAMS_PK" is created on the table "file_load_params" which is complex and containing (DOC_TYPE, PARAM) columns.

Jagmag Over a year ago

@kupa - is 30 or something in that range - the expected number of records for this table? If so, then the full table scan is not even an issue. Other than removing the order by (as you are doing a count in any case), i dont think there is any other optimization really needed at this point. How much time does your query take to execute currently?

kupa Over a year ago

It needs 0.063 seconds...You know my problem is that I can't identify how to optimize queries,is it necessary to optimize or is not.What else can be done and so on..I mean I want to know the steps to tune sqls..What aspects should i consider..an so on

Jagmag Over a year ago

With regards to fine tuning your queries especially, there are a lot of factors like load, realistic data etc which can be a huge factor. Considering the current execution timings, i would probably leave further fine tuning for this one based on actual feedback in a more realistic environment than your development db (assumption). Having said that, download.oracle.com/docs/cd/A97630_01/text.920/a96517/… is a good place to start for tuning related material in general

Cine · Accepted Answer · 2010-12-01 07:58:57Z

1

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

answered Dec 1, 2010 at 7:58

Cine

4,43930 silver badges50 bronze badges

Collectives™ on Stack Overflow

SQL tuning issue

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related