| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ** bitmap index for lot of nulls
Hi!
If you insert rows which column values for this particular index are all nulls, then insert performance shouldn't be affected, at least not from IO point of view (best execution plan calculation could get a tiny bit slower, but that's irrelevant here). I checked it with 10046 trace with waits, and if i inserted null to a table, then it's index segment wasn't accessed at all. But again, this index can be useful only for queries where you either specify that indexed column is not null or use any other predicate which rules out the null option.
Tanel.
Thanks Tanel. Point taken.
Related question : Since this table is going to have a lot of nulls for this indexed column how will it affect performance for insert in to the table. Is it safe to assume that there will be no performance impact as a result of this additional index. Thank You
Tanel Poder <tanel.poder.003_at_mail.ee> wrote: Hi!
Bitmap indexes are suited best for low cardinality columns in tables which are read only, such is gender, marital status or similar (a bitmap is needed for every different value). Since your non-null rows are unique, bitmap index isn't good choice.
Note that a regular single column index won't index any nulls. (In your 95% case it's not a problem, because if you'd search nulls, a table scan would be cheaper anyway, but if you want nulls to get indexed, you could create a composite index with your column + another not null column).
Tanel.
Hi,
I am creating an index on a column that is null in most (over 95%) of the rows. In the rest of the rows it is expected to be unique. Is bitmap index suited for this? Thanks You.
--------------------------------------------------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
------------------------------------------------------------------------------
Do you Yahoo!?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tanel Poder
INET: tanel.poder.003_at_mail.ee
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------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 Aug 04 2003 - 14:34:38 CDT
 |
 |