Oracle Live SQL - Script: NoJPPDWithConnectBy

Statement 1

select * from v$version


BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0

BANNER	BANNER_FULL	BANNER_LEGACY	CON_ID
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	0

Statement 2

drop view as_dim_view

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 3

drop view as_fact_sqlview

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 4

drop view as_fact_mainview

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 5

drop view as_fact_subview

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 6

drop table as_fact1 purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 7

drop table as_fact2 purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 8

drop table as_fact3 purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 9

drop table as_fact4 purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 10

drop table as_fact5 purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 11

drop table as_fact_main purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 12

drop table as_dimension purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 13

drop table temp_table purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 14

create table as_fact1 as select owner, object_name, subobject_name, object_id, created from (select owner, object_name, subobject_name, object_id, created, ntile(5) over (order by object_type) grp from all_objects) where grp = 1

Table created.

Statement 15

create table as_fact2 as select owner, object_name, subobject_name, object_id, created from (select owner, object_name, subobject_name, object_id, created, ntile(5) over (order by object_type) grp from all_objects) where grp = 2

Table created.

Statement 16

create table as_fact3 as select owner, object_name, subobject_name, object_id, created from (select owner, object_name, subobject_name, object_id, created, ntile(5) over (order by object_type) grp from all_objects) where grp = 3

Table created.

Statement 17

create table as_fact4 as select owner, object_name, subobject_name, object_id, created from (select owner, object_name, subobject_name, object_id, created, ntile(5) over (order by object_type) grp from all_objects) where grp = 4

Table created.

Statement 18

create table as_fact5 as select owner, object_name, subobject_name, object_id, created from (select owner, object_name, subobject_name, object_id, created, ntile(5) over (order by object_type) grp from all_objects) where grp = 5

Table created.

Statement 19

create table as_fact_main as select owner, object_name, subobject_name, object_id, created from all_objects

Table created.

Statement 20

create table temp_table as select level num_val from dual connect by level <= 10000

Table created.

Statement 21

create table as_dimension as select * from all_users

Table created.

Statement 22

create index as_fact1_idx1 on as_fact1(owner)

Index created.

Statement 23

create index as_fact2_idx1 on as_fact2(owner)

Index created.

Statement 24

create index as_fact3_idx1 on as_fact3(owner)

Index created.

Statement 25

create index as_fact4_idx1 on as_fact4(owner)

Index created.

Statement 26

create index as_fact5_idx1 on as_fact5(owner)

Index created.

Statement 27

create or replace view as_fact_subview    
as    
select * from as_fact1    
union    
select * from as_fact2    
union    
select * from as_fact3

View created.

Statement 28

create or replace view as_fact_mainview 
as    
select * from as_fact4 
union    
select * from as_fact_subview

View created.

Statement 29

create or replace view as_fact_sqlview    
as    
select /*+ PUSH_PRED(as_fact_mainview) */ as_fact_main.owner, as_fact_main.object_id,as_fact_main.created,as_fact_mainview.object_name from as_fact_main, as_fact_mainview 
where as_fact_main.owner = as_fact_mainview.owner(+)

View created.

Statement 30

create or replace view as_dim_view as select * from as_dimension asd, table(cast(multiset(select level from dual connect by level < length(asd.username))as sys.odcinumberlist)) asd_t

View created.

Statement 31

delete from plan_table

21 row(s) deleted.

Statement 32

commit

Statement processed.

Statement 33

delete from plan_table

0 row(s) deleted.

Statement 34

commit

Statement processed.

Statement 35

commit

Statement processed.

Statement 36

explain plan for      
select /*+ NO_MERGE(f) NO_MERGE(d) */ *      
from   as_fact_sqlview   f     
left outer join  as_dim_view d  on d.username = f.owner     
where  trunc(f.created) = to_date('17-FEB-2020','DD-MON-YYYY')

Statement processed.

Statement 37

select * from table(dbms_xplan.display(format => 'TYPICAL +ALIAS +OUTLINE'))


PLAN_TABLE_OUTPUT
Error: cannot fetch last explain plan from PLAN_TABLE

PLAN_TABLE_OUTPUT
Error: cannot fetch last explain plan from PLAN_TABLE

