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

Answers


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


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.


Need Your Help

Convert a image handler from ASPX to asynchronus ASHX

asp.net asynchronous httphandler

Im struggling with bad performance on one of my websites. It is handling a lot of images, and I serve images through an image handler. When I created it I did a mistake and created a ASPX file to h...

Converting java.sql.Date to java.util.Date

java jdbc

What's the simplest way to convert a java.sql.Date object to a java.util.Date while retaining the timestamp?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.