执行COUNT(COLUNM_NAME)不使用索引 [转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3839 | 回复: 0   主题: 执行COUNT(COLUNM_NAME)不使用索引 [转帖]        下一篇 
wayne
注册用户
等级:中校
经验:1690
发帖:221
精华:0
注册:2011-7-21
状态:离线
发送短消息息给wayne 加好友    发送短消息息给wayne 发消息
发表于: IP:您无权察看 2011-8-16 17:19:28 | [全部帖] [楼主帖] 楼主

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优化器在不少地方进行了改进。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论