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分多,速度提升了多少呢,来我算一算  哈哈哈哈哈哈~


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

SQL优化一条,布布扣,bubuko.com

SQL优化一条

上一篇:海量数据库设计——第三课:索引(笔记)


下一篇:mini学生管理系统。。。全部代码,4个java文件放在同一个包下面即可。用JDBC调用数据库取出数据。