高分求助，sql优化

Question

SELECT distinct p.group_id,p.cust_name, t.product_name, u.serial_number, 
to_char(u.open_date,'YYYY-MM-DD HH24:MI:SS') open_date,  
to_char(u.open_date,'YYYY-MM-DD HH24:MI:SS')
p.cust_manager_id, st.cust_manager_name,s.data_name,p.class_id,c.credit_class, p.photo_tag,           co.contract_id, co.rsrv_str1,co.rsrv_str2, o.start_cycle_id,o.end_cycle_id,o.owe_fee
       
FROM  tf_f_user u,tf_f_cust_group p,td_b_product t,tf_f_customer c,tf_f_cust_contract co,td_s_static s,tf_f_cust_manager_staff st, tf_o_oweuserinfo o WHERE 1=1 AND u.cust_id=p.cust_id AND st.cust_manager_id=p.cust_manager_id AND s.data_id=p.org_type_a AND co.cust_id=p.cust_id  AND u.product_id=t.product_id  AND c.cust_id=p.cust_id  AND o.user_id = u.user_id 
AND s.type_id = 'CUSTGROUP_ORGTYPEA'  AND u.remove_tag='0'  
AND u.open_date < t.end_date    AND t.start_date+0<sysdate AND t.end_date+0>=sysdate 表关联多，且表数据庞大，上面查询太耗资源，麻烦帮忙优化一下这个查询语句啊，用EXISTS等....

Answer 1

优化牵涉的东西太多了    有部分逻辑上就能解决，单纯看sql 有点难~
1.distinct   这个必要么？  可否用逻辑就能避免掉？
2. 1=1 这个是多余的吧
3. t.start_date+0<sysdate   这个是为了人工强制避掉索引么？

Answer 2

distinct 这个要有的，1=1多余，t.start_date+0<sysdate 是要的

Answer 3

AND t.start_date+0<sysdate AND t.end_date+0>=sysdate  
这个也可以不要的，主要那几张表的关联是非常耗资源的，比如tf_f_user u,tf_f_cust_group p,td_b_product t,tf_f_customer c这几张表的数据量很大的，优化就是让查询速度最快！！！

Answer 4

你这些条件对应的字段 u.cust_id=p.cust_id AND st.cust_manager_id=p.cust_manager_id AND s.data_id=p.org_type_a AND co.cust_id=p.cust_id AND u.product_id=t.product_id AND c.cust_id=p.cust_id AND o.user_id = u.user_id  
加上索引试试

Answer 5

lz的使用exists的话，效率不会提高过少，也不显示，因为你使用选择列中包含了所有的表名

Answer 6

physical reads 0 542
physical writes 0 0
table scans (short tables) 11 37
table scans (long tables) 0 0
table scan rows gotten 6361395 19103326
table scan blocks gotten 246542 739722
table fetch by rowid 151 399
sorts (memory) 0 7
sorts (disk) 0 0
sorts (rows) 0 2
session logical reads 248093 744313
CPU used by this session 553 1675

Answer 7

SELECT STATEMENT, GOAL = ALL_ROWS 耗费=55065 基数=1 字节=352
 HASH UNIQUE 耗费=55065 基数=1 字节=352
  TABLE ACCESS BY LOCAL INDEX ROWID 对象所有者=UCR_CRM1 对象名称=TF_F_CUSTOMER 耗费=21 基数=1 字节=13
   NESTED LOOPS 耗费=55064 基数=1 字节=352
    NESTED LOOPS 耗费=55043 基数=1 字节=339
     NESTED LOOPS 耗费=55041 基数=1 字节=213
      NESTED LOOPS 耗费=55040 基数=1 字节=197
       NESTED LOOPS 耗费=55036 基数=4 字节=656
        HASH JOIN 耗费=55034 基数=301 字节=31605
         TABLE ACCESS FULL 对象所有者=UCR_CEN1 对象名称=TD_B_PRODUCT 耗费=8 基数=783 字节=27405
         HASH JOIN 耗费=55025 基数=6021 字节=421470
          INDEX FAST FULL SCAN 对象所有者=UCR_CRM1 对象名称=TF_O_OWEUSERINFO 耗费=11 基数=6021 字节=144504
          PARTITION RANGE ALL 耗费=54991 基数=3075259 字节=141461914
           TABLE ACCESS FULL 对象所有者=UCR_CRM1 对象名称=TF_F_USER 耗费=54991 基数=3075259 字节=141461914
        TABLE ACCESS BY INDEX ROWID 对象所有者=UCR_CRM1 对象名称=TF_F_CUST_GROUP 耗费=1 基数=1 字节=59
         INDEX UNIQUE SCAN 对象所有者=UCR_CRM1 对象名称=PK_TF_F_CUST_GROUP 耗费=0 基数=1
       TABLE ACCESS BY INDEX ROWID 对象所有者=UCR_CEN1 对象名称=TD_S_STATIC 耗费=1 基数=1 字节=33
        INDEX UNIQUE SCAN 对象所有者=UCR_CEN1 对象名称=PK_TD_S_STATIC 耗费=0 基数=1
      TABLE ACCESS BY INDEX ROWID 对象所有者=UCR_CEN1 对象名称=TF_F_CUST_MANAGER_STAFF 耗费=1 基数=1 字节=16
       INDEX UNIQUE SCAN 对象所有者=UCR_CEN1 对象名称=PK_TF_F_CUST_MANAGER_STAFF 耗费=0 基数=1
     TABLE ACCESS BY INDEX ROWID 对象所有者=UCR_CRM1 对象名称=TF_F_CUST_CONTRACT 耗费=2 基数=1 字节=126
      INDEX RANGE SCAN 对象所有者=UCR_CRM1 对象名称=PK_TF_F_CUST_CONTRACT 耗费=1 基数=1
    PARTITION RANGE ALL 耗费=20 基数=1
     INDEX RANGE SCAN 对象所有者=UCR_CRM1 对象名称=PK_TF_F_CUSTOMER 耗费=20 基数=1

