I'd start by taking this part ...
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 re-writing it as (pseudo code follows)
WHERE (col5 in (
SELECT prim_col1||'-1'
FROM alloc_tab
union all
SELECT prim_col1||'-2'
FROM alloc_tab
union all
SELECT prim_col1||'-3'
FROM alloc_tab
union all
SELECT prim_col2||'-1'
FROM alloc_tab
etc .. etc.
)
On Thu, Aug 6, 2009 at 11:40 AM, Chris <[email protected]> wrote:
>
> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
