Thanks Iyad, I had index on individual columns, later created composite index. which made the query run really fast.
Thanks and Regards -G. On Sun, Oct 9, 2011 at 12:30 AM, Mohammed Iyad <iya...@gmail.com> wrote: > > Nice Morning > try this query > > create index INDX_O1 on O1 (table_name); > create index INDX_O2 on O2 (table_name); > create index INDX_O3 on O3 (table_name); > > SELECT /*+ PARALLEL(t, 30) */ t.t_col1, t.t_col2, o1.col1, o2.col2, > o3.col3 > FROM t > LEFT JOIN (SELECT /*+ INDEX(o1) */ o1.col FROM o1 WHERE > o1.table_name = 'TEST1') o1 ON o1.col = t.col > LEFT JOIN (SELECT /*+ INDEX(o2) */ o2.col FROM o2 WHERE > o2.table_name = 'TEST2') o2 ON o2.col = t.col > LEFT JOIN (SELECT /*+ INDEX(o3) */ o3.col FROM o3 WHERE > o3.table_name = 'TEST3') o3 ON o3.col = t.col > > please feed back, > > Regards, > Iyad > > > On 8 October 2011 00:49, Gayathri <gd0...@gmail.com> wrote: > >> Thanks sandeep, >> >> Here is the query: >> >> SELECT /*+ PARALLEL(t, 30) */ >> o1.col1, o2.col2, >> t.t_col1, t.t_col2, o3.col3 >> FROM t, >> o1 o1, >> o2 o2, >> o3 o3 >> WHERE o1.col(+) = t.col >> AND o1.table_name(+) = 'TEST1' >> AND o2.col(+) = t.col >> AND o2.table_name(+) = 'TEST2' >> AND o3.col(+) = t.col >> AND o3.table_name(+) = 'TEST3'; >> >> I could not use IN/EXIST as based on the match of 2 columns, I would >> retrieve the 3rd column. Its correlated both ways. >> >> Please suggest. >> >> Thanks >> G >> >> On Fri, Oct 7, 2011 at 2:19 AM, SANDEEP REDDY < >> tosandeepyan...@gmail.com> wrote: >> >>> Tuning A query Depends on Selecting/ Retrieving Rows From Tables/ >>> Views. >>> >>> Maining Tuning Operations Of Correlated SubQueries Done With The Help >>> Of "IN,EXISTS,NOT IN & NOT EXITS) >>> >>> >>> On Oct 7, 9:41 am, Gayathri <gd0...@gmail.com> wrote: >>> > Hi All, >>> > >>> > Is there a way to Tune Correlated Scalar Subquery? >>> > >>> > I have tried using left outer join too, but there seems no difference >>> in >>> > execution. >>> > also added index to columns used in where clause. >>> > >>> > I will post the query and execution plan soon. >>> > >>> > But Could you please share any experience that helped while working on >>> left >>> > outer join? >>> > >>> > Thanks in advance! >>> > G >>> >>> -- >>> 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 >>> >> >> -- >> 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 >> > > -- > 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 > -- 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
