Oracle-L: Re: Index questions

From: Don Granaman <granaman_at_cox.net>
Date: Mon, 15 Jul 2002 20:58:19 -0800
Message-ID: <F001.00498B55.20020715205819@fatcity.com>

Bitmapped indexes will be fast for the queryagainst the 'Y's, but a bitmapped index may not be appropriate if you are getting lots of DML activity on this.

Another option is to use NULL instead of the "vast majority" value ('N' - in this case) and create an index. The nulls won't be indexed, so the index will be small. This works best when there are relatively few non-majority values and they are the ones most critically/commonly queried. A hint MAY be required to make the CBO use the index. This obviousy won't help on queries against 'N' though. Example, ORDERS table with a STATUS column -

98% 'Closed', 2% ('New' | 'Pending' | 'Whatever...').  Change the logical to
interprete NULL as 'Closed' and create an index on ORDERS.STATUS.  Queries
for open orders - ('Closed' | 'Pending' | 'Whatever...') become very fast.

Don Granaman
[OraSaurus]

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, July 15, 2002 2:58 PM

If we have a column of 'Y','N' values, the index will not help with CBT(usually most of the values are 'N').

My question is : how about bitmap indexes? Should it help a lot, or just a little bit?

Thanks,

Chris Harvest

Do You Yahoo!?
Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: CC Harvest
  INET: ccharvest_at_yahoo.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: Don Granaman
  INET: granaman_at_cox.net

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 Mon Jul 15 2002 - 23:58:19 CDT