oracle正确使用索引,通过案例学调优之--Oracle中null使用索引

通过案例学调优之--Oracle中null使用索引

默认情况下，Oracle数据库，null在Index上是不被存储的，当在索引列以“is null”的方式访问时，无法使用索引；本案例，主要向大家演示如何在存在null的索引列上，使用“is null”访问索引。

案例分析：

1、建立表和普通索引13:52:23 SCOTT@ prod >create table t2 (x int,y int);

Table created.

14:00:11 SCOTT@ prod >insert into t2 values (1,1);

1 row created.

Elapsed: 00:00:00.04

14:00:21 SCOTT@ prod >insert into t2 values (1,null);

1 row created.

Elapsed: 00:00:00.00

14:00:31 SCOTT@ prod >insert into t2 values (null,1);

1 row created.

Elapsed: 00:00:00.00

14:00:37 SCOTT@ prod >insert into t2 values (null,null);

1 row created.

Elapsed: 00:00:00.00

14:00:44 SCOTT@ prod >commit;

Commit complete.

Elapsed: 00:00:00.04

14:06:41 SCOTT@ prod >select * from t2;

X          Y

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

1          1

1

1

14:36:12 SCOTT@ prod >create index t2_ind on t2(x);

Index created.

14:49:38 SCOTT@ prod >select index_name,table_name,num_rows from user_indexes where index_name='T2_IND';

INDEX_NAME                     TABLE_NAME                       NUM_ROWS

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

T2_IND                         T2                                      3

在索引中只有3行，在最后一行字段全为null值，没有被存储！

14:36:27 SCOTT@ prod >exec dbms_stats.gather_index_stats(user,'T2_IND');

PL/SQL procedure successfully completed.

14:37:29 SCOTT@ prod >select * from t2 where x=1;

X          Y

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

1          1

1

Execution Plan

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

Plan hash value: 1173409066

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |        |     2 |     8 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     2 |     8 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T2_IND |     2 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("X"=1)

Statistics

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

1  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

519  bytes sent via SQL*Net to client

415  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

2  rows processed

14:37:45 SCOTT@ prod >select * from t2 where x is not null;

X          Y

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

1          1

1

Execution Plan

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

Plan hash value: 463061910

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

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |        |     2 |     8 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX R