Did you tryFROM table2 t, alloc_tab a WHERE t.col5 = a.prim_col1||'-1' or t.col5 = a.prim_col1||'-2' or ....
2009/8/6 Chris <harpreet.n...@gmail.com> > Thanks alot Michael. That helped alot. I would appreciate if you can > comment on what way could be more effective way. > > On Aug 6, 3:07 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > > 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 <harpreet.n...@gmail.com> 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- Hide quoted text - > > > > - Show quoted text - > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