Statement 38

delete from plan_table

0 row(s) deleted.

Statement 39

commit

Statement processed.

Statement 40

explain plan for      
select /*+ NO_MERGE(f) NO_MERGE(d) */ *      
from   as_fact_sqlview   f     
left outer join  as_dim_view d  on d.username = f.owner     
where  trunc(f.created) = to_date('17-FEB-2020','DD-MON-YYYY')

Statement processed.

Statement 41

select * from table(dbms_xplan.display(format => 'TYPICAL +ALIAS +OUTLINE'))


PLAN_TABLE_OUTPUT
Error: cannot fetch last explain plan from PLAN_TABLE

PLAN_TABLE_OUTPUT
Error: cannot fetch last explain plan from PLAN_TABLE

Statement 42

delete from plan_table

0 row(s) deleted.

Statement 43

commit

Statement processed.

Statement 44

explain plan for      
select /*+ NO_MERGE(f) NO_MERGE(d) */ *      
from   as_fact_sqlview   f     
left outer join  as_dim_view d  on d.username = f.owner     
where  trunc(f.created) = to_date('17-FEB-2020','DD-MON-YYYY')

Statement processed.

Statement 45

select * from table(dbms_xplan.display(format => 'TYPICAL +ALIAS +OUTLINE'))


PLAN_TABLE_OUTPUT
Error: cannot fetch last explain plan from PLAN_TABLE

PLAN_TABLE_OUTPUT
Error: cannot fetch last explain plan from PLAN_TABLE

Statement 46

delete from plan_table

0 row(s) deleted.

Statement 47

commit

Statement processed.

Statement 48

delete from plan_table

0 row(s) deleted.

Statement 49

commit

Statement processed.

Statement 50

explain plan for      
select /*+ NO_MERGE(f) NO_MERGE(d) */ *      
from   as_fact_sqlview   f     
left outer join  as_dim_view d  on d.username = f.owner     
where  trunc(f.created) = to_date('17-FEB-2020','DD-MON-YYYY')

Statement processed.

Statement 51

select * from table(dbms_xplan.display(format => 'TYPICAL +ALIAS +OUTLINE'))


