Left Outer Joins - query is too slow [message #422090] |
Mon, 14 September 2009 06:02  |
monasingh
Messages: 229 Registered: May 2006 Location: Mumbai
|
Senior Member |

|
|
Hi,
I have a procedure which is running very slow. The table CALLEXEC is a very large table and the other tables MEMPOFF and MROUTER are master table with indexes in them.
There are some left outer joins done on the table CALLEXEC.
I am not sure but can we somehow use the WHERE caluse first with the table CALLEXEC to filter the records and then call the LEFT OUTER JOIN with the two smaller tables? It might make it run faster.....please advice a way to rewrite this code in an efficient manner.
Select CL.RTCODE RouteCode,MR.RtName RouteName,
CASE MR.TMode
WHEN 'V' THEN 'Van' WHEN 'R' THEN 'Bike Rider' WHEN 'B' THEN 'On Body' END case,
MO1.Memp_Fname || ' ' || MO1.Memp_Lname || ' - ' || MO1.Memp_RimNo Custodian1,
MO2.Memp_Fname || ' ' || MO2.Memp_Lname || ' - ' || MO2.Memp_RimNo Custodian2,
MO3.Memp_Fname || ' ' || MO3.Memp_Lname || ' - ' || MO3.Memp_RimNo Custodian3,
MO4.Memp_Fname || ' ' || MO4.Memp_Lname || ' - ' || MO4.Memp_RimNo Driver,
MO5.Memp_Fname || ' ' || MO5.Memp_Lname || ' - ' || MO5.Memp_RimNo Loader,
MO6.Memp_Fname || ' ' || MO6.Memp_Lname || ' - ' || MO6.Memp_RimNo GunMan1,
MO7.Memp_Fname || ' ' || MO7.Memp_Lname || ' - ' || MO7.Memp_RimNo GunMan2,
CL.CUST1 Cust1Code, CL.CUST2 Cust2Code ,
CL.CUST3 Cust3Code, CL.DRIVER DriverCode,CL.LOADER LoaderCode ,CL.GUNMAN GunManCode,
CL.SUPERVISOR SupervisorCode,
CL.VANCODE VanCode
from CALLEXEC CL
LEFT OUTER JOIN MROUTER MR ON MR.RTCODE = CL.RTCODE AND MR.COMPCODE=CL.COMPCODE AND MR.OFFCODE = CL.CLOFFCD
LEFT OUTER JOIN MEMPOFF MO1 ON MO1.Memp_code = CL.CUST1 AND MO1.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO2 ON MO2.Memp_code = CL.CUST2 AND MO2.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO3 ON MO3.Memp_code = CL.CUST3 AND MO3.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO4 ON MO4.Memp_code = CL.DRIVER AND MO4.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO5 ON MO5.Memp_code = CL.Loader AND MO5.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO6 ON MO6.Memp_code = CL.Gunman AND MO6.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO7 ON MO7.Memp_code = CL.Supervisor AND MO6.Memp_CompCd=CL.CompCode
WHERE CL.ClActCd='I'
AND CL.ClCallNo='14'
AND Cl.ClCustCd='A00003'
AND Cl.ClCustBrCd='ABC022'
AND Cl.ClOffCd='022'
AND Cl.CompCode='CSL'
AND Cl.ClNature='B'
AND (Cl.ClCustCustCd='ABC022000017' OR Cl.ClCustCustCd IS NULL)
The explain plan :-
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2063551002
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 540 | 52 (2)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 540 | 52 (2)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 495 | 50 (2)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 437 | 49 (3)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 379 | 48 (3)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 321 | 47 (3)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 263 | 46 (3)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 205 | 45 (3)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 147 | 44 (3)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | CLEXEC | 1 | 111 | 42 (3)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| MROUTE | 1 | 36 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | NC_CLLOG | 1 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 24 | VIEW | | 1 | 45 | 2 (0)| 00:00:01 |
|* 25 | TABLE ACCESS FULL | MEMPOFF | 1 | 53 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("CL"."CLACTCD"='I' AND "CL"."CLCALLNO"=14 AND "CL"."CLCUSTCD"='A00003' AND
"CL"."CLCUSTBRCD"='ABC022' AND "CL"."CLOFFCD"='022' AND "CL"."CLNATURE"='B' AND
("CL"."CLCUSTCUSTCD" IS NULL OR "CL"."CLCUSTCUSTCD"='ABC022000017') AND
"CL"."COMPCODE"='CSL')
11 - access("MR"."RTCODE"(+)="CL"."RTCODE" AND "MR"."COMPCODE"(+)='CSL' AND
"MR"."OFFCODE"(+)='022')
13 - access("MO6"."MEMP_CODE"(+)="CL"."GUNMAN" AND "MO6"."MEMP_COMPCD"(+)='CSL')
15 - access("MO5"."MEMP_CODE"(+)="CL"."LOADER" AND "MO5"."MEMP_COMPCD"(+)='CSL')
17 - access("MO4"."MEMP_CODE"(+)="CL"."DRIVER" AND "MO4"."MEMP_COMPCD"(+)='CSL')
19 - access("MO3"."MEMP_CODE"(+)="CL"."CUST3" AND "MO3"."MEMP_COMPCD"(+)='CSL')
21 - access("MO2"."MEMP_CODE"(+)="CL"."CUST2" AND "MO2"."MEMP_COMPCD"(+)='CSL')
23 - access("MO1"."MEMP_CODE"(+)="CL"."CUST1" AND "MO1"."MEMP_COMPCD"(+)='CSL')
25 - filter("MO7"."MEMP_CODE"="CL"."SUPERVISOR" AND "MO6"."MEMP_COMPCD"="CL"."COMPCODE")
Please tell me a way as how can I rewrite the piece of code which is a part of the procedure.
Regards,
Mona
|
|
|
Re: Left Outer Joins - query is too slow [message #422091 is a reply to message #422090] |
Mon, 14 September 2009 06:17   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Based on the columns you've specified in the Where clause, the CBO is choosing to do a Full Table Scan on CALLEXEC.
What indexes are there on this table?
Have you gathered statistics on the table?
|
|
|
|
Re: Left Outer Joins - query is too slow [message #422107 is a reply to message #422095] |
Mon, 14 September 2009 07:49   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Looks like there is a typo in that last join
LEFT OUTER JOIN MEMPOFF MO7 ON MO7.Memp_code = CL.Supervisor AND MO6.Memp_CompCd=CL.CompCode
try it as (note the M07 instead of M06)
LEFT OUTER JOIN MEMPOFF MO7 ON MO7.Memp_code = CL.Supervisor AND MO7.Memp_CompCd=CL.CompCode
Ross Leishman
|
|
|
|
Re: Left Outer Joins - query is too slow [message #422110 is a reply to message #422108] |
Mon, 14 September 2009 08:19   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
Quote:Use of IS NULL supresses the use of indexes
Not always
XE@SQL> create table t1(x int primary key, y int);
Table created.
XE@SQL> create index t1_idx on t1(y,x);
Index created.
XE@SQL> insert into t1 select rownum,case when mod(rownum,5) = 0 then null else
rownum end from dual connect by level <=100;
100 rows created.
XE@SQL> commit;
XE@SQL> exec dbms_stats.gather_table_stats('"'||user||'"','T1',method_opt=>'for
all columns size 1',cascade => true,estimate_percent=>null);
PL/SQL procedure successfully completed.
XE@SQL> explain plan for select * from t1 where y=10 or y is null;
Explained.
XE@SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1314840850
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 105 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | T1_IDX | 21 | 105 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("Y" IS NULL OR "Y"=10)
13 rows selected.
|
|
|
|
|
|
|
|
|
|
|
Re: Left Outer Joins - query is too slow [message #422201 is a reply to message #422110] |
Tue, 15 September 2009 01:38   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
@ Bonker
In my case it goes for a full table scan
I only increased the rows to 10000
SQL> create table t1(x int primary key, y int);
Table created.
SQL> create index t1_idx on t1(y,x);
Index created.
SQL>
SQL> insert into t1
2 select rownum,case when mod(rownum,5) = 0 then null else
3 rownum end
4 from dual connect by level <=10000;
10000 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> begin
2 sys.dbms_stats.gather_table_stats('"'||user||'"','T1',method_opt=>'for all columns size 1',cascade => true,estimate_percent=>null);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> explain plan Set statement_id='demo02' for select * from t1 where y=10 or y is null;
Explained.
SQL> SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table','demo02','serial'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2001 | 14007 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 2001 | 14007 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("Y" IS NULL OR "Y"=10)
13 rows selected.
|
|
|
Re: Left Outer Joins - query is too slow [message #422205 is a reply to message #422201] |
Tue, 15 September 2009 01:53   |
bonker
Messages: 402 Registered: July 2005
|
Senior Member |
|
|
Quote:In my case it goes for a full table scan
I only increased the rows to 10000
I guess it may be due to selectivity/cardinality factor which made Oracle go for full table scan.
I modified your insert case slightly and here are the results
XE@SQL> insert into t1
2 select rownum,case when mod(rownum,100) = 0 then null else
3 rownum end
4 from dual connect by level <=10000
5 /
10000 rows created.
XE@SQL> commit;
Commit complete.
XE@SQL> exec dbms_stats.gather_table_stats('"'||user||'"','T1',method_opt=>'for
all columns size 1',cascade => true,estimate_percent=>null);
XE@SQL> explain plan for select * from t1 where y=10 or y is null;
Explained.
XE@SQL>
XE@SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1838325027
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 707 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
|* 2 | INDEX RANGE SCAN| T1_IDX | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| T1_IDX | 100 | 700 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("Y"=10)
3 - access("Y" IS NULL)
filter(LNNVL("Y"=10))
17 rows selected.
XE@SQL> explain plan for select * from t1 where y is null;
Explained.
XE@SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1835451365
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 700 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T1_IDX | 100 | 700 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - access("Y" IS NULL)
13 rows selected.
XE@SQL>
you can read more about it Tom Kyte's blog
[Updated on: Tue, 15 September 2009 01:59] Report message to a moderator
|
|
|
|
|
|
Re: Left Outer Joins - query is too slow [message #422222 is a reply to message #422211] |
Tue, 15 September 2009 02:56   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What is the selectivity of ClCustCd, ClCustBrCd and ClCustCustCd?
Also, how many rows have ClCustCustCd null?
How many rows are there in the table CALLEXEC, and how many are returned by:SELECT *
FROM CALLEXEC CL
WHERE CL.ClActCd='I'
AND CL.ClCallNo='14'
AND Cl.ClCustCd='A00003'
AND Cl.ClCustBrCd='ABC022'
AND Cl.ClOffCd='022'
AND Cl.CompCode='CSL'
AND Cl.ClNature='B'
AND (Cl.ClCustCustCd='ABC022000017' OR Cl.ClCustCustCd IS NULL)
|
|
|
Re: Left Outer Joins - query is too slow [message #422224 is a reply to message #422222] |
Tue, 15 September 2009 03:02   |
monasingh
Messages: 229 Registered: May 2006 Location: Mumbai
|
Senior Member |

|
|
I forgot to change the '6' to '7'. I did that and to my surprise, the full table scan went away 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 787392693
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 553 | 12 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 553 | 12 (0)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 495 | 11 (0)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 437 | 10 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 379 | 9 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 321 | 8 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 263 | 7 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 205 | 6 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 147 | 5 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| CLEXEC | 1 | 111 | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PK_CLEXEC | 1 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| MROUTE | 1 | 36 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | NC_CLLOG | 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | MEMPOFF | 1 | 58 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_MEMPOFF17 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("CL"."CLNATURE"='B' AND ("CL"."CLCUSTCUSTCD" IS NULL OR
"CL"."CLCUSTCUSTCD"='ABC022000017'))
10 - access("CL"."CLACTCD"='I' AND "CL"."CLCALLNO"=14 AND "CL"."CLCUSTCD"='A00003' AND
"CL"."CLCUSTBRCD"='ABC022' AND "CL"."CLOFFCD"='022' AND "CL"."COMPCODE"='CSL')
12 - access("MR"."RTCODE"(+)="CL"."RTCODE" AND "MR"."COMPCODE"(+)='CSL' AND
"MR"."OFFCODE"(+)='022')
14 - access("MO7"."MEMP_CODE"(+)="CL"."SUPERVISOR" AND "MO7"."MEMP_COMPCD"(+)='CSL')
16 - access("MO6"."MEMP_CODE"(+)="CL"."GUNMAN" AND "MO6"."MEMP_COMPCD"(+)='CSL')
18 - access("MO5"."MEMP_CODE"(+)="CL"."LOADER" AND "MO5"."MEMP_COMPCD"(+)='CSL')
20 - access("MO4"."MEMP_CODE"(+)="CL"."DRIVER" AND "MO4"."MEMP_COMPCD"(+)='CSL')
22 - access("MO3"."MEMP_CODE"(+)="CL"."CUST3" AND "MO3"."MEMP_COMPCD"(+)='CSL')
24 - access("MO2"."MEMP_CODE"(+)="CL"."CUST2" AND "MO2"."MEMP_COMPCD"(+)='CSL')
26 - access("MO1"."MEMP_CODE"(+)="CL"."CUST1" AND "MO1"."MEMP_COMPCD"(+)='CSL')
Are the left Outer joins fine or can I make any further change in the query?
Regards,
Mona
[Updated on: Tue, 15 September 2009 03:03] Report message to a moderator
|
|
|
|
|
Re: Left Outer Joins - query is too slow [message #422499 is a reply to message #422224] |
Wed, 16 September 2009 22:11  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
monasingh wrote on Tue, 15 September 2009 18:02I forgot to change the '6' to '7'. I did that and to my surprise, the full table scan went away 
Isn't that what I said?
rleishman wrote on Mon, 14 September 2009 22:49Looks like there is a typo in that last join
LEFT OUTER JOIN MEMPOFF MO7 ON MO7.Memp_code = CL.Supervisor AND MO6.Memp_CompCd=CL.CompCode
try it as (note the M07 instead of M06)
LEFT OUTER JOIN MEMPOFF MO7 ON MO7.Memp_code = CL.Supervisor AND MO7.Memp_CompCd=CL.CompCode
Ross Leishman
|
|
|