Answer 8

1
1=1  这个多余
2
AND t.start_date+0<sysdate AND t.end_date+0>=sysdate  不知道目的何在 还搞个+0  这就耗时间3建立关联的字段索引  

Answer 9

1=1 这个多余
AND t.start_date+0<sysdate AND t.end_date+0>=sysdate 不知道目的何在 还搞个+0 这就耗时间建立关联的字段索引   这三了都做了，还是用时6秒多，太慢了，我是问这个语句的表关联再可不可以写的刚优化???求救啊

Answer 10

不能通用nextloop链接吧，加点hint 用hash_join 试试?
还有t表的数据是不是最大的?
distinct换成GROUP BY 

Answer 11

楼主可以尝试使用规则 或者强制走某个索引
select /*+rule*/ 试试

Answer 12

高手来用 EXISTS 来改写一下啊！！！

Answer 13

DBS会自动在一定幅度上进行改写优化，所以再片面从SQL语句的结构改写，意思不大。或者说，要进行有效改写的基础也必须是知道各个表的设计结构和使用情况。否则都是隔山打牛。

Answer 14

SELECT /*+ leading(u,p,c,co,s,st,o) use_hash(p,c,co,s,st,o,t)*/ p.group_id,
p.cust_name,
t.product_name,
u.serial_number,
to_char(u.open_date, 'YYYY-MM-DD HH24:MI:SS') open_date,
to_char(u.open_date, 'YYYY-MM-DD HH24:MI:SS') p.cust_manager_id,
st.cust_manager_name,
s.data_name,
p.class_id,
c.credit_class,
p.photo_tag,
co.contract_id,
co.rsrv_str1,
co.rsrv_str2,
o.start_cycle_id,
o.end_cycle_id,
o.owe_feeFROM   tf_f_user               u,
   tf_f_cust_group         p,
   td_b_product            t,
   tf_f_customer           c,
   tf_f_cust_contract      co,
   td_s_static             s,
   tf_f_cust_manager_staff st,
   tf_o_oweuserinfo        oWHERE  /*1 = 1
AND  */  u.cust_id = p.cust_id
AND    st.cust_manager_id = p.cust_manager_id
AND    s.data_id = p.org_type_a
AND    co.cust_id = p.cust_id
AND    u.product_id = t.product_id
AND    c.cust_id = p.cust_id
AND    o.user_id = u.user_id
AND    s.type_id = 'CUSTGROUP_ORGTYPEA'
AND    u.remove_tag = '0'
AND    u.open_date < t.end_date
AND    t.start_date /*+ 0*/ < SYSDATE
AND    t.end_date /*+ 0 */>= SYSDATE
group by p.group_id,
p.cust_name,
t.product_name,
u.serial_number,
to_char(u.open_date, 'YYYY-MM-DD HH24:MI:SS') open_date,
to_char(u.open_date, 'YYYY-MM-DD HH24:MI:SS') p.cust_manager_id,
st.cust_manager_name,
s.data_name,
p.class_id,
c.credit_class,
p.photo_tag,
co.contract_id,
co.rsrv_str1,
co.rsrv_str2,
o.start_cycle_id,
o.end_cycle_id,
o.owe_fee

Answer 15

10g以上的版本直接用sql tunning advisor

Answer 16

lz 的关联表太多了，8个表同时关联，你算算你的笛卡尔积多大？
lz是电信行业的吧？
在这中情况下一般用嵌套查询
select b.??,b.???,d.???
from 
(select a.a,a.b,a.c...from A
where a.a=??? and a.b=????)B,
(select c.a,c.b,c.c ...from C) D
where d.??=b.??
上面是3张表的嵌套，你的8张表的嵌套还是这种方式嵌套下去，最大的集合放在最后，或者指定驱动表（最小的表）按照这种方式一层层的过滤出来