Oracle的BTREE单列索引是不存储NULL值的，而COUNT(COLUMN_NAME)也不计算COLUMN_NAME为空的记录，从这一点上看，Oracle的优化器对于COUNT(COLUMN_NAME)的查询应该使用COLUMN_NAME上的索引，但是实际情况却不是。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> DESC T
名称 是否为空? 类型
------------------------------------------- -------- ---------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> CREATE INDEX IND_T_OBJECT_ID ON T (OBJECT_ID);
索引已创建。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
28631
SQL> SELECT COUNT(*) FROM T WHERE OBJECT_ID IS NULL;
COUNT(*)
----------
1
SQL> SET AUTOT ON EXP
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T');
PL/SQL 过程已成功完成。
SQL> SELECT COUNT(OBJECT_ID) FROM T;
COUNT(OBJECT_ID)
----------------
28630
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=79 Card=28631 Bytes=143155)
SQL> SELECT /*+ INDEX(T IND_T_OBJECT_ID) */ COUNT(OBJECT_ID) FROM T;
COUNT(OBJECT_ID)
----------------
28630
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=79 Card=28631 Bytes=143155)
发现不但默认情况下，Oracle不会使用索引，通过HINT强制Oracle利用索引，Oracle都不会使用索引。
下面三个SQL语句是等价的，但是执行计划并不相同：
SQL> SELECT COUNT(OBJECT_ID) FROM T;
COUNT(OBJECT_ID)
----------------
28630
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=79 Card=28631 Bytes=143155)
SQL> SELECT COUNT(*) FROM T WHERE OBJECT_ID IS NOT NULL;
COUNT(*)
----------
28630
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_T_OBJECT_ID' (NON-UNIQUE) (Cost=14 Card=28630 Bytes=143150)
SQL> SELECT COUNT(OBJECT_ID) FROM T WHERE OBJECT_ID IS NOT NULL;
COUNT(OBJECT_ID)
----------------
28630
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_T_OBJECT_ID' (NON-UNIQUE) (Cost=14 Card=28630 Bytes=143150)
如果字段设置为NOT NULL的话，Oracle会利用索引的。
SQL> CREATE INDEX IND_T_OWNER ON T(OWNER);
索引已创建。
SQL> SELECT COUNT(OWNER) FROM T;
COUNT(OWNER)
------------
28631
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=79 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=79 Card=28631 Bytes=171786)
SQL> ALTER TABLE T MODIFY OWNER NOT NULL;
表已更改。
SQL> SELECT COUNT(OWNER) FROM T;
COUNT(OWNER)
------------
28631
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_T_OWNER' (NON-UNIQUE) (Cost=4 Card=28631)
另外，如果是复合索引，情况会有变化。
SQL> DROP INDEX IND_T_OWNER;
索引已丢弃。
SQL> DROP INDEX IND_T_OBJECT_ID;
索引已丢弃。
SQL> CREATE INDEX IND_T_OWNER_OBJECT_ID ON T (OWNER, OBJECT_ID);
索引已创建。
SQL> SELECT COUNT(OBJECT_ID) FROM T;
COUNT(OBJECT_ID)
----------------
28630
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_T_OWNER_OBJECT_ID' (NON-UNIQUE) (Cost=4 Card=28631 Bytes=143155)
这种情况，Oracle使用了索引，不过这是由于复合索引中的OWNER列为NOT NULL，因此，索引包含了OBJECT_ID的NULL值。
总体来说，感觉Oracle的CBO还是不够智能。上面的测试是在9204上测试的，而10g中情况则不一样了。
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> CREATE INDEX IND_T_OBJECT_ID ON T(OBJECT_ID);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS')
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
50318
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
--------------------------------------------------------
Id Operation Name Rows Cost (%CPU)
--------------------------------------------------------
0 SELECT STATEMENT 1 44 (3)
1 SORT AGGREGATE 1
2 TABLE ACCESS FULL T 50318 44 (3)
--------------------------------------------------------
SQL> SELECT COUNT(OBJECT_ID) FROM T;
COUNT(OBJECT_ID)
----------------
50314
执行计划
----------------------------------------------------------
Plan hash value: 3841213438
------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 9 (12)
1 SORT AGGREGATE 1 5
2 INDEX FAST FULL SCAN IND_T_OBJECT_ID 50318 245K 9 (12)
------------------------------------------------------------------------------
看来Oracle10g优化器在不少地方进行了改进。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-16 17:19:28 |
赞(
操作
