ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120410]9i下索引与空值查询以及非空约束.txt

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

原创 Linux操作系统 作者:lfree 时间:2012-04-10 11:05:31 0 删除 编辑
[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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-720779/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6293287