Optimize this query?

Question

SELECT MAX (tran_date)
FROM   abc
WHERE  p_id = p_p_id
AND    flag = 'Y'
AND    (   (    p_c_number IS NULL
            AND c_number IS NULL
           )
        OR (c_number = p_c_number)
       )
AND    (   (    p_m_number IS NULL
            AND m_number IS NULL
           )
        OR (m_number = p_m_number)
       )
AND    (   (    p_s_number IS NULL
            AND s_number IS NULL
           )
        OR (s_number = p_s_number)
       );

I am using oracle as RDBMS ,i want to optimize this query

Plan SELECT STATEMENT ALL_ROWSCost: 357 Bytes: 39 Cardinality: 1
2 SORT AGGREGATE Bytes: 39 Cardinality: 1
1 TABLE ACCESS FULL TABLE abc Cost: 357 Bytes: 312 Cardinality: 8

Have you tried using your RDBMS's EXPLAIN or similar command to see the estimated execution plan of the query? Creating an index around most of these columns would probably help, and the query plan will help you see which ones. — cdhowie
@cdhowie: retrieving of execution plan in oracle is slightly more difficult than just prepending with EXPLAIN like it is in mysql ;-) But indeed, each optimization needs to be started with plan examination — zerkms
Right, I'm not pretending to be an Oracle expert, only indicating that looking at the query plan would be a better first step than asking SO. Providing the plan in the question would definitely be helpful. — cdhowie
@gaurav - Can you edit your post to include the query plan? That way you can format it so it's easier to read. — Justin Cave

Justin Cave Justin Cave · Accepted Answer · 2011-08-11T06:33:02

Can you post the query plan?
What indexes exist on the table?
Which of the references are column names and which are bind variables? My guess is that P_ID, FLAG, C_NUMBER, M_NUMBER, and S_NUMBER are columns in ABC and that P_P_ID, P_C_NUMBER, P_M_NUMBER, and P_S_NUMBER are bind variables but that's just a guess.
How selective are the various conditions? In particular, what fraction of the rows in the table have a particular P_ID value? What fraction have a FLAG of 'Y'?

Optimize this query?

1 Answers