Home » SQL & PL/SQL » SQL & PL/SQL » Left Outer Joins - query is too slow (Oracle 10g)
Left Outer Joins - query is too slow [message #422090] Mon, 14 September 2009 06:02 Go to next message
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 Go to previous messageGo to next message
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 #422095 is a reply to message #422091] Mon, 14 September 2009 07:02 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

The table is very large and there are daily many updates and inserts are done on this table. So we have only one clustered index on columns (ClActCd,ClCallNo,ClCustCd,ClCustBrCd,ClOffCd,CompCode,ClGenDate).

This order of columns are used in many procedures.

But in the given code this order is not working and so index is ont being used. I can't create many indexes as it would also effect the performance during DML operations.

Are there any other alternatives....or can I change the query ?

The statistics is gathered.....this problem is coming on production...so we have been asked to tune the procedure.
Regards,

Mona
Re: Left Outer Joins - query is too slow [message #422107 is a reply to message #422095] Mon, 14 September 2009 07:49 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #422108 is a reply to message #422090] Mon, 14 September 2009 07:53 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
 AND (cl.clcustcustcd = 'ABC022000017' OR cl.clcustcustcd IS NULL)


Use of IS NULL supresses the use of indexes

Re: Left Outer Joins - query is too slow [message #422110 is a reply to message #422108] Mon, 14 September 2009 08:19 Go to previous messageGo to next message
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 #422112 is a reply to message #422110] Mon, 14 September 2009 08:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Quote:
Use of IS NULL supresses the use of indexes


Not always

And even then, only indexes on that column.
Re: Left Outer Joins - query is too slow [message #422165 is a reply to message #422112] Mon, 14 September 2009 22:48 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks for getting me the typo mistake ... I overlooked it!

Can you please advice me as what can I do for the performance gain in this query.

In the where clause the conditions are only with the table CALLEXEC. Will the filteration with WHERE clause if done early before the joins will help anyway to lessen the number of records?

I am not sure about the code ..... as I tried but its giving error..I might be wrong with the code.....

Can we write something like this....
Select col1, col2.... from (select col1,col2 from callexec where ....all where condition here)
left join...left join!


Please advice something for the performance gain we can do with the query.


Thanks,
Mona

Re: Left Outer Joins - query is too slow [message #422166 is a reply to message #422090] Mon, 14 September 2009 22:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
That is a nice test case. I like it. It shows Oracle's smarts.

Not intending to diminish the results of bonker... in all fairness, INDEX FAST FULL SCAN is only a stone's throw away from FULL TABLE SCAN. I mean lets face it, if they had called it FULL INDEX SCAN instead of "Index Fast Full Scan" would we be so hot for it? (note the use of the word FAST... is this technical correctness, or just extra marketing peanutbutter fluff?).

Index usage in this manner is not what most would consider an "INDEX LOOKUP" in the normal sense.

But... Kudos to you bonker. This is an excellent workup of a feature that can be significant in the situations it was created for. I am filing this one away in my "smart" folder. Keep them coming.

Kevin
Re: Left Outer Joins - query is too slow [message #422172 is a reply to message #422166] Mon, 14 September 2009 23:21 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I tried putting WHERE clause before the left outer joins but got the same result. There was no change in the explain plan.

Please suggest something which I can do to this query to increase the performance to make it execute faster .....!!

[Updated on: Mon, 14 September 2009 23:29]

Report message to a moderator

Re: Left Outer Joins - query is too slow [message #422175 is a reply to message #422112] Mon, 14 September 2009 23:31 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Thanks Bonker and Rowbottom point noted

@ Mona Try using hints to use indexes Wink

[Updated on: Mon, 14 September 2009 23:33]

Report message to a moderator

Re: Left Outer Joins - query is too slow [message #422181 is a reply to message #422175] Mon, 14 September 2009 23:55 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Some more suggestions please !!!

This application is in production and I am not very comfortable with usage of Hints. There are so many of them...not sure exactly which one to use in this case!!
Re: Left Outer Joins - query is too slow [message #422185 is a reply to message #422181] Tue, 15 September 2009 00:20 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#CHDGGCHC
Using Optimizer Hints
Re: Left Outer Joins - query is too slow [message #422196 is a reply to message #422185] Tue, 15 September 2009 01:29 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Is there no other solution besides Hints.... !!
Re: Left Outer Joins - query is too slow [message #422201 is a reply to message #422110] Tue, 15 September 2009 01:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #422210 is a reply to message #422205] Tue, 15 September 2009 02:09 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
./fa/3518/0/
my selectivity was too much I got the point
thanks bonker



Re: Left Outer Joins - query is too slow [message #422211 is a reply to message #422205] Tue, 15 September 2009 02:09 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Can anyone please help me increasing the performance of the query without using Hints!!!

Thanks,
Mona
Re: Left Outer Joins - query is too slow [message #422221 is a reply to message #422211] Tue, 15 September 2009 02:54 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
After the index creation on te table CALLEXEC, I got the below explain plan. There is already a clustered index on table MEmpOff on columns (MEMP_CODE,MEMP_COMPCD). It is still giving FULL Table Access on this table. Can we do something to it.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1325962827

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |   540 |    13   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER                 |              |     1 |   540 |    13   (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 |   VIEW                              |              |     1 |    45 |     2   (0)| 00:00:01 |
|* 26 |    TABLE ACCESS FULL                | MEMPOFF      |     1 |    53 |     2   (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("MO6"."MEMP_CODE"(+)="CL"."GUNMAN" AND "MO6"."MEMP_COMPCD"(+)='CSL')
  16 - access("MO5"."MEMP_CODE"(+)="CL"."LOADER" AND "MO5"."MEMP_COMPCD"(+)='CSL')
  18 - access("MO4"."MEMP_CODE"(+)="CL"."DRIVER" AND "MO4"."MEMP_COMPCD"(+)='CSL')
  20 - access("MO3"."MEMP_CODE"(+)="CL"."CUST3" AND "MO3"."MEMP_COMPCD"(+)='CSL')
  22 - access("MO2"."MEMP_CODE"(+)="CL"."CUST2" AND "MO2"."MEMP_COMPCD"(+)='CSL')
  24 - access("MO1"."MEMP_CODE"(+)="CL"."CUST1" AND "MO1"."MEMP_COMPCD"(+)='CSL')
  26 - filter("MO7"."MEMP_CODE"="CL"."SUPERVISOR" AND "MO6"."MEMP_COMPCD"="CL"."COMPCODE")



Is the index on the table MempOff not geeting used?

Regards,
Mona
Re: Left Outer Joins - query is too slow [message #422222 is a reply to message #422211] Tue, 15 September 2009 02:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Smile

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 #422227 is a reply to message #422222] Tue, 15 September 2009 03:10 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

There are records in table CALLEXEC is 223000 and only one record is returned. CALLEXEC is a transaction table.


Regards,
Mona
Re: Left Outer Joins - query is too slow [message #422230 is a reply to message #422224] Tue, 15 September 2009 03:30 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
Are the left Outer joins fine or can I make any further change in the query?

you know it better Wink
http://stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/queries006.htm
for why should you do join(s)
Re: Left Outer Joins - query is too slow [message #422499 is a reply to message #422224] Wed, 16 September 2009 22:11 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
monasingh wrote on Tue, 15 September 2009 18:02
I forgot to change the '6' to '7'. I did that and to my surprise, the full table scan went away Smile


Isn't that what I said?

rleishman wrote on Mon, 14 September 2009 22:49
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


Previous Topic: performing transpose on table data (merged)
Next Topic: Constraints
Goto Forum:
  


Current Time: Mon Sep 26 21:29:34 CDT 2016

Total time taken to generate the page: 0.15290 seconds