这条语句应该怎么优化

Question

 
select min(substring(t1.start_date,1,6))  from
        (
          select min(t2.tract_date) start_date
          from t06_inv_change_info t2 join
          (select nos from t99_cod_nos_synclist where tablename in ('sa', 'ma') ) t1
          on t1.nos = t2.nos 
          union all
          select min(nos) start_date
          from t99_cod_nos_synclist
          where tablename in ( 'colothprice_sa' ,'dbck_rnbm_a' ) 
        ) t1;
其中在nos和tablename上都有非聚集索引，nos的重复率较低，一般最多只有2条重复
tablename的重复率较高。t06_inv_change_info有3500w条记录，t99_cod_nos_synclist 有850w条，select nos from t99_cod_nos_synclist where tablename in ('sa', 'ma')
select min(nos) start_date
from t99_cod_nos_synclist
where tablename in ( 'colothprice_sa' ,'dbck_rnbm_a' ) 
这2个部分速度是很快的，都只要0.01秒左右
但整个语句就需要大概4分30秒，请问应该如何优化

Answer 1

select nos from t99_cod_nos_synclist where tablename in ('sa', 'ma')分解为：
select nos from t99_cod_nos_synclist where tablename='sa' OR tablename='ma' 
同理下一个也是如此！！

Answer 2

select min( start_date) from
        (
          select substring(t2.tract_date,1,6) start_date
          from t06_inv_change_info t2 join
          (select nos from t99_cod_nos_synclist where tablename in ('sa', 'ma') ) t1
          on t1.nos = t2.nos 
          union all
          select substring(nos,1,6) start_date
          from t99_cod_nos_synclist
          where tablename in ( 'colothprice_sa' ,'dbck_rnbm_a' ) 
        ) t1;

Answer 3

无效的，select nos from t99_cod_nos_synclist where tablename in ('sa', 'ma')这部分花费的时间只有0.01秒
我尝试过了

Answer 4

使用存储过程，将 select min(t2.tract_date) start_date
          from t06_inv_change_info t2 join
          (select nos from t99_cod_nos_synclist where tablename in ('sa', 'ma') ) t1
          on t1.nos = t2.nos 
          union all
          select min(nos) start_date
          from t99_cod_nos_synclist
          where tablename in ( 'colothprice_sa' ,'dbck_rnbm_a' ) 
子查询作为临时表。。再来查询临时表

Answer 5

关键是select min(t2.tract_date) start_date
          from t06_inv_change_info t2 join
          (select nos from t99_cod_nos_synclist where tablename in ('sa', 'ma') ) t1
          on t1.nos = t2.nos 
这部分，需要4分多钟，我不知道应该怎么去优化了

Answer 6

select substring(min(t1.start_date),1,6)  from
        (
          select min(t2.tract_date) start_date
          from t06_inv_change_info t2 join
          (select nos from t99_cod_nos_synclist where tablenam e= 'sa' union select nos from t99_cod_nos_synclist where tablename = 'ma') ) t1
          on t1.nos = t2.nos 
          union 
          select min(nos) start_date from t99_cod_nos_synclist where tablename = 'colothprice_sa' 
          union select min(nos) start_date from t99_cod_nos_synclist where tablename = 'dbck_rnbm_a') 
        ) t1;
试试这个

Answer 7

set statistics profile on 
把执行计划贴出来

Answer 8

union all  最好别用  用精确的条件
in 有别的办法 用精确的条件
还有 min 最好有group by

Answer 9

是哪里看到的不要用union all的？
人家实践的出来是union all比union的效率高。

Answer 10

是Oracle的，，不过sql语句我想应该差不多
SELECT STATEMENT, GOAL = ALL_ROWS 22265 1 12
 SORT AGGREGATE 1 12
  VIEW U_PD_DW 22265 2 24
   UNION-ALL
    SORT AGGREGATE 1 42
     PX COORDINATOR
      PX SEND QC (RANDOM) SYS :TQ10002 1 42
       SORT AGGREGATE 1 42
        HASH JOIN 22262 30536354 1282526868
         BUFFER SORT
          PX RECEIVE 4797 1112129 22242580
           PX SEND HASH SYS :TQ10000 4797 1112129 22242580
            INLIST ITERATOR
             INDEX RANGE SCAN U_PD_DW I_T99_COD_NOS_SYNCLIST_NOS 4797 1112129 22242580
         PX RECEIVE 17418 30536376 671800272
          PX SEND HASH SYS :TQ10001 17418 30536376 671800272
           PX BLOCK ITERATOR 17418 30536376 671800272
            TABLE ACCESS FULL U_PD_DW T06_INV_CHANGE_INFO 17418 30536376 671800272
    SORT AGGREGATE 1 20
     INLIST ITERATOR
      INDEX RANGE SCAN U_PD_DW I_T99_COD_NOS_SYNCLIST_NOS 3 1112129 22242580

Answer 11

SELECT MIN(SUBSTRING(t1.start_date, 1, 6)) 
FROM (
         SELECT MIN(t2.tract_date) START_DATE 
         FROM t06_inv_change_info t2
          JOIN (
                   SELECT DISTINCT nos 
                   FROM t99_cod_nos_synclist
                   WHERE tablename = 'sa'
                   UNION ALL 
                   SELECT DISTINCT nos 
                   FROM t99_cod_nos_synclist
                   WHERE tablename = 'ma'
               ) t1
          ON  t1.nos = t2.nos 
         UNION ALL
         SELECT MIN(nos) START_DATE 
         FROM t99_cod_nos_synclist
         WHERE tablename IN ('colothprice_sa', 'dbck_rnbm_a')
     ) t1;Try

Answer 12

t99_cod_nos_synclist 上的 nos,tract_date要建一个复合索引.
语句不用改，应该就很快了。CREATE INDEX IX_nos_tract ON t99_cod_nos_synclist(nos,tract_date)
GO

Answer 13

SELECT MIN(SUBSTRING(t1.start_date, 1, 6)) 
FROM (
         SELECT MIN(t2.tract_date) START_DATE 
         FROM t06_inv_change_info t2
             JOIN (
                      SELECT DISTINCT nos 
                      FROM t99_cod_nos_synclist
WHERE exists (select * from t99_cod_nos_synclist t_1 where t_1.tablename = 'ma' and t_1.tablename = 'sa')
                  ) t1
                 ON  t1.nos = t2.nos 
         UNION ALL
         SELECT MIN(nos) START_DATE 
         FROM t99_cod_nos_synclist
         WHERE EXISTS(select * from t99_cod_nos_synclist t_2 where t_2.tablename='colothprice_sa' and tablename = 'dbck_rnbm_a')
     ) t1;   把以上的in全部换成EXISTS这样效率比较高点

Answer 14

当我把
 SELECT DISTINCT nos 
                      FROM t99_cod_nos_synclist
                      WHERE tablename = 'sa'
                      UNION ALL 
                      SELECT DISTINCT nos 
                      FROM t99_cod_nos_synclist
                      WHERE tablename = 'ma'
放入临时表tmp，代入用tmp代入你的代码中后，时间又变成4分半，这是什么情况..
不懂