Oracle SQL Plan operation: CREATE TABLE STATEMENT

CREATE TABLE AS SELECT

In a create table … as select (CTAS) statement, the execution plan contains LOAD AS SELECT operator:

explain plan for
   create table dual_copy as
   select * from dual;

select * from table(dbms_xplan.display(format => 'basic'))
--
-- ------------------------------------------------------
-- | Id  | Operation                        | Name      |
-- ------------------------------------------------------
-- |   0 | CREATE TABLE STATEMENT           |           |
-- |   1 |  LOAD AS SELECT                  | DUAL_COPY |
-- |   2 |   OPTIMIZER STATISTICS GATHERING |           |
-- |   3 |    TABLE ACCESS FULL             | DUAL      |
-- ------------------------------------------------------

Scalar subqueries

Usually, the child operator of CREATE TABLE STATMENT in CTAS statements is LOAD AS SELECT.

If one of the columns involves a scalar subquery, the plans for the subquery are children of the CREATE TABLE STATEMENET operator:

create table tq84_A (
   id  integer primary key,
   val number(5,2),
   ltr char(1)
);
 
create table tq84_B (
    ltr  char(1)
);
 
begin
   insert into tq84_A values (1, 413.01, 'a');
   insert into tq84_A values (2, 170.67, 'b');
   insert into tq84_A values (3, 859.85, 'c');
   insert into tq84_A values (4, 529.55, 'd');
   insert into tq84_A values (5, 437.13, 'e');
 
   insert into tq84_B values ('a');
   insert into tq84_B values ('e');
   insert into tq84_B values ('i');
end;
/
 
 
explain plan for
create table tq84_X as
select
   a.id,
   a.ltr,
   case when exists (select null from tq84_b b where b.ltr = a.ltr) then 'y' else 'n' end exists_
from
  tq84_A a;
 
select * from dbms_xplan.display(format => 'basic');
--
-- ---------------------------------------------------
-- | Id  | Operation                        | Name   |
-- ---------------------------------------------------
-- |   0 | CREATE TABLE STATEMENT           |        |
-- |   1 |  TABLE ACCESS FULL               | TQ84_B |
-- |   2 |  LOAD AS SELECT                  | TQ84_X |
-- |   3 |   OPTIMIZER STATISTICS GATHERING |        |
-- |   4 |    TABLE ACCESS FULL             | TQ84_A |
-- ---------------------------------------------------
 
 
drop table tq84_A;
drop table tq84_b;

See also SQL plan for scalar subqueries.