Indexes over columns without NOT NULL constraint, do them work?

[Manuel G]

Hi:

We made several tests with individual indexes over columns created without NOT NULL constraint.

We realized that kind of indexes (individual) are not used by oracle when you make a query.

I.E. select column from table where value=3 will not use an individual index created over "column" if NOT NULL constraint is not defined for the column.

Is this an oracle rule even if the whole columns have a not null value?

Thanks.

[Deepak Extross]

Usually, it's upto the query optimiser to decide whether to use the index or not.
If the amount of data (number of rows) in the table are small, the optimiser may choose to just scan the table sequentially, rather than looking up the index first.

It depends to a large extent on the type of data you have. If the table has only a few rows, and most of them have NULL values in the indexed column, there is little value in going through the index.

Some Database Products allow you to "coax" the optimiser to use an index. Informix, for example, allows you to use optimiser hints which will force the use of a particular index for the query. But this will not necessarily give you better performance.

[Brian Crabtree]

The optimizer works on the table to see if the cost of the query would be better or worse using an index over a full table scan. Normally, indexes are preferred over full table scans, however if you are pulling a specific fraction of data (I think the number is like 20%), it will use a full table scan over an index range scan.

This also depends on when you analyzed the table last as well, as it gives a proportion of what data is found in the table. If "select * from tableA where colA = '3'" is run against a table with 10 rows in it, it will do a full table scan. If there are 100 rows, it will most likely do a index scan (provided that there is an index against colA).

Null data is still data, just data that is listed as null, and indexes work on this data the same as a normal NOT NULL row.

Brian

[Yogeeraj_1]

hi,

if you are using the cost-based optimizer, make sure that your statistics are not too old.

you may wish to use either of the following:
e.g.
analyze table t
compute statistics
for TABLE
for all indexes

or

exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed columns',cascade=>true);

Also, as the others have mentioned the cost-based optimizer might choose to do a full-scan instead of an indexed scan. It depends on your statistics.

Don't forget that you can also force the query to use the index by using hints.

lastly, don't forget that use of indexes are highly dependent on the SQL query that you have written...

Hope this helps!

Best Regards
Yogeeraj

[Manuel G]

Hi:

As far as I can understand from your answers, null column indexes could work with cost-optimizer mode. The only thing we have to take care is to keep stats as newer as possible.

What happen with the rule-optimizer mode?

I think it??s out of date from oracle 8i but it??s still in use on older versions.

Thanks for help.