Creating index just for a query in Oracle 10g

Question

Hi,

I have around 3.5 million records in a particular table in Oracle 10g and I want to query for all records with one particular column having NULL value.

My question is, is it worth to create an index on that column just for the purpose of that one query? Will the time saved by using the index compensate the time taken to create it? Or should I just do the query without the index?

Please note that I don't have the option to have the index created for me ahead of time and if I do create the index just for the query, I have to immediately drop it afterward.

p/s: I did search for existing questions but can't find the answer I want. Do point me to any similar questions that I might have missed.

Answer 1

Creating the index will be much more expensive than a single query. Without an index, the query will just have to scan the table. Building the index also has to scan the table -- and then build and write the index.

Answer 2

Could you partition the table in a way that NULL values end up in a single partition? If I'm not mistaken (sorry, can't test it right now) NULL values go to the MAXVALUE partition in a range partitioned table. I think Oracle would do partition elimination just fine and therefore scan only the partition that contains the NULL values. You just need to choose a suitable number to be the high value of the previous partition in order to eliminate most or all unwanted rows.

Example (say my_column is a number and you want to find NULLs):

create table test_table (
  my_column number,
  other_columns ...
)
partition by range (my_column)
(
  partition not_null values less than (99999999999999),
  partition nulls    values less than (MAXVALUE)
);

Then if you "select * from test_table where my_column is null" the execution plan should show only partition "nulls" being scanned (full scan, but it will contain mostly only what you want).

Remember to enable row movement if the value can be updated from null to not null and vice-versa.

Answer 3

If you have Enterprise Edition and you have a multi-CPU system then you should consider using parallel query. This is the quickest way of getting a result from a full table scan, although it does have an administrative overhead, and it does require the availability of multiple CPUs (obviously),

Answer 4

If I recall correctly, Oracle does not index NULL values; thus, if all the fields which would go into an index are NULL there will be no index entry for that row. So if, for example, you've got a table like

ID      NUMBER PRIMARY KEY
FIELD1  NUMBER
FIELD2  NUMBER

with indexes

ID_INDEX (ID) PRIMARY KEY
FIELD1_INDEX (FIELD1)
FIELD2_INDEX (FIELD2)

and data

ID=1  FIELD1=NULL  FIELD2=1
ID=2  FIELD1=2     FIELD2=NULL
ID=3  FIELD1=3     FIELD2=3
ID=4  FIELD1=NULL  FIELD2=NULL

there should be four entries in ID_INDEX, but only two each in FIELD1_INDEX and FIELD2_INDEX. You can verify this by querying the DBA_IND_STATISTICS view (after gathering table statistics):

SELECT * FROM DBA_IND_STATISTICS WHERE TABLE_NAME = 'whatever';

and looking at the DISTINCT KEYS and NUM_ROWS columns.

The take-away from all this is that if you've got a query that looks for NULL values in a particular column there's a strong likelihood that you'll end up doing a full table scan. I believe that a clustered index may actually index NULL-valued entries, but I haven't used them so I'm not certain about that.

I hope this helps.