改写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'
);