create table as_fact as select owner, object_name, subobject_name, object_id, created from all_objectsTable created.
create table as_dimension as select * from all_usersTable created.
create index as_fact_owner on as_fact (owner)Index created.
explain plan for
select *
from as_fact f
join as_dimension d
on d.username = f.owner
where d.user_id = 1Statement processed.
select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 2118260961 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 2025 | 259K| 36 (0)| 00:00:01 | | | 1 | NESTED LOOPS | | 2025 | 259K| 36 (0)| 00:00:01 | | | 2 | NESTED LOOPS | | 2025 | 259K| 36 (0)| 00:00:01 | | |* 3 | TABLE ACCESS FULL | AS_DIMENSION | 1 | 72 | 8 (0)| 00:00:01 | | |* 4 | INDEX RANGE SCAN | AS_FACT_OWNER | 2025 | | 4 (0)| 00:00:01 | | | 5 | TABLE ACCESS BY INDEX ROWID| AS_FACT | 2025 | 116K| 28 (0)| 00:00:01 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - filter("D"."USER_ID"=1) | 4 - access("D"."USERNAME"="F"."OWNER") | Note | ----- | - this is an adaptive plan |
|---|
explain plan for
select *
from (select * from as_fact union all select 'SYS','DUMMY', null,0, sysdate from dual) f
join as_dimension d
on d.username = f.owner
where d.user_id = 1Statement processed.
select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 2122170290 | ------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 42 | 12264 | 166 (1)| 00:00:01 | | |* 1 | HASH JOIN | | 42 | 12264 | 166 (1)| 00:00:01 | | |* 2 | TABLE ACCESS FULL | AS_DIMENSION | 1 | 72 | 8 (0)| 00:00:01 | | | 3 | VIEW | | 52653 | 11M| 158 (1)| 00:00:01 | | | 4 | UNION-ALL | | | | | | | | 5 | TABLE ACCESS FULL| AS_FACT | 52652 | 3033K| 156 (1)| 00:00:01 | | | 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | ------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - access("D"."USERNAME"="F"."OWNER") | 2 - filter("D"."USER_ID"=1) |
|---|
create table as_dual as select 'SYS' owner,'DUMMY' object_name, cast(null as varchar2(30)) subobject_name,0 object_id, sysdate created from dualTable created.
create index as_dual_idx on as_dual (owner)Index created.
explain plan for
select *
from (select * from as_fact union all select owner,'DUMMY', null,0, sysdate from as_dual) f
join as_dimension d
on d.username = f.owner
where d.user_id = 1Statement processed.
select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 806721089 | -------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 42 | 9702 | 38 (0)| 00:00:01 | | | 1 | NESTED LOOPS | | 42 | 9702 | 38 (0)| 00:00:01 | | |* 2 | TABLE ACCESS FULL | AS_DIMENSION | 1 | 72 | 8 (0)| 00:00:01 | | | 3 | VIEW | | 2 | 318 | 30 (0)| 00:00:01 | | | 4 | UNION ALL PUSHED PREDICATE | | | | | | | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| AS_FACT | 2025 | 116K| 29 (0)| 00:00:01 | | |* 6 | INDEX RANGE SCAN | AS_FACT_OWNER | 2025 | | 5 (0)| 00:00:01 | | |* 7 | INDEX RANGE SCAN | AS_DUAL_IDX | 1 | 4 | 1 (0)| 00:00:01 | | -------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("D"."USER_ID"=1) | 6 - access("AS_FACT"."OWNER"="D"."USERNAME") | 7 - access("OWNER"="D"."USERNAME") |
|---|