AK,
I'd still do some testing with a bitmap index on that column.
You have 1,000,000 records. 500 records have value in that column.
That means the absolute most distinct values in that column is 501.
This is at least one criteria for bitmap candidacy.
What I see in testing is select * from tab
It means if I do a query with where clause with A=:x its going to go for
index .
Does oracle go to table if :x is null. It looks like it does . ???
-ak
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, March 27, 2003 6:33 PM
> AK
>
Title: RE: index on null column
Here is an idea ...
Create a function that returns NULL if the value is null or any other acceptable value that can be ignored. And the function should return valid values otherwise. Then create a function based index on this and see if that helps you.
To
Queries against the non-null values should benefit
greatly. Queries on the null values will not use the index since null
values are not indexed (at least in single column indexes). It is an old
trick to create an index on something like STATUS where the vast majority of
records have a NULL
AK
Yes, it will benefit the non-null records. Just remember that the null
values aren't indexes.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Thursday, March 27, 2003 5:19 PM
To: Multiple recipients of list ORACLE-L
I have a t
ak,
This really depends as much on how you're going to access the data as
much as the content.
Will your typical query use this column in a where clause? And will it
be looking for records where there is data?
I did a little testing with a query which was looking for one unique
value from column
Hi AK,
It depends ...
If you frequently perform searches for not null values on column A and
the cardinality of values is such that the optimizer is turned on by
the index, then yes, potentially an index could be useful.
Also, with so many nulls, such an index would be relatively small.
Cheer
