SQL & PL/SQL

Hi,

I tried to move 1 particular LOB partition from one tablespace to another tablespace using parallel clause, but it was taking time & my understanding would be its going in serial fashion (parallel was not effect), Can you pls clarify ?

I was referring some doc's, then from the execution plan "LOAD AS SELECT" comes before "Query Coordinator", So my feeling would be it went in serial not parallel. Pls correct me if I am wrong ? Thanks

SQL> /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b9hry1x4scfqx
--------------------
alter table core.clo
Plan hash value: 2771440782
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |            |       |       |   105 (100)|          |       |       |        |      |            |
|   1 |  LOAD AS SELECT        |            |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |            |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000   |   834K|   117M|   105   (0)| 00:00:02 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR  |            |   834K|   117M|   105   (0)| 00:00:02 |     4 |     4 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL | CLOB_DATA_ |   834K|   117M|   105   (0)| 00:00:02 |     4 |     4 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   5 - SEL$1 / CLOB_DATA_@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      OPT_PARAM('_optimizer_cost_based_transformation' 'off')
      OPT_PARAM('_optimizer_compute_index_stats' 'false')
      OPT_PARAM('_optimizer_better_inlist_costing' 'off')
      OPT_PARAM('_optimizer_extended_stats_usage_control' 255)
      OPT_PARAM('_optimizer_distinct_agg_transform' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_fix_control' '6146906:0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "CLOB_DATA_"@"SEL$1")
      END_OUTLINE_DATA
  */

Sql Statement

alter session force parallel ddl parallel 64;
alter table core.clob_data_ move partition clob_data04 tablespace NEW_D01 lob (LOB_DATA) store as (tablespace NEW_D01);