Re: Indexes

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/07/25
Message-ID: <33da174a.28867208@newshost>#1/1

On 22 Jul 1997 15:34:57 GMT, "Brad Worsfold" <bworsfold_at_vic.lgs.ca> wrote:

>Hello.
>
>If I want to create a composite index on two columns, will Oracle Index a
>row if one
>of the columns is optional? Meaning that one column has a value, the other
>is null???
>
>I was told that Indexes are not created on rows with Null Values?
>
>If this is true, then it would be better not to index that table, right???
>
>
>I hope to hear something back????
>
>Thanks.
>
>Brad Worsfold
>

Index entries are not made for rows where all of the columns in the index are NULL. If any of the entries in the set of columns to be indexed are NOT NULL, then that row is indexed.

Consider the following simple example. I have a table with three columns, x, y, and z. It has three rows.

X is never NULL.
Y is null once out of three.
Z is always NULL.

I have an index on

(z)      - 0 rows in the index
(y,z)    - 2 rows in the index
(x)      - 3 rows in the index

So, the column Z which is ALWAYS null contributes 0 to the index. the index on Y,Z is populated for the 2 rows when Y is not null (but the third row where y,z are both NULL is not in the index). The index on X has three entries.

Index the columns that have nulls if you ask questions like:

select * from T where y = 5;

Even if Y allowed NULLs, the fact you ask for Y = 5 allows the index to be used (since it is a fact that if Y = 5, Y is not null, the index can be used). A search like:

select * from T where y is NULL;

can never use an index since nulls are not indexed. What kind of questions are you going to ask? That will determine what you need to index.

SQL>
SQL> create table index_demo

  2  (	     x	     int,
  3  	     y	     int,
  4  	     z	     int

  5 )
  6 /

Table created.

SQL> create index index_on_null_column on index_demo(z);

Index created.

SQL> create index index_on_partially_null_cols on index_demo(y,z);

Index created.

SQL> create index index_on_non_null_column on index_demo(x);

Index created.

SQL>
SQL> insert into index_demo (x,y,z) values ( 1, NULL, NULL );

1 row created.

SQL> insert into index_demo (x,y,z) values ( 2, 4, NULL );

1 row created.

SQL> insert into index_demo (x,y,z) values ( 3, 5, NULL );

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> analyze index index_on_null_column validate structure;

Index analyzed.

SQL> select name, LF_ROWS from index_stats;

NAME                              LF_ROWS                                       
------------------------------ ----------                                       
INDEX_ON_NULL_COLUMN                    0                                       

SQL>
SQL> analyze index index_on_partially_null_cols validate structure;

Index analyzed.

SQL> select name, LF_ROWS from index_stats;

NAME                              LF_ROWS                                       
------------------------------ ----------                                       
INDEX_ON_PARTIALLY_NULL_COLS            2                                       

SQL>
SQL> analyze index index_on_non_null_column validate structure;

Index analyzed.

SQL> select name, LF_ROWS from index_stats;

NAME                              LF_ROWS                                       
------------------------------ ----------                                       
INDEX_ON_NON_NULL_COLUMN                3                                       

SQL>
SQL> spool off

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 25 1997 - 00:00:00 CDT