RE: Index performance

From: G Sanjay <g_sanjay_at_mks.panasonic.com.sg>
Date: Tue, 23 Jul 2002 19:28:21 -0800
Message-ID: <F001.004A060E.20020723192821@fatcity.com>

What is the selectivity of the columns emp_st, emp_status and match? If there are just 3-4 distinct values in the column, do not use b-tree index on them. Just drop the Indexes on those columns and try again. If still slow then try bitmap indexes on those low selectivity columns.

Sanjay

-----Original Message-----

Sent: 23 July, 2002 11:24 PM
To: Multiple recipients of list ORACLE-L

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

---

Chat with friends online, try MSN Messenger: http://messenger.msn.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Seema Singh
  INET: oracledbam_at_hotmail.com

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: G Sanjay
  INET: g_sanjay_at_mks.panasonic.com.sg

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). Received on Tue Jul 23 2002 - 22:28:21 CDT