create table as_fact as select * from all_objects where rownum <=10000Table created.
create index as_fact_idx on as_fact(owner) Index created.
create table as_dimension as select * from all_users where rownum <=10 Table created.
explain plan for
select 1,(select 1 from dual) e
from as_dimension d
join (select * from as_fact union all select * from as_fact) f
on d.username = f.owner
where trunc(d.created) = to_date('17-FEB-2020','DD-MON-YYYY') Statement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 3626378065 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 200 | 4400 | 21 (0)| 00:00:01 | | | 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | | 2 | NESTED LOOPS | | 200 | 4400 | 19 (0)| 00:00:01 | | |* 3 | TABLE ACCESS FULL | AS_DIMENSION | 1 | 16 | 3 (0)| 00:00:01 | | | 4 | VIEW | | 667 | 4002 | 16 (0)| 00:00:01 | | | 5 | UNION ALL PUSHED PREDICATE | | | | | | | |* 6 | INDEX RANGE SCAN | AS_FACT_IDX | 3333 | 19998 | 8 (0)| 00:00:01 | | |* 7 | INDEX RANGE SCAN | AS_FACT_IDX | 3333 | 19998 | 8 (0)| 00:00:01 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - filter(TRUNC(INTERNAL_FUNCTION("D"."CREATED"))=TO_DATE(' 2020-02-17 00:00:00', | 'syyyy-mm-dd hh24:mi:ss')) | 6 - access("AS_FACT"."OWNER"="D"."USERNAME") | 7 - access("AS_FACT"."OWNER"="D"."USERNAME") |
|---|
explain plan for
select 1,(select 1 from table(sys.odcinumberlist(1))) e
from as_dimension d
join (select * from as_fact union all select * from as_fact) f
on d.username = f.owner
where trunc(d.created) = to_date('17-FEB-2020','DD-MON-YYYY') Statement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 2848673079 | ------------------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 667 | 14674 | 13 (0)| 00:00:01 | | | 1 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | | 2 (0)| 00:00:01 | | | 2 | NESTED LOOPS | | 667 | 14674 | 11 (0)| 00:00:01 | | |* 3 | TABLE ACCESS FULL | AS_DIMENSION | 1 | 16 | 3 (0)| 00:00:01 | | | 4 | VIEW | | 6667 | 40002 | 8 (0)| 00:00:01 | | | 5 | UNION-ALL PARTITION | | | | | | | |* 6 | INDEX RANGE SCAN | AS_FACT_IDX | 3333 | 19998 | 8 (0)| 00:00:01 | | |* 7 | INDEX RANGE SCAN | AS_FACT_IDX | 3333 | 19998 | 8 (0)| 00:00:01 | | ------------------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - filter(TRUNC(INTERNAL_FUNCTION("D"."CREATED"))=TO_DATE(' 2020-02-17 00:00:00', | 'syyyy-mm-dd hh24:mi:ss')) | 6 - access("AS_FACT"."OWNER"="D"."USERNAME") | 7 - access("AS_FACT"."OWNER"="D"."USERNAME") |
|---|