RE: Index performance

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.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Seema Singh [SMTP:oracledbam_at_hotmail.com]
>
> 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).