ITPub博客

首页 > Linux操作系统 > Linux操作系统 > order by,not null 索引问题

order by,not null 索引问题

原创 Linux操作系统 作者:279531451 时间:2009-09-07 11:29:27 0 删除 编辑
试验:
创建测试表
test@ORCLDEV> create table testindex as select * from all_objects;
Table created.
test@ORCLDEV> select count(*) from testindex;
  COUNT(*)
----------
     50297
test@ORCLDEV> set autotrace on;
test@ORCLDEV> select count(*) from testindex;
  COUNT(*)
----------
     50297

Execution Plan
----------------------------------------------------------
Plan hash value: 2159720071
------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   158   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TESTINDEX | 47602 |   158   (1)| 00:00:02 |
------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        697  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@ORCLDEV> desc testindex;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 OBJECT_NAME                                           NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                                 VARCHAR2(30)
 OBJECT_ID                                             NOT NULL NUMBER
 DATA_OBJECT_ID                                                 NUMBER
 OBJECT_TYPE                                                    VARCHAR2(19)
 CREATED                                               NOT NULL DATE
 LAST_DDL_TIME                                         NOT NULL DATE
 TIMESTAMP                                                      VARCHAR2(19)
 STATUS                                                         VARCHAR2(7)
 TEMPORARY                                                      VARCHAR2(1)
 GENERATED                                                      VARCHAR2(1)
 SECONDARY                                                      VARCHAR2(1)
注意: OBJECT_ID 列被定义为not null
test@ORCLDEV> create index idx_object_id on testindex(object_id);
Index created.
test@ORCLDEV> select count(*) from testindex order by object_id;
  COUNT(*)
----------
     50297

Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604
---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    13 |    28   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 47602 |   604K|    28   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        184  consistent gets
        111  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
在object_id 列上设置了NOT NULL 约束,就可以走索引
现在修改去掉object_id约束
test@ORCLDEV> alter table TESTINDEX modify OBJECT_ID null;
Table altered.
test@ORCLDEV> desc TESTINDEX
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 OBJECT_NAME                                           NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                                 VARCHAR2(30)
 OBJECT_ID                                                      NUMBER
 DATA_OBJECT_ID                                                 NUMBER
 OBJECT_TYPE                                                    VARCHAR2(19)
 CREATED                                               NOT NULL DATE
 LAST_DDL_TIME                                         NOT NULL DATE
 TIMESTAMP                                                      VARCHAR2(19)
 STATUS                                                         VARCHAR2(7)
 TEMPORARY                                                      VARCHAR2(1)
 GENERATED                                                      VARCHAR2(1)
 SECONDARY                                                      VARCHAR2(1)
test@ORCLDEV> select count(*) from TESTINDEX;
  COUNT(*)
----------
     50297

Execution Plan
----------------------------------------------------------
Plan hash value: 2159720071
------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |   158   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TESTINDEX | 47602 |   158   (1)| 00:00:02 |
------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         75  recursive calls
          0  db block gets
        787  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
看到没有走索引,说明object_id列没定义not null  就不走索引
如果我们在where 条件加上 object_id is not null 会发生什么情况呢?
test@ORCLDEV> select count(*) from testindex where object_id is not null;
  COUNT(*)
----------
     50297

Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604
---------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    13 |    28   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 47602 |   604K|    28   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID" IS NOT NULL)
Note
-----
   - dynamic sampling used for this statement
已经走索引了
 
另外,索引和order by 语句没有直接关系
 
order by 使用索引是有条件的:
1)ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
2)ORDER BY中所有的列必须定义为非空.
3)另外,如果ORDER BY中的列在where 条件中出现,也会使用索引
 
not null 可以影响索引
 

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

请登录后发表评论 登录
全部评论

注册时间:2009-09-01

  • 博文量
    100
  • 访问量
    153841