[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.补充说明：

--//另外，这样担心有点多余，我的测试有点极端。。^_^