DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to execute a subquery exist before to filter the a table

  1. #1
    Join Date
    Mar 2003
    Location
    Blainville , Quebec, Canada
    Posts
    2

    How to execute a subquery exist before to filter the a table

    I want to take advantage of the subquery result because it return just a few rows to filter the TABLE_A and remove the INDEX FULL SCAN

    SELECT /*+ index(A IDX_TABLE_A) */ *
    FROM TABLE_A A
    WHERE EXISTS (SELECT /*+ merge_sj */ 'X' FROM VIEW_B_B S WHERE A.INDV_ID = S.INDV_ID);

    The plan is:
    Rows Row Source Operation
    ------- ---------------------------------------------------
    10 MERGE JOIN SEMI
    19183 TABLE ACCESS BY INDEX ROWID TABLE_A
    19183 INDEX FULL SCAN (object id 36600)
    10 SORT UNIQUE
    1 VIEW VIEW_B
    1 UNION-ALL
    0 FILTER
    0 INDEX FULL SCAN (object id 37701)
    0 FILTER
    0 NESTED LOOPS
    0 INDEX RANGE SCAN (object id 36602)
    0 INDEX UNIQUE SCAN (object id 37701)
    1 FILTER
    1 INDEX UNIQUE SCAN (object id 37701)

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    This might be a case where IN works better - depends on the view:

    SELECT * FROM TABLE_A A
    WHERE A.INDV_ID IN (SELECT DISTINCT S.INDV_ID FROM VIEW_B_B S);
    (perhaps DISTINCT is superfluous?)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Mar 2003
    Location
    Blainville , Quebec, Canada
    Posts
    2
    Unfortunately the IN is not better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width