Search notes:

Oracle SQL Plan operation: LOAD AS SELECT (HYBRID TSM/HWMB)

TSM stands for Temp Segment Merge, HWMB for High Water Mark Brokering.

Typically, LOAD AS SELECT (HYBRID TSM/HWMB) has one child row-source: OPTIMIZER STATISTIC GATHERING. In rare cases, I've also seen TABLE ACCESS BY INDEX ROWID BATCHED as second row source.

The parent (consuming) row-source is a PX SEND with the options QC (RANDOM) or RANGE (or others?)

create table tq84_src as select * from dba_objects;
 
create table tq84_dest (
   id   number,
   own  varchar2(128),
   nam  varchar2(128),
   typ  varchar2( 20)
);
 
insert /*+ enable_parallel_dml parallel(4) */ into tq84_dest
select
   object_id,
   owner,
   object_name,
   object_type
from
   tq84_src;
  
select * from dbms_xplan.display(format => 'basic);
-- 
-- --------------------------------------------------------
-- | Id  | Operation                          | Name      |
-- --------------------------------------------------------
-- |   0 | INSERT STATEMENT                   |           |
-- |   1 |  PX COORDINATOR                    |           |
-- |   2 |   PX SEND QC (RANDOM)              | :TQ10000  |
-- |   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| TQ84_DEST |
-- |   4 |     OPTIMIZER STATISTICS GATHERING |           |
-- |   5 |      PX BLOCK ITERATOR             |           |
-- |   6 |       TABLE ACCESS FULL            | TQ84_SRC  |
-- --------------------------------------------------------

drop table tq84_src;
drop table tq84_dest;

Oracle SQL Plan operation: LOAD AS SELECT (HYBRID TSM/HWMB)

See also