order by,not null 索引问题

试验:

创建测试表

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7551038/viewspace-614097/