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 <[email protected]> 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
> <[email protected]>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 <[email protected]> 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 [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
>>
>
> --
> 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
>
--
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