PLAN_TABLE_OUTPUT
Plan hash value: 1170889197
 
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |  6968M|  2083G|       |   207K (16)| 00:00:09 |
|*  1 |  HASH JOIN OUTER                      |                  |  6968M|  2083G|   135M|   207K (16)| 00:00:09 |
|   2 |   VIEW                                | AS_FACT_SQLVIEW  |   853K|   125M|       |  3795   (1)| 00:00:01 |
|*  3 |    HASH JOIN OUTER                    |                  |   853K|   122M|       |  3795   (1)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL                 | AS_FACT_MAIN     |   527 |  9486 |       |   158   (2)| 00:00:01 |
|   5 |     VIEW                              | AS_FACT_MAINVIEW | 42124 |  5430K|       |  3633   (1)| 00:00:01 |
|   6 |      SORT UNIQUE                      |                  | 42124 |  7384K|  6496K|  3633   (1)| 00:00:01 |
|   7 |       UNION-ALL                       |                  |       |       |       |            |          |
|   8 |        TABLE ACCESS FULL              | AS_FACT4         | 10531 |   596K|       |    35   (0)| 00:00:01 |
|   9 |        VIEW                           | AS_FACT_SUBVIEW  | 31593 |  6787K|       |   613   (2)| 00:00:01 |
|  10 |         SORT UNIQUE                   |                  | 31593 |  1902K|  2376K|   613   (2)| 00:00:01 |
|  11 |          UNION-ALL                    |                  |       |       |       |            |          |
|  12 |           TABLE ACCESS FULL           | AS_FACT1         | 10531 |   637K|       |    37   (0)| 00:00:01 |
|  13 |           TABLE ACCESS FULL           | AS_FACT2         | 10531 |   678K|       |    39   (0)| 00:00:01 |
|  14 |           TABLE ACCESS FULL           | AS_FACT3         | 10531 |   586K|       |    34   (0)| 00:00:01 |
|  15 |   VIEW                                | AS_DIM_VIEW      |    11M|  1767M|       | 43541   (1)| 00:00:02 |
|  16 |    NESTED LOOPS                       |                  |    11M|   783M|       | 43541   (1)| 00:00:02 |
|  17 |     TABLE ACCESS FULL                 | AS_DIMENSION     |  1359 | 97848 |       |     8   (0)| 00:00:01 |
|  18 |     COLLECTION ITERATOR SUBQUERY FETCH|                  |  8168 | 16336 |       |    32   (0)| 00:00:01 |
|* 19 |      CONNECT BY WITHOUT FILTERING     |                  |       |       |       |            |          |
|  20 |       FAST DUAL                       |                  |     1 |       |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1A05ABD1
   2 - SEL$2        / F@SEL$1
   3 - SEL$2       
   4 - SEL$2        / AS_FACT_MAIN@SEL$2
   5 - SET$1        / AS_FACT_MAINVIEW@SEL$2
   6 - SET$1       
   8 - SEL$3        / AS_FACT4@SEL$3
   9 - SET$2        / AS_FACT_SUBVIEW@SEL$4
  10 - SET$2       
  12 - SEL$5        / AS_FACT1@SEL$5
  13 - SEL$6        / AS_FACT2@SEL$6
  14 - SEL$7        / AS_FACT3@SEL$7
  15 - SEL$D4C8709B / D@SEL$1
  16 - SEL$D4C8709B
  17 - SEL$D4C8709B / ASD@SEL$8
  18 - SEL$D4C8709B / KOKBF$0@SEL$9
  19 - SEL$10      
  20 - SEL$10       / DUAL@SEL$10
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$5" "AS_FACT1"@"SEL$5")
      FULL(@"SEL$6" "AS_FACT2"@"SEL$6")
      FULL(@"SEL$7" "AS_FACT3"@"SEL$7")
      FULL(@"SEL$3" "AS_FACT4"@"SEL$3")
      NO_ACCESS(@"SEL$4" "AS_FACT_SUBVIEW"@"SEL$4")
      CONNECT_BY_COMBINE_SW(@"SEL$10")
      NO_CONNECT_BY_FILTERING(@"SEL$10")
      USE_NL(@"SEL$D4C8709B" "KOKBF$0"@"SEL$9")
      LEADING(@"SEL$D4C8709B" "ASD"@"SEL$8" "KOKBF$0"@"SEL$9")
      FULL(@"SEL$D4C8709B" "KOKBF$0"@"SEL$9")
      FULL(@"SEL$D4C8709B" "ASD"@"SEL$8")
      USE_HASH(@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
      LEADING(@"SEL$2" "AS_FACT_MAIN"@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
      NO_ACCESS(@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
      FULL(@"SEL$2" "AS_FACT_MAIN"@"SEL$2")
      USE_HASH(@"SEL$1A05ABD1" "D"@"SEL$1")
      LEADING(@"SEL$1A05ABD1" "F"@"SEL$1" "D"@"SEL$1")
      NO_ACCESS(@"SEL$1A05ABD1" "D"@"SEL$1")
      NO_ACCESS(@"SEL$1A05ABD1" "F"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$75A38DF4")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$546317DF")
      ANSI_REARCH(@"SEL$1")
      OUTLINE(@"SEL$9A6AFCDE")
      OUTLINE(@"SEL$11")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$9793510F")
      MERGE(@"SEL$546317DF" >"SEL$9A6AFCDE")
      OUTLINE(@"SEL$163E3CF7")
      ANSI_REARCH(@"SEL$11")
      OUTLINE(@"SEL$90C0FE98")
      MERGE(@"SEL$9" >"SEL$8")
      OUTLINE(@"SEL$9C1B3155")
      MERGE(@"SEL$163E3CF7" >"SEL$90C0FE98")
      OUTLINE_LEAF(@"SEL$1A05ABD1")
      OUTLINE_LEAF(@"SEL$D4C8709B")
      OUTLINE_LEAF(@"SEL$10")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SET$2")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$3")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D"."USERNAME"(+)="F"."OWNER")
   3 - access("AS_FACT_MAIN"."OWNER"="AS_FACT_MAINVIEW"."OWNER"(+))
   4 - filter(TRUNC(INTERNAL_FUNCTION("AS_FACT_MAIN"."CREATED"))=TO_DATE(' 2020-02-17 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
  19 - filter(LEVEL<LENGTH(:B1))
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   2 -  SEL$2 / AS_FACT_MAINVIEW@SEL$2
         U -  PUSH_PRED(as_fact_mainview)

123 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 1170889197

------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \|TempSpc\| Cost (%CPU)\| Time \|
------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 6968M\| 2083G\| \| 207K (16)\| 00:00:09 \|
\|* 1 \| HASH JOIN OUTER \| \| 6968M\| 2083G\| 135M\| 207K (16)\| 00:00:09 \|
\| 2 \| VIEW \| AS_FACT_SQLVIEW \| 853K\| 125M\| \| 3795 (1)\| 00:00:01 \|
\|* 3 \| HASH JOIN OUTER \| \| 853K\| 122M\| \| 3795 (1)\| 00:00:01 \|
\|* 4 \| TABLE ACCESS FULL \| AS_FACT_MAIN \| 527 \| 9486 \| \| 158 (2)\| 00:00:01 \|
\| 5 \| VIEW \| AS_FACT_MAINVIEW \| 42124 \| 5430K\| \| 3633 (1)\| 00:00:01 \|
\| 6 \| SORT UNIQUE \| \| 42124 \| 7384K\| 6496K\| 3633 (1)\| 00:00:01 \|
\| 7 \| UNION-ALL \| \| \| \| \| \| \|
\| 8 \| TABLE ACCESS FULL \| AS_FACT4 \| 10531 \| 596K\| \| 35 (0)\| 00:00:01 \|
\| 9 \| VIEW \| AS_FACT_SUBVIEW \| 31593 \| 6787K\| \| 613 (2)\| 00:00:01 \|
\| 10 \| SORT UNIQUE \| \| 31593 \| 1902K\| 2376K\| 613 (2)\| 00:00:01 \|
\| 11 \| UNION-ALL \| \| \| \| \| \| \|
\| 12 \| TABLE ACCESS FULL \| AS_FACT1 \| 10531 \| 637K\| \| 37 (0)\| 00:00:01 \|
\| 13 \| TABLE ACCESS FULL \| AS_FACT2 \| 10531 \| 678K\| \| 39 (0)\| 00:00:01 \|
\| 14 \| TABLE ACCESS FULL \| AS_FACT3 \| 10531 \| 586K\| \| 34 (0)\| 00:00:01 \|
\| 15 \| VIEW \| AS_DIM_VIEW \| 11M\| 1767M\| \| 43541 (1)\| 00:00:02 \|
\| 16 \| NESTED LOOPS \| \| 11M\| 783M\| \| 43541 (1)\| 00:00:02 \|
\| 17 \| TABLE ACCESS FULL \| AS_DIMENSION \| 1359 \| 97848 \| \| 8 (0)\| 00:00:01 \|
\| 18 \| COLLECTION ITERATOR SUBQUERY FETCH\| \| 8168 \| 16336 \| \| 32 (0)\| 00:00:01 \|
\|* 19 \| CONNECT BY WITHOUT FILTERING \| \| \| \| \| \| \|
\| 20 \| FAST DUAL \| \| 1 \| \| \| 2 (0)\| 00:00:01 \|
------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1A05ABD1
2 - SEL$2 / F@SEL$1
3 - SEL$2
4 - SEL$2 / AS_FACT_MAIN@SEL$2
5 - SET$1 / AS_FACT_MAINVIEW@SEL$2
6 - SET$1
8 - SEL$3 / AS_FACT4@SEL$3
9 - SET$2 / AS_FACT_SUBVIEW@SEL$4
10 - SET$2
12 - SEL$5 / AS_FACT1@SEL$5
13 - SEL$6 / AS_FACT2@SEL$6
14 - SEL$7 / AS_FACT3@SEL$7
15 - SEL$D4C8709B / D@SEL$1
16 - SEL$D4C8709B
17 - SEL$D4C8709B / ASD@SEL$8
18 - SEL$D4C8709B / KOKBF$0@SEL$9
19 - SEL$10
20 - SEL$10 / DUAL@SEL$10

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$5" "AS_FACT1"@"SEL$5")
FULL(@"SEL$6" "AS_FACT2"@"SEL$6")
FULL(@"SEL$7" "AS_FACT3"@"SEL$7")
FULL(@"SEL$3" "AS_FACT4"@"SEL$3")
NO_ACCESS(@"SEL$4" "AS_FACT_SUBVIEW"@"SEL$4")
CONNECT_BY_COMBINE_SW(@"SEL$10")
NO_CONNECT_BY_FILTERING(@"SEL$10")
USE_NL(@"SEL$D4C8709B" "KOKBF$0"@"SEL$9")
LEADING(@"SEL$D4C8709B" "ASD"@"SEL$8" "KOKBF$0"@"SEL$9")
FULL(@"SEL$D4C8709B" "KOKBF$0"@"SEL$9")
FULL(@"SEL$D4C8709B" "ASD"@"SEL$8")
USE_HASH(@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
LEADING(@"SEL$2" "AS_FACT_MAIN"@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
NO_ACCESS(@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
FULL(@"SEL$2" "AS_FACT_MAIN"@"SEL$2")
USE_HASH(@"SEL$1A05ABD1" "D"@"SEL$1")
LEADING(@"SEL$1A05ABD1" "F"@"SEL$1" "D"@"SEL$1")
NO_ACCESS(@"SEL$1A05ABD1" "D"@"SEL$1")
NO_ACCESS(@"SEL$1A05ABD1" "F"@"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$75A38DF4")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$546317DF")
ANSI_REARCH(@"SEL$1")
OUTLINE(@"SEL$9A6AFCDE")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$9793510F")
MERGE(@"SEL$546317DF" >"SEL$9A6AFCDE")
OUTLINE(@"SEL$163E3CF7")
ANSI_REARCH(@"SEL$11")
OUTLINE(@"SEL$90C0FE98")
MERGE(@"SEL$9" >"SEL$8")
OUTLINE(@"SEL$9C1B3155")
MERGE(@"SEL$163E3CF7" >"SEL$90C0FE98")
OUTLINE_LEAF(@"SEL$1A05ABD1")
OUTLINE_LEAF(@"SEL$D4C8709B")
OUTLINE_LEAF(@"SEL$10")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SET$2")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$3")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

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

1 - access("D"."USERNAME"(+)="F"."OWNER")
3 - access("AS_FACT_MAIN"."OWNER"="AS_FACT_MAINVIEW"."OWNER"(+))
4 - filter(TRUNC(INTERNAL_FUNCTION("AS_FACT_MAIN"."CREATED"))=TO_DATE(' 2020-02-17 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
19 - filter(LEVEL<LENGTH(:B1))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

2 - SEL$2 / AS_FACT_MAINVIEW@SEL$2
U - PUSH_PRED(as_fact_mainview)

Statement 52

create or replace view as_dim_view as select * from as_dimension asd, table(cast(multiset(select num_val from temp_table where num_val <= length(asd.username))as sys.odcinumberlist)) asd_t

View created.

Statement 53

delete from plan_table

0 row(s) deleted.

Statement 54

commit

Statement processed.

Statement 55

explain plan for      
select /*+ NO_MERGE(f) NO_MERGE(d) */ *      
from   as_fact_sqlview   f     
left outer join  as_dim_view d  on d.username = f.owner     
where  trunc(f.created) = to_date('17-FEB-2020','DD-MON-YYYY')

Statement processed.

Statement 56

select * from table(dbms_xplan.display(format => 'TYPICAL +ALIAS +OUTLINE'))


PLAN_TABLE_OUTPUT
Error: cannot fetch last explain plan from PLAN_TABLE

PLAN_TABLE_OUTPUT
Error: cannot fetch last explain plan from PLAN_TABLE

Statement 57

delete from plan_table

21 row(s) deleted.

Statement 58

commit

Statement processed.

Statement 59

delete from plan_table

0 row(s) deleted.

Statement 60

commit

Statement processed.

Statement 61

explain plan for      
select /*+ NO_MERGE(f) NO_MERGE(d) */ *      
from   as_fact_sqlview   f     
left outer join  as_dim_view d  on d.username = f.owner     
where  trunc(f.created) = to_date('17-FEB-2020','DD-MON-YYYY')

Statement processed.

Statement 62

select * from table(dbms_xplan.display(format => 'TYPICAL +ALIAS +OUTLINE'))


PLAN_TABLE_OUTPUT
Plan hash value: 1251545928
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                  |   426M|   127G|       |  1229K  (1)| 00:00:49 |
|*  1 |  HASH JOIN RIGHT OUTER                       |                  |   426M|   127G|   116M|  1229K  (1)| 00:00:49 |
|   2 |   VIEW                                       | AS_DIM_VIEW      |   679K|   108M|       |    21  (24)| 00:00:01 |
|   3 |    MERGE JOIN                                |                  |   679K|    49M|       |    21  (24)| 00:00:01 |
|   4 |     SORT JOIN                                |                  |  1359 | 97848 |       |     9  (12)| 00:00:01 |
|   5 |      TABLE ACCESS FULL                       | AS_DIMENSION     |  1359 | 97848 |       |     8   (0)| 00:00:01 |
|*  6 |     SORT JOIN                                |                  | 10000 | 40000 |       |     9  (12)| 00:00:01 |
|   7 |      TABLE ACCESS FULL                       | TEMP_TABLE       | 10000 | 40000 |       |     8   (0)| 00:00:01 |
|   8 |   VIEW                                       | AS_FACT_SQLVIEW  |   853K|   125M|       |  1211K  (1)| 00:00:48 |
|   9 |    NESTED LOOPS OUTER                        |                  |   853K|    69M|       |  1211K  (1)| 00:00:48 |
|* 10 |     TABLE ACCESS FULL                        | AS_FACT_MAIN     |   527 |  9486 |       |   158   (2)| 00:00:01 |
|  11 |     VIEW                                     | AS_FACT_MAINVIEW |   870 | 59160 |       |  2299   (1)| 00:00:01 |
|  12 |      SORT UNIQUE                             |                  | 22624 |  4732K|  3496K|  2299   (1)| 00:00:01 |
|  13 |       UNION ALL PUSHED PREDICATE             |                  |       |       |       |            |          |
|  14 |        TABLE ACCESS BY INDEX ROWID BATCHED   | AS_FACT4         |   810 | 46980 |       |    12   (0)| 00:00:01 |
|* 15 |         INDEX RANGE SCAN                     | AS_FACT4_IDX1    |   810 |       |       |     2   (0)| 00:00:01 |
|  16 |        VIEW                                  | AS_FACT_SUBVIEW  | 21814 |  4686K|       |   434   (2)| 00:00:01 |
|  17 |         SORT UNIQUE                          |                  | 21814 |  1310K|  1640K|   434   (2)| 00:00:01 |
|  18 |          UNION-ALL                           |                  |       |       |       |            |          |
|  19 |           TABLE ACCESS BY INDEX ROWID BATCHED| AS_FACT1         |   752 | 46624 |       |    10   (0)| 00:00:01 |
|* 20 |            INDEX RANGE SCAN                  | AS_FACT1_IDX1    |   752 |       |       |     2   (0)| 00:00:01 |
|* 21 |           TABLE ACCESS FULL                  | AS_FACT2         | 10531 |   678K|       |    39   (0)| 00:00:01 |
|* 22 |           TABLE ACCESS FULL                  | AS_FACT3         | 10531 |   586K|       |    34   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1A05ABD1
   2 - SEL$60B2167F / D@SEL$1
   3 - SEL$60B2167F
   5 - SEL$60B2167F / ASD@SEL$8
   7 - SEL$60B2167F / TEMP_TABLE@SEL$10
   8 - SEL$2        / F@SEL$1
   9 - SEL$2       
  10 - SEL$2        / AS_FACT_MAIN@SEL$2
  11 - SET$5715CE2E / AS_FACT_MAINVIEW@SEL$2
  12 - SET$5715CE2E
  14 - SEL$B01C6807 / AS_FACT4@SEL$3
  15 - SEL$B01C6807 / AS_FACT4@SEL$3
  16 - SET$2        / AS_FACT_SUBVIEW@SEL$4
  17 - SET$2       
  19 - SEL$5        / AS_FACT1@SEL$5
  20 - SEL$5        / AS_FACT1@SEL$5
  21 - SEL$6        / AS_FACT2@SEL$6
  22 - SEL$7        / AS_FACT3@SEL$7
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "AS_FACT1"@"SEL$5")
      INDEX_RS_ASC(@"SEL$5" "AS_FACT1"@"SEL$5" ("AS_FACT1"."OWNER"))
      FULL(@"SEL$6" "AS_FACT2"@"SEL$6")
      FULL(@"SEL$7" "AS_FACT3"@"SEL$7")
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B01C6807" "AS_FACT4"@"SEL$3")
      INDEX_RS_ASC(@"SEL$B01C6807" "AS_FACT4"@"SEL$3" ("AS_FACT4"."OWNER"))
      NO_ACCESS(@"SEL$8E13D68A" "AS_FACT_SUBVIEW"@"SEL$4")
      USE_MERGE(@"SEL$60B2167F" "TEMP_TABLE"@"SEL$10")
      LEADING(@"SEL$60B2167F" "ASD"@"SEL$8" "TEMP_TABLE"@"SEL$10")
      FULL(@"SEL$60B2167F" "TEMP_TABLE"@"SEL$10")
      FULL(@"SEL$60B2167F" "ASD"@"SEL$8")
      USE_NL(@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
      LEADING(@"SEL$2" "AS_FACT_MAIN"@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
      NO_ACCESS(@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2")
      FULL(@"SEL$2" "AS_FACT_MAIN"@"SEL$2")
      SWAP_JOIN_INPUTS(@"SEL$1A05ABD1" "D"@"SEL$1")
      USE_HASH(@"SEL$1A05ABD1" "D"@"SEL$1")
      LEADING(@"SEL$1A05ABD1" "F"@"SEL$1" "D"@"SEL$1")
      NO_ACCESS(@"SEL$1A05ABD1" "D"@"SEL$1")
      NO_ACCESS(@"SEL$1A05ABD1" "F"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$75A38DF4")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$546317DF")
      ANSI_REARCH(@"SEL$1")
      OUTLINE(@"SEL$9A6AFCDE")
      OUTLINE(@"SEL$11")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$9793510F")
      MERGE(@"SEL$546317DF" >"SEL$9A6AFCDE")
      OUTLINE(@"SEL$163E3CF7")
      ANSI_REARCH(@"SEL$11")
      OUTLINE(@"SEL$90C0FE98")
      OUTLINE(@"SEL$10")
      MERGE(@"SEL$9" >"SEL$8")
      OUTLINE(@"SEL$9C1B3155")
      OUTLINE(@"SET$1")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      MERGE(@"SEL$163E3CF7" >"SEL$90C0FE98")
      OUTLINE_LEAF(@"SEL$1A05ABD1")
      MERGE(@"SEL$10" >"SEL$9C1B3155")
      OUTLINE_LEAF(@"SEL$60B2167F")
      OUTLINE_LEAF(@"SEL$2")
      PUSH_PRED(@"SEL$2" "AS_FACT_MAINVIEW"@"SEL$2" 2)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$8E13D68A")
      OUTLINE_LEAF(@"SET$2")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$B01C6807")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("D"."USERNAME"(+)="F"."OWNER")
   6 - access(INTERNAL_FUNCTION("NUM_VAL")<=LENGTH("ASD"."USERNAME"))
       filter(INTERNAL_FUNCTION("NUM_VAL")<=LENGTH("ASD"."USERNAME"))
  10 - filter(TRUNC(INTERNAL_FUNCTION("AS_FACT_MAIN"."CREATED"))=TO_DATE(' 2020-02-17 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss'))
  15 - access("OWNER"="AS_FACT_MAIN"."OWNER")
  20 - access("OWNER"="AS_FACT_MAIN"."OWNER")
  21 - filter("OWNER"="AS_FACT_MAIN"."OWNER")
  22 - filter("OWNER"="AS_FACT_MAIN"."OWNER")

128 rows selected.

NoJPPDWithConnectBy

Additional Information