Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Tuning required for full outer join

Posted on 2014-08-25
7
Medium Priority
?
283 Views
Last Modified: 2014-09-01
Below is my query and cost, I tried many indexes, but my cost remains huge.
Can we do anything for this.? Let me know if you need more details.

Query :

SELECT MD.WEIGHT md_weight, V.WEIGHT v_weight
  FROM GCB_DBA.GCB_CARGO_CAT_MAPPING m
  LEFT join GCB_DBA.GCB_CARGO_CAT_MAPPING_DTLS MD
    on (M.MAPPING_ID = MD.MAPPING_ID)
  full outer join gencargo.vehiclelist_dsch v
    on (v.bol_no = m.bol_no and v.rotation_no = m.rotation_no and
       (v.weight = md.weight / md.quantity) OR MD.WEIGHT IS NULL)
 RIGHT JOIN GENCARGO.TRACK_DISCH_LIST T
    ON ((T.BOL_NO = V.BOL_NO AND T.ROTATION_NO = V.ROTATION_NO) OR
       (T.ROTATION_NO = M.ROTATION_NO AND T.BOL_NO = M.BOL_NO))
 where T.CCM_FLAG = 'N'
 GROUP BY MD.WEIGHT, V.WEIGHT


PLAN :

SELECT STATEMENT, GOAL = ALL_ROWS                  61021      7435      334575
 HASH GROUP BY                  61021      7435      334575
  NESTED LOOPS OUTER                  61019      7435      334575
   TABLE ACCESS FULL      GENCARGO      TRACK_DISCH_LIST      3      3      57
   VIEW      SYS            20339      2974      77324
    VIEW      SYS            20339      2974      226024
     UNION-ALL                              
      NESTED LOOPS OUTER                  20179      2513      158319
       HASH JOIN OUTER                  19      2513      62825
        TABLE ACCESS FULL      GCB_DBA      GCB_CARGO_CAT_MAPPING      9      1661      21593
        TABLE ACCESS FULL      GCB_DBA      GCB_CARGO_CAT_MAPPING_DTLS      9      2513      30156
       VIEW      SYS            8      1      38
        TABLE ACCESS FULL      GENCARGO      VEHICLELIST_DSCH      8      1      25
      FILTER                              
       TABLE ACCESS FULL      GENCARGO      VEHICLELIST_DSCH      8      461      11525
       FILTER                              
        NESTED LOOPS OUTER                  4      1      25
         TABLE ACCESS FULL      GCB_DBA      GCB_CARGO_CAT_MAPPING      2      1      13
         TABLE ACCESS BY INDEX ROWID      GCB_DBA      GCB_CARGO_CAT_MAPPING_DTLS      2      1      12
          INDEX RANGE SCAN      GCB_DBA      GCB_CARGO_CAT_MAPPING_DTLS_IND      1      1
0
Comment
Question by:sakthikumar
  • 3
  • 3
7 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40282811
Check the paran groups on:
 full outer join gencargo.vehiclelist_dsch v
     on (v.bol_no = m.bol_no and v.rotation_no = m.rotation_no and
        (v.weight = md.weight / md.quantity) OR MD.WEIGHT IS NULL)


The way the parans group it it is likely this part causing some of your issues:
OR MD.WEIGHT IS NULL

You have:  (A) and (B) and (C) or (D)

If that is what you must have, then I would look at a function-based index on:
NVL(MD.WEIGHT,99999999999999)

then you can write it as:
 full outer join gencargo.vehiclelist_dsch v
     on (v.bol_no = m.bol_no and v.rotation_no = m.rotation_no and
        (v.weight = md.weight / md.quantity) OR NVL(MD.WEIGHT,99999999999999)
 = 99999999999999)

and use the index.

Just pick a number for NVL to return that will NEVER be a legitimate weight.
0
 

Author Comment

by:sakthikumar
ID: 40285168
Dear  slightwv,

Coorect. You have identified the problem(MD.WEIGHT IS NULL) IS causing the problem.

But putting function based index doesn't help, index is not used.

Please help me resolve this.

