[20120410]9i下索引与空值查询以及非空约束.txt

前几天在优化9i的一条sql语句时，发现一个奇怪的现象，查询条件是is null，发现竟然可以使用索引，感觉很奇怪，再仔细看原来
查询字段存在一个非空约束，对比了10g以及11g的版本，感觉10g与11g改进不少。

测试如下：

1.建立测试环境:

SQL> select * from v$version;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

PL/SQL Release 9.2.0.8.0 - Production

CORE    9.2.0.8.0       Production

TNS for 32-bit Windows: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production

SQL> create table t tablespace users as select rownum id,'test' name from dual connect by level <=1000;

Table created.

SQL> desc t;

Name    Null?    Type

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

ID              NUMBER

NAME            CHAR(4)

--可以发现ID字段可以为空。

SQL> create index i_t_id  on t(id) tablespace users;

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

2.测试：

SQL> set autot traceonly ;

SQL> select * from t where id is  null ;

no rows selected

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=8)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=8)

Statistics

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

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        297  bytes sent via SQL*Net to client

        376  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

--可以发现执行计划走的是全表扫描。

3.如果加入非空约束，情况会如何呢？

SQL> alter table t modify id not null ;

Table altered.

SQL> set autot traceonly ;

SQL> select * from t where id is  null ;

no rows selected

Execution Plan

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

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=8)

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

   2    1     INDEX (RANGE SCAN) OF 'I_T_ID' (NON-UNIQUE) (Cost=1 Card=1)

Statistics

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

          0  recursive calls

          0  db block gets

          2  consistent gets

          0  physical reads

          0  redo size

        297  bytes sent via SQL*Net to client

        376  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

--发现加入非空约束后，可以使用索引。

4.在11g下测试，10g测试留给大家：

SQL> select * from v$version ;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select rownum id,'test' name from dual connect by level<=1000;

Table created.

SQL> create index i_t_id on t(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL> select * from t where id is  null ;

no rows selected

SQL> @dpc

PLAN_TABLE_OUTPUT

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

SQL_ID  9gsqcvc0dxtd4, child number 0

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

select * from t where id is  null

Plan hash value: 1601196873

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

| Id  | Operation         | Name | E-Rows | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |      |        |     3 (100)|

|*  1 |  TABLE ACCESS FULL| T    |      1 |     3   (0)|

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

Predicate Information (identified by operation id):

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

   1 - filter("ID" IS NULL)

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

24 rows selected.

SQL> alter table t modify id not null;

Table altered.

SQL> set autot traceonly

SQL> select * from t where id is  null ;

no rows selected

Execution Plan

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

Plan hash value: 1322348184

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |     9 |     0   (0)|          |

|*  1 |  FILTER            |      |       |       |            |          |

|   2 |   TABLE ACCESS FULL| T    |  1000 |  9000 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter(NULL IS NOT NULL)

Statistics

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

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        396  bytes sent via SQL*Net to client

        508  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

--可以发现走的是全表扫描，但是过滤条件是NULL IS NOT NULL，肯定为false。结果根本不读表，所以逻辑读为0.