SQL优化一条

这条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(*)
----------
       334

Elapsed: 00:00:00.01

0.01秒出结果，之前是5分多，速度提升了多少呢，来我算一算  哈哈哈哈哈哈~

优化虽易，乙方不易 ，且行且特么珍惜。