[20181029]避免表达式在sql语句中(10g).txt

--//前一段时间的测试,避免表达式在语句中.链接http://blog.itpub.net/267265/viewspace-2215206/

--//在10g存在很明显的差距,别人也重复了我的测试,给我一些建议.我自己按照自己的想法重新做了1次.

1.环境:

SCOTT@test> @ &r/ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> show sga

Total System Global Area  486539264 bytes

Fixed Size                  2084872 bytes

Variable Size             360714232 bytes

Database Buffers          113246208 bytes

Redo Buffers               10493952 bytes

--//sga仅仅480M.

$ env | grep NLS

NLS_LANG=AMERICAN_AMERICA.zhs16gbk

NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM

NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF

NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

--//注:我日期格式定义如上,这样我下面使用字符串变量不会出现问题.

create table t as

with a as (select rownum from dual connect by level<=1e4)

select 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   100000000     263203 2018-10-29 09:20:10

2.测试:

SCOTT@test> select sysdate+10 from dual;

SYSDATE+10

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

2018-11-08 09:21:46

--//测试各种情况:

set timing on

variable v_dd varchar2(20);

exec :v_dd := '2018-11-08 09:21:46'

select count(*) from t where dd_date>=sysdate+10;                    

select count(*) from t where dd_date>='2018-11-08 09:21:46';         

select count(*) from t where dd_date>= (select sysdate+10 from dual);

select count(*) from t where dd_date>= (select sysdate    from dual);

select count(*) from t where dd_date>=trunc(sysdate+10);             

select count(*) from t where dd_date>=trunc(sysdate);                

select count(*) from t where dd_date>=trunc(sysdate);                

select count(*) from t where trunc(dd_date)=trunc(sysdate+10);       

select count(*) from t where dd_date>=:v_dd;                         

--// 测试使用等于的情况

select count(*) from t where dd_date='2018-11-08 09:21:46';          

select count(*) from t where dd_date=:v_dd;                          

--//注每条sql语句测试4次,第1次不算.

--//测试sql语句                                                          时间  

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

select count(*) from t where dd_date>=sysdate+10;                        00:00:14.28  00:00:14.22  00:00:14.23

select count(*) from t where dd_date>='2018-11-08 09:21:46';             00:00:03.96  00:00:03.93  00:00:03.96  

select count(*) from t where dd_date>= (select sysdate+10 from dual);    00:00:07.64  00:00:07.63  00:00:07.62

select count(*) from t where dd_date>= (select sysdate    from dual);    00:00:07.60  00:00:07.61  00:00:07.59

select count(*) from t where dd_date>=trunc(sysdate+10);                 00:00:38.58  00:00:38.58  00:00:38.57

select count(*) from t where dd_date>=trunc(sysdate);                    00:00:38.58  00:00:38.58  00:00:38.57

select count(*) from t where dd_date>=trunc(sysdate);                    00:00:30.02  00:00:30.04  00:00:30.01

select count(*) from t where trunc(dd_date)=trunc(sysdate+10);           00:01:05.22  00:01:05.22  00:01:05.23

select count(*) from t where dd_date>=:v_dd;                             00:00:04.89  00:00:04.90  00:00:04.83

--// 测试使用等于的情况

select count(*) from t where dd_date='2018-11-08 09:21:46';              00:00:03.92  00:00:03.90  00:00:03.93   

select count(*) from t where dd_date=:v_dd;                              00:00:04.88  00:00:04.90  00:00:04.85

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

3.结果分析:

--//1.大于 与 等于 类似的语句执行时间差别不大.比如:

select count(*) from t where dd_date>='2018-11-08 09:21:46';             00:00:03.96  00:00:03.93  00:00:03.96  

select count(*) from t where dd_date='2018-11-08 09:21:46';              00:00:03.92  00:00:03.90  00:00:03.93   

--//而且使用常量情况要快于使用绑定变量的情况:

select count(*) from t where dd_date>='2018-11-08 09:21:46';             00:00:03.96  00:00:03.93  00:00:03.96  

select count(*) from t where dd_date>=:v_dd;                             00:00:04.89  00:00:04.90  00:00:04.83

