分享程序员开发的那些事...
更新时间：2022-09-13 11:12:00
[20150610]sql的谓词中使用函数.txt
--程序开发经常可以看到如下的代码：
-- trunc(dd_date)=trunc(sysdate),结果导致必须建立函数索引。实际上类似的错误还有很多。
-- to_char(dd_date,'yyyymmdd') = '20150610' 等等。
-- 以前我给新来的讲课都讲这些例子，可惜可惜不知道对方不长记忆还是需要多次提到，总之在实际的开发中一直出现相似的问题。
-- 还是通过一个例子来说明：
1.建立测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t as
with a as (select rownum from dual connect by levelselect rownum id ,sysdate-rownum/1440 dd_date from a,a ;
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
SCOTT@test> select owner, num_rows, blocks, last_analyzed from dba_tables where owner=user and table_name ='T';
OWNER NUM_ROWS BLOCKS LAST_ANALYZED
------ ---------- ---------- -------------------
SCOTT 9000000 23693 2015-06-10 09:43:12
-- 23693*8/1024=185.1015625,不到190M。
2.开始测试：
SCOTT@test> show sga
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 973082064 bytes
Database Buffers 620756992 bytes
Redo Buffers 7344128 bytes
SCOTT@test> select count(*) from t where trunc(dd_date)=trunc(sysdate+10);
COUNT(*)
----------
0
Elapsed: 00:00:05.68
--注意要测试多次，这样可以缓存数据到内存中，最后几次执行以上语句测试需要5，6秒完成。
--而执行如下呢？
SCOTT@test> select count(*) from t where dd_date between trunc(sysdate+10) and trunc(sysdate+10)-1/86400;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
--执行计划肯定都是全表扫描，因为没有任何索引，但是查询条件是trunc(dd_date)=trunc(sysdate+10)的语句，相当于trunc(dd_date)
--调用函数9e6次，可以看出5.XX秒基本都耗在这个上面。而第2次查询没有这个调用trunc函数，差距是如此的悬殊。
--另外注意后面的常量如(trunc(sysdate+10))仅仅需要计算1次。
--当然实际应用调用函数次数可能没有多，至少说明在谓词中使用函数要注意，希望这些引起开发注意。
3.使用explain plan看看cpu cost。
explain plan set statement_id='x1' for select count(*) from t where trunc(dd_date)=trunc(sysdate+10);
explain plan set statement_id='x2' for select count(*) from t where dd_date between trunc(sysdate+10) and trunc(sysdate+10)-1/86400;
SCOTT@test> set numw 12
SCOTT@test> select STATEMENT_ID,OPERATION, OPTIONS, COST, CPU_COST, IO_COST, TIME from plan_table where options='FULL';
STATEMENT_ OPERATION OPTIONS COST CPU_COST IO_COST TIME
---------- ------------------------------ ---------- ------------ ------------ ------------ ------------
x1 TABLE ACCESS FULL 7192 15198728278 6419 1
x2 TABLE ACCESS FULL 6849 8448729226 6419 1
--很明显第一个cpu cost消耗高。
--另外补充cpu_cost转化cost的计算：
--计算公式如下：
CPU Cost = ROUND(#CPUCycles / cpuspeednw / 1000 / sreadtim)
SCOTT@test> Select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ -------------------- ------------ --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 05-27-2015 09:43
SYSSTATS_INFO DSTOP 05-27-2015 09:43
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 1639
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
SCOTT@test> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ -------- --------
db_file_multiblock_read_count integer 8
--根据以上数据，计算如下：
--sreadtim
10+8192/4096=12
--mreadtim
10+8*8192/4096=26
15198728278/1639/1000/12=772.7643013016066773418
773+6419=7192
8448729226/1639/1000/12=429.56727811673784828147
430+6419=6849
--正好与上面的测试结果一致。至于这里的cpu_cost如何计算出来的，估计比较复杂放弃。