A reader, November 11, 2022 - 8:55 am UTC
We tried with UNION ALL also and still the same
Snippet of the View as below
SELECT col1,col2,col3
from old_table1
inner join old_table2 ON something
UNION ALL
select distinct col1,col2,col3
from table_a a
inner join table_b b on a.a=b.b
inner join table_c c on b.c=c.c
inner join table_d d on c.d=d.d
left outer join table_e on d.e=e.e
and exists (select 'X' from old_table o where a.c = o.o)
UNION ALL
select distinct col1,col2,col3
from table_a a
inner join table_b b on a.d=b.d
inner join table_c c on b.c=c.c
inner join table_d d on c.d=d.d
left outer join table_e on d.e=e.e
and exists (select 'X' from old_table o where a.c = o.o and something = 'XYZ')
UNION ALL
select distinct col1,col2,col3
from table_a a
inner join table_b b on a.d=b.d
inner join table_c c on b.c=c.c
inner join table_d d on c.d=d.d
inner join old_table o1 on o1.d=d.e
left outer join table_e on d.e=e.e
and exists (select 'X' from old_table o where a.c = o.o and something = 'XYZ')
Please note here the use of 3 new UNION ALLs to cater to our requirement , 2 of which are mostly with the new set of tables, and 1 is joined with the old set of tables, and also note the use of DISTINCT in the union all.
Could this be the issue? Anyway to fine tune this or any place we are going wrong here
November 15, 2022 - 7:02 am UTC
OK, run your query with the gather_plan_statistics hint, ie,
SELECT /*+ gather_plan_statistics */ col1,col2,col3
from old_table1
inner join old_table2 ON something
UNION ALL
select distinct col1,col2,col3
from table_a a
...
...
...
and then run this immediately after:
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'));
and paste that back in here with the code tag so its nicely formatted.
A reader, November 16, 2022 - 5:10 am UTC
Plan hash value: 3541338315
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 146 (100)| 50 |00:00:00.01 | 80 |
| 1 | UNION-ALL | | 1 | | | 50 |00:00:00.01 | 80 |
| 2 | NESTED LOOPS OUTER | | 1 | 286 | 3 (34)| 50 |00:00:00.01 | 80 |
| 3 | NESTED LOOPS OUTER | | 1 | 286 | 3 (34)| 50 |00:00:00.01 | 27 |
| 4 | NESTED LOOPS | | 1 | 86 | 3 (34)| 14 |00:00:00.01 | 24 |
| 5 | NESTED LOOPS OUTER | | 1 | 33 | 3 (34)| 3 |00:00:00.01 | 21 |
| 6 | NESTED LOOPS OUTER | | 1 | 33 | 3 (34)| 3 |00:00:00.01 | 16 |
| 7 | NESTED LOOPS | | 1 | 33 | 3 (34)| 3 |00:00:00.01 | 13 |
| 8 | NESTED LOOPS OUTER | | 1 | 33 | 3 (34)| 3 |00:00:00.01 | 8 |
| 9 | MERGE JOIN OUTER | | 1 | 33 | 3 (34)| 3 |00:00:00.01 | 3 |
| 10 | TABLE ACCESS BY INDEX ROWID | Table_A | 1 | 33 | 1 (0)| 3 |00:00:00.01 | 2 |
| 11 | INDEX FULL SCAN | PK_Table_A | 1 | 33 | 1 (0)| 3 |00:00:00.01 | 1 |
|* 12 | SORT JOIN | | 3 | 15 | 2 (50)| 3 |00:00:00.01 | 1 |
| 13 | INDEX FULL SCAN | Table_B_IDX_14 | 1 | 15 | 1 (0)| 15 |00:00:00.01 | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | Table_C | 3 | 1 | 0 (0)| 3 |00:00:00.01 | 5 |
|* 15 | INDEX UNIQUE SCAN | PK_Table_A_1 | 3 | 1 | 0 (0)| 3 |00:00:00.01 | 2 |
| 16 | TABLE ACCESS BY INDEX ROWID | Table_D | 3 | 1 | 0 (0)| 3 |00:00:00.01 | 5 |
|* 17 | INDEX UNIQUE SCAN | PK_Table_A2 | 3 | 1 | 0 (0)| 3 |00:00:00.01 | 2 |
| 18 | TABLE ACCESS BY INDEX ROWID BATCHED | Table_G | 3 | 1 | 0 (0)| 3 |00:00:00.01 | 3 |
|* 19 | INDEX RANGE SCAN | Table_ATC_AUTHUSERID_IDX | 3 | 1 | 0 (0)| 3 |00:00:00.01 | 2 |
| 20 | TABLE ACCESS BY INDEX ROWID | Table_E | 3 | 1 | 0 (0)| 3 |00:00:00.01 | 5 |
|* 21 | INDEX UNIQUE SCAN | PK_Table_E | 3 | 1 | 0 (0)| 3 |00:00:00.01 | 2 |
| 22 | TABLE ACCESS BY INDEX ROWID BATCHED | Table_F | 3 | 3 | 0 (0)| 14 |00:00:00.01 | 3 |
|* 23 | INDEX RANGE SCAN | 1_IDX_9 | 3 | 3 | 0 (0)| 14 |00:00:00.01 | 2 |
|* 24 | INDEX RANGE SCAN | _IDX_10 | 14 | 3 | 0 (0)| 49 |00:00:00.01 | 3 |
| 25 | TABLE ACCESS BY INDEX ROWID | Table_FROLE | 50 | 1 | 0 (0)| 49 |00:00:00.01 | 53 |
|* 26 | INDEX UNIQUE SCAN | PK_A | 50 | 1 | 0 (0)| 49 |00:00:00.01 | 4 |
| 27 | NESTED LOOPS | | 0 | 107 | 119 (3)| 0 |00:00:00.01 | 0 |
| 28 | NESTED LOOPS | | 0 | 4 | 7 (15)| 0 |00:00:00.01 | 0 |
| 29 | NESTED LOOPS | | 0 | 3 | 7 (15)| 0 |00:00:00.01 | 0 |
| 30 | NESTED LOOPS | | 0 | 3 | 7 (15)| 0 |00:00:00.01 | 0 |
| 31 | NESTED LOOPS | | 0 | 3 | 7 (15)| 0 |00:00:00.01 | 0 |
| 32 | NESTED LOOPS | | 0 | 3 | 7 (15)| 0 |00:00:00.01 | 0 |
|* 33 | HASH JOIN | | 0 | 3 | 7 (15)| 0 |00:00:00.01 | 0 |
| 34 | SORT UNIQUE | | 0 | 42 | 3 (0)| 0 |00:00:00.01 | 0 |
|* 35 | TABLE ACCESS FULL | Table_H | 0 | 42 | 3 (0)| 0 |00:00:00.01 | 0 |
| 36 | TABLE ACCESS FULL | Table_I | 0 | 9 | 3 (0)| 0 |00:00:00.01 | 0 |
| 37 | TABLE ACCESS BY INDEX ROWID | Table_J | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 38 | INDEX UNIQUE SCAN | Table_J_PK | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 39 | TABLE ACCESS BY INDEX ROWID | Table_J1 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 40 | INDEX UNIQUE SCAN | Table_J1_PK | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 41 | TABLE ACCESS BY INDEX ROWID | Table_J2 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 42 | INDEX UNIQUE SCAN | Table_J2_PK | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 43 | TABLE ACCESS BY INDEX ROWID BATCHED | Table_J3 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 44 | INDEX RANGE SCAN | V_AU23_KNDNR_INST_AVD_IDX | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 45 | TABLE ACCESS BY INDEX ROWID BATCHED | V_AUT_AU9 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 46 | INDEX RANGE SCAN | V_AU9_BRUKERID_IDX | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 47 | COLLECTION ITERATOR CONSTRUCTOR FETCH | | 0 | 24 | 28 (0)| 0 |00:00:00.01 | 0 |
| 48 | HASH UNIQUE | | 0 | 1 | 9 (12)| 0 |00:00:00.01 | 0 |
|* 49 | HASH JOIN SEMI | | 0 | 1 | 8 (0)| 0 |00:00:00.01 | 0 |
| 50 | NESTED LOOPS OUTER | | 0 | 1 | 5 (0)| 0 |00:00:00.01 | 0 |
| 51 | NESTED LOOPS OUTER | | 0 | 1 | 5 (0)| 0 |00:00:00.01 | 0 |
| 52 | NESTED LOOPS | | 0 | 1 | 4 (0)| 0 |00:00:00.01 | 0 |
| 53 | NESTED LOOPS | | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
| 54 | NESTED LOOPS | | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
| 55 | TABLE ACCESS FULL | New_Table_A | 0 | 1 | 2 (0)| 0 |00:00:00.01 | 0 |
| 56 | TABLE ACCESS BY INDEX ROWID | New_Table_B | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|* 57 | INDEX UNIQUE SCAN | PK_New_Table_B | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 58 | TABLE ACCESS BY INDEX ROWID BATCHED | New_Table_C | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 59 | INDEX RANGE SCAN | FK_New_IDX | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 60 | TABLE ACCESS BY INDEX ROWID | Table_FDATA_XYZ | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|* 61 | INDEX UNIQUE SCAN | PK_Table_New | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 62 | INDEX RANGE SCAN | UQ_1 | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|* 63 | TABLE ACCESS BY INDEX ROWID BATCHED | New_Table_C | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 64 | INDEX RANGE SCAN | FK_1 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 65 | TABLE ACCESS FULL | Table_H | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
| 66 | HASH UNIQUE | | 0 | 1 | 7 (29)| 0 |00:00:00.01 | 0 |
| 67 | NESTED LOOPS OUTER | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 68 | NESTED LOOPS OUTER | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 69 | NESTED LOOPS | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 70 | NESTED LOOPS | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 71 | NESTED LOOPS | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 72 | NESTED LOOPS | | 0 | 1 | 5 (20)| 0 |00:00:00.01 | 0 |
| 73 | SORT UNIQUE | | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
|* 74 | TABLE ACCESS FULL | Table_H | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
| 75 | TABLE ACCESS BY INDEX ROWID BATCHED | New_Table_B | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|* 76 | INDEX RANGE SCAN | IDX_A2 | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|* 77 | INDEX RANGE SCAN | UQ_New_Table_A | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|* 78 | TABLE ACCESS BY INDEX ROWID BATCHED | New_Table_C | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 79 | INDEX RANGE SCAN | FK_New_IDX | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 80 | TABLE ACCESS BY INDEX ROWID | Table_FDATA_XYZ | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 81 | INDEX UNIQUE SCAN | Table_FDATA_XYZ_IDX_1 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 82 | TABLE ACCESS BY INDEX ROWID BATCHED | Table_FROLENAME_XYZ | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 83 | INDEX RANGE SCAN | IDX_B2 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 84 | TABLE ACCESS BY INDEX ROWID BATCHED | New_Table_C | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|* 85 | INDEX RANGE SCAN | FK_22 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 86 | HASH UNIQUE | | 0 | 1 | 8 (25)| 0 |00:00:00.01 | 0 |
| 87 | NESTED LOOPS OUTER | | 0 | 1 | 7 (15)| 0 |00:00:00.01 | 0 |
| 88 | NESTED LOOPS OUTER | | 0 | 1 | 7 (15)| 0 |00:00:00.01 | 0 |
| 89 | NESTED LOOPS | | 0 | 1 | 7 (15)| 0 |00:00:00.01 | 0 |
| 90 | NESTED LOOPS OUTER | | 0 | 1 | 7 (15)| 0 |00:00:00.01 | 0 |
| 91 | NESTED LOOPS | | 0 | 1 | 7 (15)| 0 |00:00:00.01 | 0 |
| 92 | NESTED LOOPS OUTER | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 93 | NESTED LOOPS OUTER | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 94 | NESTED LOOPS | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 95 | NESTED LOOPS | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 96 | NESTED LOOPS | | 0 | 1 | 6 (17)| 0 |00:00:00.01 | 0 |
| 97 | NESTED LOOPS | | 0 | 1 | 5 (20)| 0 |00:00:00.01 | 0 |
| 98 | SORT UNIQUE | | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
|* 99 | TABLE ACCESS FULL | Table_H | 0 | 1 | 3 (0)| 0 |00:00:00.01 | 0 |
| 100 | TABLE ACCESS BY INDEX ROWID BATCHED| New_Table_B | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|*101 | INDEX RANGE SCAN | IDX_AUTH_CUS_XYZ | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|*102 | INDEX RANGE SCAN | UQ_New_Table_A | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|*103 | TABLE ACCESS BY INDEX ROWID BATCHED | New_Table_C | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*104 | INDEX RANGE SCAN | FK_New_IDX | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 105 | TABLE ACCESS BY INDEX ROWID | Table_FDATA_XYZ | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*106 | INDEX UNIQUE SCAN | Table_FDATA_XYZ_IDX_1 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 107 | TABLE ACCESS BY INDEX ROWID BATCHED | Table_FROLENAME_XYZ | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*108 | INDEX RANGE SCAN | IDX_B | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*109 | TABLE ACCESS BY INDEX ROWID BATCHED | New_Table_C | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*110 | INDEX RANGE SCAN | FK_C | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 111 | TABLE ACCESS BY INDEX ROWID BATCHED | Table_I | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
|*112 | INDEX RANGE SCAN | TableX | 0 | 1 | 1 (0)| 0 |00:00:00.01 | 0 |
| 113 | TABLE ACCESS BY INDEX ROWID | Table_J | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*114 | INDEX UNIQUE SCAN | Table_J_PK | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 115 | TABLE ACCESS BY INDEX ROWID BATCHED | Table_J1 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*116 | INDEX RANGE SCAN | V_A1 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
| 117 | TABLE ACCESS BY INDEX ROWID | Table_J2 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*118 | INDEX UNIQUE SCAN | Table_J2_PK | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*119 | TABLE ACCESS BY INDEX ROWID BATCHED | Table_J3 | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
|*120 | INDEX RANGE SCAN | TableY | 0 | 1 | 0 (0)| 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Here is the stats
November 25, 2022 - 5:29 am UTC
This suggests the query ran virtually instantaneously no?