Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

NO_MERGE in outer query - please explain

698658Apr 21 2010 — edited Apr 21 2010
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 19 2010
Added on Apr 21 2010
3 comments
1,629 views