Tuning required for full outer join

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
sakthikumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
sakthikumarAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sakthikumarAuthor Commented:
Index picked now, but how did you identified "OR MD.WEIGHT IS NULL" is the problem.

So, In future I can probe easily.
0
slightwv (䄆 Netminder) Commented:
>>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
sakthikumarAuthor Commented:
Thanks slightwv.
Great.
0
PortletPaulfreelancerCommented:
By the way:

 1 = md.weight / md.quantity

the same as this:  

md.weight = md.quantity

which is far simpler
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.