--//使用标量子查询有缓存结果的作用,但是测试效果并不明显.

select count(*) from t where dd_date>= (select sysdate+10 from dual);    00:00:07.64  00:00:07.63  00:00:07.62

select count(*) from t where dd_date>= (select sysdate    from dual);    00:00:07.49  00:00:07.48  00:00:07.47

--//2.你可以发现在10g下,如果谓词里面存在表达式,如果运算次数很多的情况下,执行时间差距很明显:

--//3.对比如下:

select count(*) from t where dd_date>=trunc(sysdate);                    00:00:30.02  00:00:30.04  00:00:30.01

select count(*) from t where dd_date>=trunc(sysdate+10);                 00:00:38.58  00:00:38.58  00:00:38.57

select count(*) from t where trunc(dd_date)=trunc(sysdate+10);           00:01:05.22  00:01:05.22  00:01:05.23

--//可以推测select count(*) from t where trunc(dd_date)=trunc(sysdate+10);,trunc 两边都参与运算.

4.继续分析:

--//建立索引看看.

SCOTT@test> create index i_t_dd_data on t(dd_date) tablespace users_index;

Index created.

SCOTT@test> select min(dd_date) from t;

MIN(DD_DATE)

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

1828-09-10 22:34:03

SCOTT@test> select sysdate - to_date('1828-09-10 22:34:03','yyyy-mm-dd hh24:mi:ss') from dual ;

SYSDATE-TO_DATE('1828-09-1022:34:03','YYYY-MM-DDHH24:MI:SS')

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

                                                  69444.4784

SCOTT@test> select segment_name,bytes,blocks from dba_segments where owner=user and segment_name in ('T','I_T_DD_DATA');

SEGMENT_NAME              BYTES     BLOCKS

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

T                    2214592512     270336

I_T_DD_DATA          2183135232     266496

--//表比索引仅仅大一点点,索引包含rowid,可以发现索引与表大小相近.

set timing on

--//使用提示保证使用索引范围扫描.

select /*+  INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."DD_DATE")) */  count(*) from t  where  dd_date>=sysdate-70000;

SCOTT@test> select /*+  INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."DD_DATE")) */  count(*) from t  where  dd_date>=sysdate-70000;

  COUNT(*)

----------

 100000000

Elapsed: 00:00:10.79

--//使用时间 00:00:10.79 00:00:10.83

select /*+  INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."DD_DATE")) */  count(*) from t  where  dd_date>='1828-09-10 22:34:02';

SCOTT@test> select /*+  INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."DD_DATE")) */  count(*) from t  where  dd_date>='1828-09-10 22:34:02';

  COUNT(*)

----------

 100000000

Elapsed: 00:00:10.77

--//使用时间 00:00:10.77 00:00:10.77

--//可以两者执行时间很接近.我估计这里根本没有进行比较,因为只要确定左侧,一直扫描到右端就ok了.

--//执行时间主要使用单块读方面.可以看出2者执行时间相差不大.

--//修改提示使用索引快速全扫描看看( INDEX FAST FULL SCAN).

SCOTT@test> select /*+  INDEX_FFS(T) */  count(*) from t  where  dd_date>=sysdate-70000;

  COUNT(*)

----------

 100000000

Elapsed: 00:00:15.01

SCOTT@test> select /*+  INDEX_FFS(T) */  count(*) from t  where  dd_date>='1828-09-10 22:34:02';

  COUNT(*)

----------

 100000000

Elapsed: 00:00:05.75

--//INDEX FAST FULL SCAN是乱序读取的,这样必须比较.你可以发现使用表达式以及常量存在10秒的差距.

4.总结:

--//10g下,在执行sql语句时,先snapshot scn取得当日期(sysdate),并且把它当作变量,每次比较都执行sysdate+10 计算.这样耗费了大量cpu.

--//在谓词中使用函数问题更加严重.

--//当然现在使用10g的越来越少,这样极端的情况很少见,仅仅通过测试说明问题.

--//实际上如果计算,函数出现在select里面,如果返回结果集的话,情况也是一样的.特别有一些开发喜欢自定义的函数.

--//另外在11g以后,这个问题已经不明显.我仅仅在12c做了测试.