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

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

--//前一段時間的測試,避免表示式在語句中.連結http://blog.itpub.net/267265/viewspace-2215206/

--//在10g存在很明顯的差距,別人也重複了我的測試,給我一些建議.我自己按照自己的想法重新做了1次.

--//昨天做了測試，連結：http://blog.itpub.net/267265/viewspace-2217847/

--//做一些補充：

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

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,可以發現索引與表大小相近.

2.補充測試：

set timing on

--//昨天的測試:

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

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者執行時間相差不大.

--//補充測試看看日期存在範圍的情況。

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

  COUNT(*)

----------

 100000000

Elapsed: 00:00:11.24

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' and dd_date<='2018-10-30 09:00:00';

  COUNT(*)

----------

 100000000

Elapsed: 00:00:11.06

--//可以兩者執行時間很接近,我的理解這些表示式僅僅計算1次.這樣執行時間差距不大.

3.繼續測試:

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

  COUNT(*)

----------

 100000000

Elapsed: 00:00:11.04

SCOTT@test> select /*+  INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."DD_DATE")) */  count(*) from t  where   dd_date<='2018-10-30 09:00:00';

  COUNT(*)

----------

 100000000

Elapsed: 00:00:11.03

--//兩者時間接近.

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

  COUNT(*)

----------

 100000000

Elapsed: 00:00:15.76

SCOTT@test> select /*+  INDEX_FFS(T) */  count(*) from t  where   dd_date<='2018-10-30 09:00:00';

  COUNT(*)

----------

 100000000

Elapsed: 00:00:06.08

SCOTT@test> alter table t modify(dd_date not  null);

Table altered.

SCOTT@test> select /*+  INDEX_FFS(T) */  count(*) from t  ;

  COUNT(*)

----------

 100000000

Elapsed: 00:00:04.17

--//正常索引全掃描應該比索引範圍掃描快,但是你可以看出表示式運算對執行時間的影響.

4.補充說明：

--//另外，這樣擔心有點多餘，我的測試有點極端。。^_^