Hi,
I'm studying paper about semijoins from www.dbspecialists.com .
One of examples shows optimization via no_merge hint:
SELECT /*+ NO_MERGE (M) */
DISTINCT M.name, M.code, M.description,
M.item_id, M.assignment_id, M.string0, M.string1
FROM (
SELECT A.name, A.code, A.description,
A.item_id, A.assignment_id, FI.string0, FI.string1
FROM relationships R, assignments A, format_items FI
WHERE R.user_id = 134546
AND R.account_id = 134545
AND R.type_code = 0
AND A.item_id = R.item_id
AND FI.item_id = A.item_id
AND EXISTS
(SELECT 1 FROM relationships R1
WHERE R1.item_id = A.item_id AND R1.status = 5
AND R1.user_id = 137279)
AND EXISTS
(SELECT 1 FROM relationships R2
WHERE R2.item_id = A.item_id AND R2.status = 5
AND R2.user_id = 134555)
AND EXISTS
(SELECT 1 FROM relationships R3
WHERE R3.item_id = A.item_id AND R3.status = 5
AND R3.user_id = 134546)
AND EXISTS
(SELECT 1 FROM relationships R4
WHERE R4.item_id = A.item_id AND R4.status = 5
AND R4.user_id = 137355)
AND EXISTS
(SELECT 1 FROM relationships R5
WHERE R5.item_id = A.item_id AND R5.status = 5
AND R5.user_id = 134556)
) M
ORDER BY M.name ASC;
Why is NO_MERGE in outer query , I'm sure that this hint should be placed in inline view for making no merging inline view with main query .
expected code like
select * from tab1 , (select * /*+ no_merge */ ...)
Regards.
Greg