Re: Index questions
> 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? The issue you are dealing with is skewed data. Most rows have 'N', a few have 'Y'. Your data is not evenly distributed ... it's skewed. Bitmap indexes are a solution, but not a solution for this issue. What you are looking for is histograms. If they won't work for you (because you're using bind variables) an alternate solution is to code two SQL's in PL/SQL using an IF statement ... IF :x = 'N' then ... execute the SQL that's hinted to not use the index, ELSE execute the SQL that's hinted to use the index. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Index questions
Thanks Don, Your explanation is very helpful.
--- Don Granaman <[EMAIL PROTECTED]> wrote:
> 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"
> <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
__
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: Index questions
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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: Index questions
sorry, CBO(Cost-based Optimizer). --- Greg Moore <[EMAIL PROTECTED]> wrote: > What's CBT? > > > If we have a column of 'Y','N' values, the index > will > > not help with CBT(usually most of the values are > 'N'). > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Greg Moore > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). __ 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Index questions
What's CBT? > If we have a column of 'Y','N' values, the index will > not help with CBT(usually most of the values are 'N'). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Index questions
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
