这条SQL还是很简单的,主要说说思路吧
前期检查
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle DatabAS e 10g Enterprise Edition ReleAS e 10.2.0.4.0 - 64bi PL/SQL ReleAS e 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> show parameter block NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192 db_file_multiblock_read_count integer 16 SQL> select actual_start_date,job_name,status from(select * from dba_scheduler_job_run_details where job_name=‘GATHER_STATS_JOB‘ order by log_date desc) where rownum<4; ACTUAL_START_DATE JOB_NAME STATUS ---------------------------------------- -------------------- --------------- 15-APR-14 10.00.02.642615 PM +08:00 GATHER_STATS_JOB SUCCEEDED 14-APR-14 10.00.03.035445 PM +08:00 GATHER_STATS_JOB SUCCEEDED 12-APR-14 06.01.42.695071 AM +08:00 GATHER_STATS_JOB SUCCEEDED
前期检查,这个库很小,基本来说 自动统计信息就够了。
select tt.clevel , tt.week , tt.storeid , tt.competitorid , tt.code , tt.storename , tt.shortname , tt.comparable , tt.shelfproduct , tt.pricesum , tt.competitorpricesum , tt.AVGMARKETPRICESUM , tt.AUCHANLESS , tt.AUCHANSAME , tt.AUCHANMORE , tt.division_no , tt.section_no , tt.grp_no , tt.subgrp_no , tt.subgrp2_no , tt.priceindex , tt.standardid from V_HAHA tt where tt.year = 2013 AND (tt.week in(38) OR 38 is null) AND (tt.storeid = null OR null is null) AND (tt.standardid = null OR null is null) AND (tt.code in (null) OR null is null) AND (tt.division_no = null OR null is null) AND (tt.section_no = null OR null is null) AND (tt.grp_no = null OR null is null) AND (tt.subgrp_no = null OR null is null) AND (tt.subgrp2_no = null OR null is null) AND (tt.clevel = ‘store‘ OR ‘store‘ is null);
运行时间:5分钟多
从语句上看,其实挺坑爹的,条件明显是不固定的,还有视图! 先把视图定义搞出来
视图定义
select tt.clevel, tt.year, tt.week, tt.storeid, tt.competitorid, tt.code, NULL AS storename, NULL AS shortname, tt.comparable, tt.shelfproduct, tt.AUCHANLESS, tt.AUCHANSAME, tt.AUCHANMORE, tt.pricesum, tt.competitorpricesum, tt.AVGMARKETPRICESUM, tt.division_no, tt.section_no, tt.grp_no, tt.subgrp_no, tt.subgrp2_no, tt.priceindex, tt.standardid from T_HAHA tt WHERE tt.type=6 AND tt.standardid>=0 AND tt.code IS NOT NULL;
可以看到是针对T_HAHA表 做了一次过滤而已(设计的太tm烂了,不过我只做几个报表的优化,又不是整体,我就不管了)
看看表多大
select num_rows,blocks*8/1024/1024 "G" from dba_tables where table_name =upper(‘T_HAHA‘) and OWNER=‘HAHA‘; NUM_ROWS G ---------- ---------- 39305070 9.43690491不算小
思路一,看这个视图能过滤出多少行,如果过滤的很多,可以考虑根据视图的列,建立一个组合索引,外面查询将 视图merge禁止掉,让其对小结果集再进行过滤。
select count(*) from T_HAHA tt WHERE tt.type=6 AND tt.standardid>=0 AND tt.code IS NOT NULL; COUNT(*) ---------- 21088861
SQL> select 21088861/39292445 from dual; 21088861/39292445 ----------------- .536715417
占了全表的50%多,思路一没戏
找开发确认,这语句这么坑爹,上面有 year 还有week,且貌似看上去是不固定条件,确认是否有固定存在值的列?
答复为:year,week,clevel 都是固定有值的(值本身内容不定)。
这就好办了
思路二:根据三个列创建组合索引,其他条件回表后再过滤
现在需要确认一个问题,是否三个条件过滤的值是少量的。
SQL> select count(*),count(distinct year),count(distinct week),count(distinct clevel) from T_HAHA; COUNT(*) COUNT(DISTINCTYEAR) COUNT(DISTINCTWEEK) COUNT(DISTINCTCLEVEL) ---------- ------------------- ------------------- --------------------- 37497589 7 53 6
SQL> select count(*) from T_HAHA where year=2013 and week=50 and clevel=‘store‘; COUNT(*) ---------- 151随便写了几个测试,结果都差不多 这么少。那么最终确定方案出来了。
1、创建索引,从Distinct那条语句可以看出,week 选择率最好(过滤的最多)
create index T_HAHA_test on storebasepricesum(week,year,clevel) parallel 8;
alter index T_HAHA_test parallel 1;
再次执行语句
select count(*) from ( select /*+ index(tt T_HAHA_TEST) */ . . . . from V_HAHA tt where tt.year = 2013 AND (tt.week in(38) OR 38 is null) AND (tt.storeid = null OR null is null) AND (tt.standardid = null OR null is null) AND (tt.code in (null) OR null is null) AND (tt.division_no = null OR null is null) AND (tt.section_no = null OR null is null) AND (tt.grp_no = null OR null is null) AND (tt.subgrp_no = null OR null is null) AND (tt.subgrp2_no = null OR null is null) AND (tt.clevel = ‘store‘ OR ‘store‘ is null) ); COUNT(*) ---------- 334Elapsed: 00:00:00.01
0.01秒出结果,之前是5分多,速度提升了多少呢,来我算一算 哈哈哈哈哈哈~
优化虽易,乙方不易 ,且行且特么珍惜。