select T1.Customer_id,T1.Commodity_id,T1.liq_pl,T2.open_pl
from (select customer_id, commodity_id, sum(liq_pl) as liq_pl,max(trade_date) as trade_date
from table
where Trade_Date BETWEEN TO_DATE('20060701', 'yyyymmdd') AND
TO_DATE('20060802', 'yyyymmdd')
and LENGTH(commodity_id) > 2
and LENGTH(customer_id) = 4
group by customer_id, commodity_id) T1,
table T2
where T1.customer_id=T2.Customer_id and T1.commodity_id=T2.Commodity_id and T1.Trade_Date=T2.Trade_Date
===========================================
首先表里数据量很大,如果用上面的语句没有问题,PL/SQL1秒就出结果了。我认为加上下面这一句会缩小两表相连时T2表的范围,效率应该会更高:
and T2.Trade_Date BETWEEN TO_DATE('20060701', 'yyyymmdd') AND
TO_DATE('20060802', 'yyyymmdd')
但实际测试结果是,加上这一句很长时间还不能出结果,好像进入了一个死锁,
请问是什么原因造成的?想不明白
from (select customer_id, commodity_id, sum(liq_pl) as liq_pl,max(trade_date) as trade_date
from table
where Trade_Date BETWEEN TO_DATE('20060701', 'yyyymmdd') AND
TO_DATE('20060802', 'yyyymmdd')
and LENGTH(commodity_id) > 2
and LENGTH(customer_id) = 4
group by customer_id, commodity_id) T1,
table T2
where T1.customer_id=T2.Customer_id and T1.commodity_id=T2.Commodity_id and T1.Trade_Date=T2.Trade_Date
===========================================
首先表里数据量很大,如果用上面的语句没有问题,PL/SQL1秒就出结果了。我认为加上下面这一句会缩小两表相连时T2表的范围,效率应该会更高:
and T2.Trade_Date BETWEEN TO_DATE('20060701', 'yyyymmdd') AND
TO_DATE('20060802', 'yyyymmdd')
但实际测试结果是,加上这一句很长时间还不能出结果,好像进入了一个死锁,
请问是什么原因造成的?想不明白
FROM table c4,
(SELECT MAX(c4a.trade_date) as trade_date, c4a.customer_id
FROM table c4a
WHERE c4a.trade_date BETWEEN date1 AND date2
GROUP BY c4a.customer_id) c4b
WHERE /*C4.Trade_Date BETWEEN date1 AND date2 AND */
c4.trade_date = c4b.trade_date
AND c4.customer_id = c4b.customer_id
GROUP BY c4.trade_date, c4.customer_id, c4.commodity_id
============================================================
同样的问题,如果上面这个语句不加/*C4.Trade_Date BETWEEN date1 AND date2 AND */这一句也会像死锁一样查不出东西,oracle进程占CPU 100%,如果加上注释的这一句也是1秒就出结果了。我想这两个原因可能一样,请高手指点一下,谢谢。
另外,oracle在执行时,会自动生存执行计划并存储在内存中,“时间段的条件”在t1的临时表中先执行,可能在总的where中加入的化,会取t1中的执行计划,而t1此时又不会释放快照数据块,导致系统等待。我想就是这个原因罢。