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.
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.