Seema,
It would really help to see your index scripts also. If you have an index that includes emp_id, reg_date, emp_st, last_access, emp_status, and match, that index should work best. Use a hint on that index.
You say that this query hits a number of indices on this table. Do you have the explain plan that shows which and how the indexes are hit? You can't assume that just because you have an index that it is being used. If you have a bunch of indexes each on a different column or two, the indexes are probably not doing this SQL any good and surely making inserts and updates take a lot longer.
Also how big is the table? How big is it expected to become? When's the last time it was analyzed?
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-----Original Message-----
From: Seema Singh [SMTP:[EMAIL PROTECTED]]
Hi
I am executing following query adn this query hits a number of indices on
this table.let me know what is wrong please.all in where clause are having
indexes.
select name,last_access, reg_date from empmaster where emp_id<1000000
and reg_date>to_date('2001-01-01','YYYY-MM-DD') and
emp_st='valid' and last_access>to_date ('2001-01-01','YYYY-MM-DD')
and emp_status='S' and match='FIRST'
Here all conditions in where clause are having indexes.
How to rewrite this query.
The primary key is emp_id.
Thanks
-Seema
