[20120410]9i下索引與空值查詢以及非空約束.txt

[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.