Search notes:

Oracle SQL Plan operation INDEX MAINTENANCE

The plan operation INDEX MAINTENANCE seems to always have the child row source PX RECEIVE.

Note that the name this operator refers to is the name of a table, not of an index.

create table tq84_src (
   id   number,
   val  varchar2(20)
);

create table tq84_dest (
   id   number,
   val  varchar2(20),
   constraint tq84_dest_pk primary key (id)
);


insert into tq84_src
select
   rownum,
   rpad('*', 20 '*')
from
   dual connect by level <= 100000;

begin
   dbms_stats.gather_table_stats(user, 'tq84_src' );
   dbms_stats.gather_table_stats(user, 'tq84_dest');
end;
/


explain plan for
   insert /*+ enable_parallel_dml parallel(4) */
     into tq84_dest
   select *
     from tq84_src;

select * from table(dbms_xplan.display(format=>'basic'));
--
-- -----------------------------------------------------------
-- | Id  | Operation                             | Name      |
-- -----------------------------------------------------------
-- |   0 | INSERT STATEMENT                      |           |
-- |   1 |  PX COORDINATOR                       |           |
-- |   2 |   PX SEND QC (RANDOM)                 | :TQ10001  |
-- |   3 |    INDEX MAINTENANCE                  | TQ84_DEST |
-- |   4 |     PX RECEIVE                        |           |
-- |   5 |      PX SEND RANGE                    | :TQ10000  |
-- |   6 |       LOAD AS SELECT (HYBRID TSM/HWMB)| TQ84_DEST |
-- |   7 |        OPTIMIZER STATISTICS GATHERING |           |
-- |   8 |         PX BLOCK ITERATOR             |           |
-- |   9 |          TABLE ACCESS FULL            | TQ84_SRC  |
-- -----------------------------------------------------------

drop table tq84_src;
drop table tq84_dest;

Oracle SQL Plan operation INDEX MAINTENANCE

See also