一次细小的sql tunning-CSDN博客

大家在sql tunning时，首先都会去看执行计划。但set autotrace traceonly exp看执行计划显示的却是不全，有些细小的地方就无法发现。下面是我一次经历，其实挺简单的，只是希望对对家有所提醒。[@more@]

创建测试表格：

create">c2c@c2c>create table bak_all_order as select * from all_order where rownum<10000;

Table created.

create">c2c@c2c>create index bak_test on bak_all_order (SELLER_DOMAIN_USER_ID,CLOSINGDATE,IS_DELETED,TRADE_STATUS);

Index created.

有这么条语句，执行非常的频繁：

SELECT count(*) FROM bak_all_order
WHERE IS_DELETED='n' and SELLER_DOMAIN_USER_ID=:1 and SITE=:2
and TRADE_STATUS=:3
and CLOSINGDATE>=:4
and BUYER_STATUS<>:5;

所以我们建索引时将domain_user_id,closingdate,trade_status,is_deleted四个字段建个复合索引。

explain">c2c@c2c>explain plan for SELECT count(*) FROM bak_all_order
2 WHERE IS_DELETED='n' and SELLER_DOMAIN_USER_ID=:1 and SITE=:2
3 and TRADE_STATUS=:3
4 and CLOSINGDATE>=:4
5 and BUYER_STATUS<>:5;

Explained.

@?/rdbms/admin/utlxpls.sql">c2c@c2c>@?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BAK_ALL_ORDER | | | |
|* 3 | INDEX RANGE SCAN | BAK_TEST | | | |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BAK_ALL_ORDER"."BUYER_STATUS"<>TO_NUMBER(:Z) AND
"BAK_ALL_ORDER"."SITE"=:Z)
3 - access("BAK_ALL_ORDER"."SELLER_DOMAIN_USER_ID"=:Z AND
"BAK_ALL_ORDER"."CLOSINGDATE">=:Z AND "BAK_ALL_ORDER"."IS_DELETED"='n' AND
"BAK_ALL_ORDER"."TRADE_STATUS"=:Z AND "BAK_ALL_ORDER"."CLOSINGDATE" IS NOT
NULL)
filter("BAK_ALL_ORDER"."TRADE_STATUS"=:Z AND
"BAK_ALL_ORDER"."IS_DELETED"='n')

Note: rule based optimization

23 rows selected.

我们发现第3步中除了access索引时，还有个filter两个字段。

从原理上考虑，应该是 CLOSINGDATE>=:4 是大于等于引起的，需要在索引里面再做一次过滤。

为了验证，做以下测试：

先修改sql语句，去掉大于号：

explain">c2c@c2c>explain plan for SELECT count(*) FROM bak_all_order
2 WHERE IS_DELETED='n' and SELLER_DOMAIN_USER_ID=:1 and SITE=:2
3 and TRADE_STATUS=:3
4 and CLOSINGDATE=:4
5 and BUYER_STATUS<>:5;

Explained.

@?/rdbms/admin/utlxpls.sql">c2c@c2c>@?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

Note: rule based optimization

大家可以看到，这是比较好的，第3步中没有filter操作了。

然后再测试索引的顺序，将closingdate字段放在最后：

drop">c2c@c2c>drop index bak_test;

Index dropped.

c2c@c2c> create index bak_test on bak_all_order (SELLER_DOMAIN_USER_ID,IS_DELETED,TRADE_STATUS,CLOSINGDATE);

Index created.

Explained.

@?/rdbms/admin/utlxpls.sql">c2c@c2c>@?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("BAK_ALL_ORDER"."BUYER_STATUS"<>TO_NUMBER(:Z) AND
"BAK_ALL_ORDER"."SITE"=:Z)
3 - access("BAK_ALL_ORDER"."SELLER_DOMAIN_USER_ID"=:Z AND
"BAK_ALL_ORDER"."IS_DELETED"='n' AND "BAK_ALL_ORDER"."TRADE_STATUS"=:Z AND
"BAK_ALL_ORDER"."CLOSINGDATE">=:Z AND "BAK_ALL_ORDER"."CLOSINGDATE" IS NOT
NULL)

这样也是对的。

所以以后大家建复合索引时，需要注意将包含大于号的字段放在最后。最开始之所以将closingdate字段放在前面，估计是考虑到这个字段的选择性较好的原故。

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

转载于:http://blog.itpub.net/100091/viewspace-925993/