Query optimization - avoid full outer join

Question

below is the data scripts used.

create table enab_test (ban varchar2(10),DVR_IND char(1)); 
create table oms_test (ban varchar2(10),DVR_IND char(1));

insert into enab_test values('111111111','Y');
insert into enab_test values('123456789','N');
insert into enab_test values('321654987','N');
insert into enab_test values('444444444','Y');
insert into enab_test values('111111111','Y');
insert into enab_test values('741258963','Y');

insert into oms_test values('111111111','Y');
insert into oms_test values('222222222','N');
insert into oms_test values('333333333','N');
insert into oms_test values('444444444','N');
insert into oms_test values('555555555','Y');
insert into oms_test values('666666666','N');

Here i'm trying to get the count of bans present in enab_test table where if the ban having dvr_ind = 'Y' then it is counted as one and if there are many with same ban then it is summed up. if there are same ban in Oms with dvr_ind = 'Y'. the count is subracted like enab_test - oms_test for bans. if we have any negative count while subracting from enab_test - oms_test then the count is made zero. and viceversa(oms_test-enab_test).

below is the query I worked on.

select nvl(enab.ENAB_COUNT,0) as ENAB_COUNT, 
case when nvl(enab.ENAB_COUNT,0) - nvl(oms.OMS_COUNT,0) > 0
   then nvl(enab.ENAB_COUNT,0) - nvl(oms.OMS_COUNT,0)
   else 0
end as IN_ENAB_NOT_OMS
from (
select ban, count(*) as OMS_COUNT from oms_test 
where dvr_ind = 'Y'
group by ban order by ban
) oms
full outer join (
select ban, count(*) as ENAB_COUNT from enab_test 
where dvr_ind = 'Y'
group by ban order by ban
) enab
on oms.BAN = enab.BAN

Below is the output of the query

ENAB_COUNT  IN_ENAB_NOT_OMS
    2               1
    0               0
    1               1
    1               1

Run Plan For the above query with original data is

    Plan hash value: 2599843784

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |  6705K|   166M|       |    10M  (9)| 33:33:17 |
|   1 |  VIEW                 | VW_FOJ_0                |  6705K|   166M|       |    10M  (9)| 33:33:17 |
|*  2 |   HASH JOIN FULL OUTER|                         |  6705K|   255M|   204M|    10M  (9)| 33:33:17 |
|   3 |    VIEW               |                         |  6703K|   127M|       |  4767K  (9)| 15:53:31 |
|   4 |     SORT GROUP BY     |                         |  6703K|    76M|    14G|  4767K  (9)| 15:53:31 |
|*  5 |      TABLE ACCESS FULL| OMS_TEST                |   764M|  8749M|       |  3335K  (7)| 11:07:08 |
|   6 |    VIEW               |                         |  6705K|   127M|       |  5278K  (9)| 17:35:37 |
|   7 |     SORT GROUP BY     |                         |  6705K|    76M|    15G|  5278K  (9)| 17:35:37 |
|*  8 |      TABLE ACCESS FULL| ENAB_TEST               |   805M|  9215M|       |  3770K  (7)| 12:34:05 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OMS"."BAN"="ENAB"."BAN")
   5 - filter("DVR_IND"='Y')
   8 - filter("DVR_IND"='Y')

I have created the above query and works fine on smaller data. its taking too much time if i'm having lot of records.Is there any way to remove full outer join and optimize the query or any other ways to optimize ?

Optimization without execution plan, knowledge of table statistics, and existing indexes is near impossible. We'd be taking a best guess approach. Provide plan, table stats and existing indexes to get a better qualified response. On the surface: order by isn't needed in the inline views.. — xQbert, Feb 03 '16 at 17:20
I'm sorry @xQbert. I'm new to this and somehow i managed to create the above query. All the information i have is mentioned above in the question . And I have around 2136000 rows of data in both the tables. it takes around 58 mins to complete the query. if you can help me i'll be the happiest person on the earth — Sharath Samala, Feb 03 '16 at 17:30
@SharathChandra To generate the explain plan run `explain plan for select nvl...` and then run `select * from table(dbms_xplan.display);` and add the entire output to the question. It might also help if you could create a full test case. What you have is already pretty close, it may just require putting a loop around the INSERTs. — Jon Heller, Feb 03 '16 at 19:04
`select AI.Owner, AI.Index_name, AI.Table_owner, AI.table_name, AI.Distinct_Keys, AI.Num_rows,AIC.column_Name, AIC.Column_Position from all_indexes AI inner join all_ind_columns aic on ai.owner = aic.index_owner and ai.index_name = aic.index_name where ai.table_name = 'CUSTOMER'--ai.table_name in ('enab_test','oms_test') and AI.uniqueness = 'UNIQUE'` would identify if there are unique indexes and the columns involved. — xQbert, Feb 03 '16 at 22:18

Query optimization - avoid full outer join

0 Answers0