Skip to Main Content
  • Questions
  • New tables creation and their behavior

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: November 10, 2022 - 10:56 am UTC

Last updated: November 25, 2022 - 5:29 am UTC

Version: Oracle 19c

Viewed 100+ times

You Asked

Hello,

We have created 5 new tables ( Table_A - around 80K records, Table_B- around 15K records, Table_C - around 35K records, Table_D- around 55K records, and Table_E- around 130K records)

These tables have been used in a view using a UNION ( The view had another set of tables from which it would fetch the data and now these new tables have been unioned)

We find that soon after the data load, it takes a very long time to fetch result from the view and the application is causing a timeout..

We figured that this might be due to the Oracle statistics getting generated at later point, so we generated the stats manually soon after the data load, still the same problem.

Indexes are in place. Can anyone please point out what might be the issue here? or any inputs on how we can figure this out is appreciated.




and Connor said...

Well, we'd probably need to see the DDL for the view and how its being used.

But maybe your UNION really needs to be a UNION ALL?

UNION = get the data from both sides of the union and then remove any duplicates
UNION ALL = get the data from both sides of the union all

The need to remove duplicates can be very expensive and can reduce optimizer options to run your query


Rating

  (2 ratings)

Comments

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

Connor McDonald
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
Connor McDonald
November 25, 2022 - 5:29 am UTC

This suggests the query ran virtually instantaneously no?

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.