On Oct 11, 4:34 pm, Gayathri <[email protected]> wrote:
> 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 <[email protected]> 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 <[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- Hide quoted text -
>
> - Show quoted text -
It would have been nice to know you had indexes on the tables as such
information makes a huge difference in how one approaches the
problem.
The query, as posted, can't run:
SQL> SELECT /*+ PARALLEL(t, 30) */
2 o1.col1, o2.col2,
3 t.t_col1, t.t_col2, o3.col3
4 FROM t,
5 o1 o1,
6 o2 o2,
7 o3 o3
8 WHERE o1.col(+) = t.col
9 AND o1.table_name(+) = 'TEST1'
10 AND o2.col(+) = t.col
11 AND o2.table_name(+) = 'TEST2'
12 AND o3.col(+) = t.col
13 AND o3.table_name(+) = 'TEST3';
AND o3.col(+) = t.col
*
ERROR at line 12:
ORA-00904: "T"."COL": invalid identifier
neither can the posted 'solution' as the column names are not correct
nor are the columns in the outermost select provided by the
subqueries:
SQL> SELECT /*+ PARALLEL(t, 30) */ t.t_col1, t.t_col2, o1.col1,
o2.col2, o3.col3
2 FROM t
3 LEFT JOIN (SELECT /*+ INDEX(o1) */ o1.col FROM o1 WHERE
4 o1.table_name = 'TEST1') o1 ON o1.col = t.col
5 LEFT JOIN (SELECT /*+ INDEX(o2) */ o2.col FROM o2 WHERE
6 o2.table_name = 'TEST2') o2 ON o2.col = t.col
7 LEFT JOIN (SELECT /*+ INDEX(o3) */ o3.col FROM o3 WHERE
8 o3.table_name = 'TEST3') o3 ON o3.col = t.col ;
LEFT JOIN (SELECT /*+ INDEX(o3) */ o3.col FROM o3 WHERE
*
ERROR at line 7:
ORA-00904: "O3"."COL": invalid identifier
The original query outer joins a column to a static text value so why
even have that condition? It appears to be useless.
The plan for that query is:
Execution Plan
----------------------------------------------------------
Plan hash value: 1636174663
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 21M| 240 (5)|
00:00:03 |
|* 1 | HASH JOIN RIGHT OUTER | | 100K| 21M| 240 (5)|
00:00:03 |
|* 2 | TABLE ACCESS FULL | O3 | 1 | 63 | 3 (0)|
00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 100K| 15M| 235 (4)|
00:00:03 |
|* 4 | TABLE ACCESS FULL | O2 | 1 | 63 | 3 (0)|
00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER| | 100K| 9M| 230 (3)|
00:00:03 |
|* 6 | TABLE ACCESS FULL | O1 | 1 | 52 | 3 (0)|
00:00:01 |
| 7 | TABLE ACCESS FULL | T | 100K| 5078K| 225 (2)|
00:00:03 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O3"."COL1"(+)="T"."T_COL1")
2 - filter("O3"."TABLE_NAME"(+)='TEST3')
3 - access("O2"."COL1"(+)="T"."T_COL1")
4 - filter("O2"."TABLE_NAME"(+)='TEST2')
5 - access("O1"."COL1"(+)="T"."T_COL1")
6 - filter("O1"."TABLE_NAME"(+)='TEST1')
The query and plan for the fixed suggested replacement:
SQL> SELECT /*+ PARALLEL(t, 30) */ t.t_col1, t.t_col2, o1.col1,
o2.col2, o3.col3
2 FROM t
3 LEFT JOIN (SELECT /*+ INDEX(o1) */ o1.col1 FROM o1 WHERE
4 o1.table_name = 'TEST1') o1 ON o1.col1 = t.t_col1
5 LEFT JOIN (SELECT /*+ INDEX(o2) */ o2.col1, o2.col2 FROM
o2 WHERE
6 o2.table_name = 'TEST2') o2 ON o2.col1 = t.t_col1
7 LEFT JOIN (SELECT /*+ INDEX(o3) */ o3.col1, o3.col3 FROM
o3 WHERE
8 o3.table_name = 'TEST3') o3 ON o3.col1 = t.t_col1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3110313937
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 21M|
242 (5)| 00:00:03 |
|* 1 | HASH JOIN RIGHT OUTER | | 100K| 21M|
242 (5)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID | O3 | 1 | 63 |
4 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | INDX_O3 | 1 | |
3 (0)| 00:00:01 |
|* 4 | HASH JOIN RIGHT OUTER | | 100K| 15M|
236 (4)| 00:00:03 |
| 5 | TABLE ACCESS BY INDEX ROWID| O2 | 1 | 63 |
4 (0)| 00:00:01 |
|* 6 | INDEX FULL SCAN | INDX_O2 | 1 | |
3 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 100K| 9M|
230 (3)| 00:00:03 |
|* 8 | INDEX FULL SCAN | INDX_O1 | 1 | 52 |
3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T | 100K| 5078K|
225 (2)| 00:00:03 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O3"."COL1"(+)="T"."T_COL1")
3 - access("O3"."TABLE_NAME"(+)='TEST3')
filter("O3"."TABLE_NAME"(+)='TEST3')
4 - access("O2"."COL1"(+)="T"."T_COL1")
6 - access("O2"."TABLE_NAME"(+)='TEST2')
filter("O2"."TABLE_NAME"(+)='TEST2')
7 - access("O1"."COL1"(+)="T"."T_COL1")
8 - access("O1"."TABLE_NAME"(+)='TEST1')
filter("O1"."TABLE_NAME"(+)='TEST1')
which does speed the query up considerably (notably by use of the
concatenated indexes created on the three 'o' tables). The query, as
written, returns all of the data from table T along with mostly
nothing from the other three tables (as I have it set up as there is
one row, in each table, that matches the table_name criteria).
My rewrite of the query:
SQL> SELECT /*+ PARALLEL(t, 30) */ t.t_col1, t.t_col2, o1.col1,
o2.col2, o3.col3
2 FROM t
3 LEFT JOIN o1
4 ON o1.col1 = t.t_col1 and o1.table_name = 'TEST1'
5 LEFT JOIN o2
6 ON o2.col1 = t.t_col1 and o2.table_name = 'TEST2'
7 LEFT JOIN o3
8 ON o3.col1 = t.t_col1 and o3.table_name = 'TEST3';
produces this plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 2662284025
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 21M| 239
(5)| 00:00:03 |
|* 1 | HASH JOIN RIGHT OUTER | | 100K| 21M| 239
(5)| 00:00:03 |
|* 2 | TABLE ACCESS FULL | O3 | 1 | 63 | 3
(0)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 100K| 15M| 234
(4)| 00:00:03 |
|* 4 | TABLE ACCESS FULL | O2 | 1 | 63 | 3
(0)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER| | 100K| 9M| 229
(3)| 00:00:03 |
|* 6 | INDEX FAST FULL SCAN| INDX_O1 | 1 | 52 | 2
(0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T | 100K| 5078K| 225
(2)| 00:00:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O3"."COL1"(+)="T"."T_COL1")
2 - filter("O3"."TABLE_NAME"(+)='TEST3')
3 - access("O2"."COL1"(+)="T"."T_COL1")
4 - filter("O2"."TABLE_NAME"(+)='TEST2')
5 - access("O1"."COL1"(+)="T"."T_COL1")
6 - filter("O1"."TABLE_NAME"(+)='TEST1')
The purpose of this query escapes me.
David Fitzjarrell
--
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
