oracle index column,When does Oracle index null column values?

And in addition to APC's answer: when you want to index a NULL value, you can add a constant expression to the index.

Example:

SQL> select * from v$version where rownum = 1

2 /

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

1 rij is geselecteerd.

SQL> create table t (id,status,fill)

2 as

3 select level

4 , nullif(ceil((level-1)/1000),0)

5 , lpad('*',1000,'*')

6 from dual

7 connect by level <= 10000

8 /

Tabel is aangemaakt.

SQL> select status

2 , count(*)

3 from t

4 group by status

5 /

STATUS COUNT(*)

---------- ----------

1 1000

2 1000

3 1000

4 1000

5 1000

6 1000

7 1000

8 1000

9 1000

10 999

1

11 rijen zijn geselecteerd.

SQL> create index i_status on t(status)

2 /

Index is aangemaakt.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true)

PL/SQL-procedure is geslaagd.

SQL> set autotrace traceonly

SQL> select *

2 from t

3 where status is null

4 /

1 rij is geselecteerd.

Uitvoeringspan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=201 Card=1 Bytes=1007)

1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=201 Card=1 Bytes=1007)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

364 consistent gets

0 physical reads

0 redo size

1265 bytes sent via SQL*Net to client

242 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

Please note the full table scan and the 364 consistent gets.

SQL> set autotrace off

SQL> create index i_status2 on t(status,1)

2 /

Index is aangemaakt.

SQL> set autotrace traceonly

SQL> select *

2 from t

3 where status is null

4 /

1 rij is geselecteerd.

Uitvoeringspan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=1007)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=1 Card=1 Bytes=1007)

2 1 INDEX (RANGE SCAN) OF 'I_STATUS2' (INDEX) (Cost=1 Card=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

3 consistent gets

1 physical reads

0 redo size

1265 bytes sent via SQL*Net to client

242 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

And now it uses the index and has only 3 consistent gets.

Regards,

Rob.