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

[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做了測試.