列定义是否为空对COUNT（*）操作索引选择的影响

 

SQL> desc test;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME         VARCHAR2(128)
 OBJECT_ID         NUMBER

=====================================

SQL> select count(*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation    | Name | Rows  | Cost (%CPU)| Time   |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |   | 1 | 5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |   | 1 |        |   |
|   2 |   TABLE ACCESS FULL| TEST |  2305 | 5   (0)| 00:00:01 |
-------------------------------------------------------------------

 

=========SQL> alter table test modify(object_id not null);

Table altered.

SQL> desc test;
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_NAME         VARCHAR2(128)
 OBJECT_ID       NOT NULL NUMBER

SQL> select count(*) from test;

  COUNT(*)
----------
      2305

Execution Plan
----------------------------------------------------------
Plan hash value: 911472365

----------------------------------------------------------------------------
| Id  | Operation       | Name    | Rows  | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |     |  1 |  3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |     |  1 |  |    |
|   2 |   INDEX FAST FULL SCAN| INX_OBJ_ID |  2305 |  3   (0)| 00:00:01 |
----------------------------------------------------------------------------
===================================================