Greetings,

I have following query which is working as expected but the it seems
the way I have build this is not so performace friendly. I am pretty
sure that the WHERE clause is messing up the whole performance part.
Can you please advise. Let me know if any more informations is needed.

WITH root_tab AS (SELECT column1
                  FROM   table1
                  WHERE  col2 = 15
                  AND    col3    >= '20090618'
                  AND    col3    <= '20090730'
                  ),
     alloc_tab AS (SELECT column1 prim_col2,
                          col4 prim_col1
                   FROM   table1
                   WHERE  col4 in
                   (
                   SELECT column1
                   FROM   root_tab
                   )
                   )
SELECT col5, col6
FROM   table2
WHERE  (col5 in (
                            SELECT prim_col1||'-1'
                            FROM   alloc_tab
                            )
OR col5 in (
                            SELECT prim_col1||'-2'
                            FROM   alloc_tab
                            )
OR col5 in (
                            SELECT prim_col1||'-3'
                            FROM   alloc_tab
                            )
OR      col5 in (
                             SELECT prim_col2||'-1'
                             FROM   alloc_tab)
OR      col5 in (
                             SELECT prim_col2||'-2'
                             FROM   alloc_tab)
OR      col5 in (
                             SELECT prim_col2||'-3'
                             FROM
alloc_tab)
        )
AND    col6 NOT LIKE '%SET%';

TIA

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---

Reply via email to