Search notes:

Oracle SQL Plan operation: LOAD AS SELECT (CURSOR DURATION MEMORY)

LOAD AS SELECT (CURSOR DURATION MEMORY) is always a child of a TEMP TABLE TRANSFORMATION operation.
explain plan for
with
   numbers as (select level as n from dual connect by level <= 100)
select
   n1.n           n1,
   n2.n           n2,
   n1.n * n2.n as product
from
   numbers n1  join
   numbers n2  on n1.n <= n2.n;
  
 
 
select * from dbms_xplan.display(format=>'basic');
--
-- --------------------------------------------------------------------------------
-- | Id  | Operation                                | Name                        |
-- --------------------------------------------------------------------------------
-- |   0 | SELECT STATEMENT                         |                             |
-- |   1 |  TEMP TABLE TRANSFORMATION               |                             |
-- |   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D858D_E7611E22 |
-- |   3 |    CONNECT BY WITHOUT FILTERING          |                             |
-- |   4 |     FAST DUAL                            |                             |
-- |   5 |   NESTED LOOPS                           |                             |
-- |   6 |    VIEW                                  |                             |
-- |   7 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D858D_E7611E22 |
-- |   8 |    VIEW                                  |                             |
-- |   9 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D858D_E7611E22 |
-- --------------------------------------------------------------------------------

See also

LOAD AS SELECT
Plan operations

Index