drop table t1 purgeTable dropped.
drop table t2 purgeTable dropped.
drop table t3 purgeTable dropped.
create table t1 as
select * from all_objects
where rownum <= 10000 -- > comment to avoid wordpress format issue Table created.
alter table t1 add constraint t1_pk primary key(object_id)Table altered.
create table t2 as
select * from t1 Table created.
alter table t2 add constraint t2_pk primary key(object_id)Table altered.
create table t3 as
select * from all_objects
where rownum <= 500 -- > comment to avoid wordpress format issue Table created.
alter table t3 add constraint t3_pk primary key(object_id)Table altered.
explain plan for
select
v2.*
from (
select
t1.object_id,
t1.object_name t1_name,
v1.object_name t2_name,
t1.object_type t1_type,
v1.object_type t2_type
from
t1
join (
select
t2.*,
(
select t3.object_type
from t3
where t3.object_id = t2.object_id
) t3_type
from
t2
order by
t2.object_id
) v1
on
v1.object_id = t1.object_id
and v1.object_type = 'TABLE'
) v2 Statement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 2446888267 | ----------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ----------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 500 | 66500 | 380 (1)| 00:00:01 | | |* 1 | HASH JOIN | | 500 | 66500 | 380 (1)| 00:00:01 | | | 2 | VIEW | | 500 | 46000 | 315 (1)| 00:00:01 | | | 3 | SORT ORDER BY | | 500 | 62500 | 315 (1)| 00:00:01 | | |* 4 | TABLE ACCESS FULL| T2 | 500 | 62500 | 64 (0)| 00:00:01 | | | 5 | TABLE ACCESS FULL | T1 | 10000 | 400K| 64 (0)| 00:00:01 | | ----------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - access("V1"."OBJECT_ID"="T1"."OBJECT_ID") | 4 - filter("T2"."OBJECT_TYPE"='TABLE') | Note | ----- | - this is an adaptive plan |
|---|
explain plan for
create table t4 as
select
v2.*
from (
select
t1.object_id,
t1.object_name t1_name,
v1.object_name t2_name,
t1.object_type t1_type,
v1.object_type t2_type
from
t1
join (
select
t2.*,
(
select t3.object_type
from t3
where t3.object_id = t2.object_id
) t3_type
from
t2
order by
t2.object_id
) v1
on
v1.object_id = t1.object_id
and v1.object_type = 'TABLE'
) v2 Statement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 1843098714 | ------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------ | | 0 | CREATE TABLE STATEMENT | | 500 | 66500 | 385 (1)| 00:00:01 | | | 1 | LOAD AS SELECT | T4 | | | | | | | 2 | OPTIMIZER STATISTICS GATHERING | | 500 | 66500 | 380 (1)| 00:00:01 | | |* 3 | HASH JOIN | | 500 | 66500 | 380 (1)| 00:00:01 | | | 4 | VIEW | | 500 | 46000 | 315 (1)| 00:00:01 | | | 5 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 12 | 1 (0)| 00:00:01 | | |* 6 | INDEX UNIQUE SCAN | T3_PK | 1 | | 0 (0)| 00:00:01 | | | 7 | SORT ORDER BY | | 500 | 62500 | 315 (1)| 00:00:01 | | |* 8 | TABLE ACCESS FULL | T2 | 500 | 62500 | 64 (0)| 00:00:01 | | | 9 | TABLE ACCESS FULL | T1 | 10000 | 400K| 64 (0)| 00:00:01 | | ------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("V1"."OBJECT_ID"="T1"."OBJECT_ID") | 6 - access("T3"."OBJECT_ID"=:B1) | 8 - filter("T2"."OBJECT_TYPE"='TABLE') |
|---|