SELECT STATEMENT, GOAL = ALL_ROWS                  40726      1      84
 FILTER                              
  HASH GROUP BY                  40726      1      84
   NESTED LOOPS OUTER                  40724      5948      499632
    VIEW      GENCARGO            4      2      40
     HASH UNIQUE                  4      2      38
      TABLE ACCESS FULL      GENCARGO      TRACK_DISCH_LIST      3      3      57
    VIEW      SYS            20360      2974      190336
     VIEW      SYS            20360      2974      339036
      UNION-ALL                              
       NESTED LOOPS OUTER                  20200      2513      221144
        HASH JOIN OUTER                  19      2513      62825
         TABLE ACCESS FULL      GCB_DBA      GCB_CARGO_CAT_MAPPING      9      1661      21593
         TABLE ACCESS FULL      GCB_DBA      GCB_CARGO_CAT_MAPPING_DTLS      9      2513      30156
        VIEW      SYS            8      1      63
         TABLE ACCESS FULL      GENCARGO      VEHICLELIST_DSCH      8      1      36
       FILTER                              
        TABLE ACCESS FULL      GENCARGO      VEHICLELIST_DSCH      8      461      16596
        FILTER                              
         NESTED LOOPS OUTER                  4      1      25
          TABLE ACCESS FULL      GCB_DBA      GCB_CARGO_CAT_MAPPING      2      1      13
          TABLE ACCESS BY INDEX ROWID      GCB_DBA      GCB_CARGO_CAT_MAPPING_DTLS      2      1      12
           INDEX RANGE SCAN      GCB_DBA      GCB_CARGO_CAT_MAPPING_DTLS_IND      1      1
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40285521
I set up a miniature test case based on what I think is the problem table.

The code is below.

I wanted to point out that reducing COST alone should not be a tuning goal.  COST is a made up number and should only be used as a guide.  Lower COST does not mean faster execution.

Keep that in mind.

Based on the test below, try the following indexes:

create index md_nvl_weight_idx on GCB_CARGO_CAT_MAPPING_DTLS(nvl(weight,999999999999));
create index md_weight_qty_idx on GCB_CARGO_CAT_MAPPING_DTLS(weight/quantity);


then add the NVL like my select to yours where you have  MD.WEIGHT IS NULL

drop table tab1 purge;
create table tab1(mapping_id number, weight number, quantity number) nologging;


begin
	for i in 1..100000 loop
		insert into tab1 values(i,i,i);
	end loop;
	for i in 100001..110000 loop
		insert into tab1 values(i,null,i);
	end loop;
	commit;
end;
/

create index tab1_weight_qty_idx on tab1(weight/quantity);


set autotrace traceonly

select md.weight md_weight
from tab1 MD
where 1 = md.weight / md.quantity OR MD.WEIGHT IS NULL
/

set autotrace off

create index tab1_nvl_weight_idx on tab1(nvl(weight,999999999999));

set autotrace traceonly

select md.weight md_weight
from tab1 MD
where 1 = md.weight / md.quantity OR nvl(weight,999999999999) = 999999999999
/

set autotrace off

Open in new window



The results of my autotrace runs:
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                            |  5687 |   144K|    71   (2)| 00:00:01 |
|   1 |  RESULT CACHE      | b715cz8v0gsrmbmk9y626v8s9d |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TAB1                       |  5687 |   144K|    71   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |  1902 | 74178 |    71   (0)| 00:00:01 |
|   1 |  RESULT CACHE                 | dad5rrxw0g2vk678ck79n955dx |       |       |            |       |
|   2 |   CONCATENATION               |                            |       |       |            |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| TAB1                       |   956 | 37284 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | TAB1_NVL_WEIGHT_IDX        |   120 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| TAB1                       |   946 | 36894 |    69   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | TAB1_NVL_WEIGHT_QTY_IDX    |   120 |       |    97   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:sakthikumar
ID: 40295189
Index picked now, but how did you identified "OR MD.WEIGHT IS NULL" is the problem.

So, In future I can probe easily.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40295930
>>but how did you identified "OR MD.WEIGHT IS NULL" is the problem.

NULL values aren't indexed.  If they are part of a multi-column index, nulls can be part of the index but not searchable as a value in the index.

Think about it:
A NULL is an unknown value.  What would you store in the index for an unknown value?
0
 

Author Comment

by:sakthikumar
ID: 40296062
Thanks slightwv.
Great.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40296239
By the way:

 1 = md.weight / md.quantity

the same as this:  

md.weight = md.quantity

which is far simpler
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question