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 -~----------~----~----~----~------~----~------~--~---
