Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Problems with SQL analyze tool

From the SQL analyze tool I am geting index unique scan of pk_ecode, which is the index name on employee table. Can you please explain why index unique scan is being used as step 1?

SELECT st.*, ee.fname || ee.lname "NAME"
FROM
      (SELECT ecode, on_date, basic, hra, convey_alwn, other_alwn1,
      other_alwn2, other_alwn3, other_alwn4, spl_earn, pf_employee,
      edu_allow, ptax, tax, sal_advance, other_deduc, loan, 
      spl_deduction, taxcode, hrapercentageyn
      FROM salary_template) st,
      employee ee,
      (SELECT ecode, MAX(on_date)
      maxdate FROM salary_template GROUP BY ecode) zz
      WHERE st.ecode = zz.ecode
      AND st.on_date = zz.maxdate
      AND st.ecode = ee.ecode
      AND st.ecode = '1'

table salary_template has a composite primary key(ecode,on_date). Table employee has a primary key(ecode). From the SQL analyze tool I am geting index unique scan of pk_ecode, which is the index name on employee table. Can you please explain why index unique scan is being used as step 1 though? I have not used ee.ecode='1' where ee is the table alias of employee.

Even though you're not explicitly asking for

 ee.ecode='1'

you are effectively doing so by

 st.ecode = ee.ecode
 AND st.ecode = '1'

The optimizer tries to find the most efficient plan. If you like, you can force use of the other table's index by inserting a hint in the query, and see if that plan is any faster.

Also, make sure that you have up-to-date statistics on both tables and their indexes.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close