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

来源：这里教程网时间：2026-03-03 12:11:37 作者：

[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.补充说明： --//另外，这样担心有点多余，我的测试有点极端。。^_^