SQL1:
SELECT COUNT(*)
FROM tbm_order_queue_his a, tbm_main_inst_his c
WHERE a.order_id = c.order_id
AND a.state = 1
AND c.is_new = 1
AND a.offer_id IN (SELECT tso.offer_id
FROM tpm_service_offer tso
WHERE tso.offer_type_id = 1)
AND a.prod_id IN (SELECT tmp.main_prod_id
FROM tpm_main_product tmp
WHERE tmp.prod_big_id in (11, 12))
AND c.company_id in
(select tc.company_id
from tsm_company tc
start with tc.company_id = 1668
connect by PRIOR tc.company_id = tc.upper_id)
AND a.finish_time BETWEEN
to_date('2012-02-01 11:35:17', 'yyyy-mm-dd hh24:mi:ss') AND
to_date('2012-02-28 11:35:20', 'yyyy-mm-dd hh24:mi:ss')
SQL2:
SELECT count(*)
FROM tbm_order_queue_his a, tbm_main_inst_his c, tbm_cust_his d
WHERE a.order_id = c.order_id
AND a.order_id = d.order_id
AND a.state = 1
AND c.is_new = 1
AND d.is_new = 1
AND a.offer_id IN (SELECT tso.offer_id
FROM tpm_service_offer tso
WHERE tso.offer_type_id = 1)
AND a.prod_id IN (SELECT tmp.main_prod_id
FROM tpm_main_product tmp
WHERE tmp.prod_big_id in (11, 12))
AND c.company_id in
(select tc.company_id
from tsm_company tc
start with tc.company_id = 1668
connect by PRIOR tc.company_id = tc.upper_id)
AND a.finish_time BETWEEN
to_date('2012-02-01 11:35:17', 'yyyy-mm-dd hh24:mi:ss') AND
to_date('2012-02-28 11:35:20', 'yyyy-mm-dd hh24:mi:ss')SQL1执行时间:501秒
SQL2执行时间:2秒以内
不明白为何多关联一个表效率反而提高了。
SQL如上所示,查询条件中以下列建了索引(a.offer_id,a.prod_id,c.company_id,a.finish_time,a.order_id,d.order_id)
该时间段数据量10W左右。总数据量300W左右。若SQL1去掉条件:
AND c.company_id in
(select tc.company_id
from tsm_company tc
start with tc.company_id = 1668
connect by PRIOR tc.company_id = tc.upper_id)
则执行效率也在2秒以内,SQL2有SQL1的所有查询条件,但SQL2为何还会能快?寻大师指点。(SQL2中tbm_cust_his表 order_id建了索引)sql
SELECT COUNT(*)
FROM tbm_order_queue_his a, tbm_main_inst_his c
WHERE a.order_id = c.order_id
AND a.state = 1
AND c.is_new = 1
AND a.offer_id IN (SELECT tso.offer_id
FROM tpm_service_offer tso
WHERE tso.offer_type_id = 1)
AND a.prod_id IN (SELECT tmp.main_prod_id
FROM tpm_main_product tmp
WHERE tmp.prod_big_id in (11, 12))
AND c.company_id in
(select tc.company_id
from tsm_company tc
start with tc.company_id = 1668
connect by PRIOR tc.company_id = tc.upper_id)
AND a.finish_time BETWEEN
to_date('2012-02-01 11:35:17', 'yyyy-mm-dd hh24:mi:ss') AND
to_date('2012-02-28 11:35:20', 'yyyy-mm-dd hh24:mi:ss')
SQL2:
SELECT count(*)
FROM tbm_order_queue_his a, tbm_main_inst_his c, tbm_cust_his d
WHERE a.order_id = c.order_id
AND a.order_id = d.order_id
AND a.state = 1
AND c.is_new = 1
AND d.is_new = 1
AND a.offer_id IN (SELECT tso.offer_id
FROM tpm_service_offer tso
WHERE tso.offer_type_id = 1)
AND a.prod_id IN (SELECT tmp.main_prod_id
FROM tpm_main_product tmp
WHERE tmp.prod_big_id in (11, 12))
AND c.company_id in
(select tc.company_id
from tsm_company tc
start with tc.company_id = 1668
connect by PRIOR tc.company_id = tc.upper_id)
AND a.finish_time BETWEEN
to_date('2012-02-01 11:35:17', 'yyyy-mm-dd hh24:mi:ss') AND
to_date('2012-02-28 11:35:20', 'yyyy-mm-dd hh24:mi:ss')SQL1执行时间:501秒
SQL2执行时间:2秒以内
不明白为何多关联一个表效率反而提高了。
SQL如上所示,查询条件中以下列建了索引(a.offer_id,a.prod_id,c.company_id,a.finish_time,a.order_id,d.order_id)
该时间段数据量10W左右。总数据量300W左右。若SQL1去掉条件:
AND c.company_id in
(select tc.company_id
from tsm_company tc
start with tc.company_id = 1668
connect by PRIOR tc.company_id = tc.upper_id)
则执行效率也在2秒以内,SQL2有SQL1的所有查询条件,但SQL2为何还会能快?寻大师指点。(SQL2中tbm_cust_his表 order_id建了索引)sql
1.order_id不是唯一,a表中order_id唯一,c,d同一order_id最多两条,通过条件 AND c.is_new = 1
AND d.is_new = 1只取表中的一条数据。
2.数据量:2a=c=d