sql语句中常量处理——to_char and to_date 调优

tuning sql

sql语句调优

尽量避免在列上调用函数，如果不能避免则在常数上使用函数。

下面这证明这一点；

(一）SQL语句解释和执行时间比较

测试语句A和语句B，功能是等价的，explain plan都走相同的索引，但是cost不一样。

A:这是一个标准的写法，建议都用这个，列"add_time"为date类型。

select count(*)from wiliam.tb_sale

where add_time>=to_date('2011-01-11 00:00 ','yyyy-mm-dd hh24:mi:ss')

and add_time00:00 ','yyyy-mm-dd hh24:mi:ss')

B:在列上“add_time"使用"to_char"函数，

select count(*) from wiliam.tb_sale

where to_char(add_time,'yyyy-mm-dd')='2010-01-11';

为了避免shared_pool带来的误差，每条语句我都执行了两次

SQL> set timing on

SQL>

SQL> select count(*) from wiliam.tb_sale

2 where to_char(add_time,'yyyy-mm-dd')='2010-01-11'

3 ;

COUNT(*)

----------

936

Executed in 1.219 seconds

SQL>

SQL> select count(*) from wiliam.tb_sale

2 where to_char(add_time,'yyyy-mm-dd')='2010-01-11'

3 ;

COUNT(*)

----------

936

Executed in 1.187 seconds

语句B执行的时间为1.187S，一个简单的记数查询，显然太长。

SQL>

SQL> select count(*)from wiliam.tb_sale

2 where add_time>=to_date('2011-01-11 00:00 ','yyyy-mm-dd hh24:mi:ss')

3 and add_time00:00 ','yyyy-mm-dd hh24:mi:ss')

4 ;

COUNT(*)

----------

632

Executed in 0.047 seconds

SQL>

SQL> select count(*)from wiliam.tb_sale

2 where add_time>=to_date('2011-01-11 00:00 ','yyyy-mm-dd hh24:mi:ss')

3 and add_time00:00 ','yyyy-mm-dd hh24:mi:ss')

4 /

COUNT(*)

----------

632

Executed in 0.016 seconds

语句A:这是我改写的，只花了0.016S，相对于语句B,1.187S天然之别，这正是我需要的结果。

(二）下面我使用执行计划查看两条功能相同的语句

QL> explain plan for

2

2 select count(*)from wiliam.tb_sale

3 where add_time>=to_date('2011-01-11 00:00 ','yyyy-mm-dd hh24:mi:ss')

4 and add_time00:00 ','yyyy-mm-dd hh24:mi:ss')

5 /

Explained

Executed in 0.141 seconds

SQL> select * from table (dbms_xplan.display)

2 ;

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 6 |

| 1 | SORT AGGREGATE | | 1 | 7 | |

|* 2 | FILTER | | | | |

|* 3 | INDEX SKIP SCAN | IDX2_TB_SALE | 1 | 7 | 6 |

----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_DATE('2011-01-11 00:00 ','yyyy-mm-dd

hh24:mi:ss')00:00 ','yyyy-mm-dd hh24:mi:ss'))

3 - access("TB_SALE"."ADD_TIME">=TO_DATE('2011-01-11 00:00

','yyyy-mm-dd hh24:mi:ss') AND "TB_SALE"."ADD_TIME"

2 00:00 ','yyyy-mm-dd hh24:mi:ss'))

filter("TB_SALE"."ADD_TIME">=TO_DATE('2011-01-11 00:00

','yyyy-mm-dd hh24:mi:ss') AND "TB_SALE"."ADD_TIME"

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

2 00:00 ','yyyy-mm-dd hh24:mi:ss'))

Note: cpu costing is off

23 rows selected

Executed in 1.125 seconds

分析：index skip scan 为”索引跳跃式扫描” 索引没有包含引导列，或者返回行很少采用此方式，这里

只返回一行所以采用此方式。而且"COST"为6，很小。我比较下面的SQL的COST。

SQL> explain plan for

2 select count(*) from wiliam.tb_sale

3 where to_char(add_time,'yyyy-mm-dd')='2010-01-11'

4 /

Explained

Executed in 0 seconds

SQL> select * from table (dbms_xplan.display)

2 /

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

-----------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

-----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 7 | 327 |

| 1 | SORT AGGREGATE | | 1 | 7 | |

|* 2 | INDEX FAST FULL SCAN| IDX2_TB_SALE | 16500 | 112K| 327 |

-----------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter(TO_CHAR("TB_SALE"."ADD_TIME",'yyyy-mm-dd')='2010-01-11')

Note: cpu costing is off

15 rows selected

Executed in 0.062 seconds

分析：这里采用"index fast full scan"为”索引快速全扫描”，如果select 语句后面中的列都被包含在组合索引中，而且where后面没有出现组合索引的引导列，并且需要检索出大部分数据，那么这个时候可能执行index fast full scan。index fast full scan 发生的条件为2个：1.必须是组合索引。2.引导列不在where条件中。这里COST 为327，显然太高了。

最终分析：

为什么where 后面列上使用函数会降低数据库性能：因为数据库每次在分析一条数据的时候，都会调用函数to_char or to_date,这样势必COST很大，时间很长。而在参数项上使用转换函数对数据库性能不会有太大影响。

[@more@]

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

转载于:http://blog.itpub.net/22934571/viewspace-1044512/