改寫一個要跑5小時的SQL成1分鐘

改寫NOT EXISTS, 分成兩個語句,用union all連起來. 一個要跑個小時的SQL,改寫後,1分鐘就可以跑完. 以下item,link,item_link表都大約40 million.[@more@]

INSERT INTO temp_existing_links
(item_id,
link_id,
link_priority,
item_identifier)
SELECT /*+ INDEX (i IX_ITEM_6) */ i.item_id,
NULL,
NULL,
i.item_identifier
FROM item i
WHERE i.account_code = p_account_code
AND i.account_type_code = p_account_type_code
AND i.latest_ind ='Y'
AND NOT EXISTS (SELECT 1 from link, item_link WHERE link.link_id = item_link.link_id AND valid_link_ind = 'Y' AND item_id = i.item_id);

改成以下寫法:

union all 裡的第一個select用左連線表示找出沒有在item_link裡出現的item(左連線,左邊都會出現item_id,即使右邊il.item_id是null,即沒有對應的. ), 第二個表示找出link了,但valid_link_ind<>'Y' 的item. 這兩個條件就滿足了上面的判斷NOT EXISTS.
INSERT INTO temp_existing_links
(item_id,
link_id,
link_priority,
item_identifier)
(
SELECT /*+ INDEX (i IX_ITEM_6) */ i.item_id,
NULL,
NULL,
i.item_identifier
FROM item i,item_link il
WHERE i.account_code = 'CRES_20000'
AND i.account_type_code = 'TRADE'
AND i.latest_ind ='Y'
and i.item_id = il.item_id(+)

and il.item_id is null

union all

SELECT /*+ INDEX (i IX_ITEM_6) */ i.item_id,
NULL,
NULL,
i.item_identifier
FROM item i,item_link il,link l
WHERE i.account_code = 'CRES_20000'
AND i.account_type_code = 'TRADE'
AND i.latest_ind ='Y'
and i.item_id = il.item_id
and il.link_id = l.link_id
and l.valid_link_ind <> 'Y'
);