索引并行扫描

After the optimizer determines the execution plan of a statement, the parallel
execution coordinator determines the parallelization method for each operation in the
plan. For example, the parallelization method might be to parallelize a full table scan
by block range or parallelize an index range scan by partition. The coordinator must
decide whether an operation can be performed in parallel and, if so, how many
parallel execution servers to enlist.

1.index fast full scan

对于index fast full scan，即使是普通索引，也可以实现并行读取。

sys@EBANK>SELECT /*+ PARALLEL_INDEX(t, idx_test, 2) */ count(*) from test t;

Execution Plan
----------------------------------------------------------
Plan hash value: 870284884

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |     1 |    58   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE           |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |          | 99309 |    58   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       INDEX FAST FULL SCAN| IDX_TEST | 99309 |    58   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------

2.index range scan

对于index range scan的并行，只有在分区索引的情况下才有效，而对于非分区索引，是无效的。

sys@EBANK>create table tt as select * from dba_objects;

Table created.

sys@EBANK>sys@EBANK>create index tt_index on tt(object_id) global partition by hash(object_id) partitions 4;

Index created.

sys@EBANK>sys@EBANK>exec dbms_stats.gather_table_stats(user,'TT');

PL/SQL procedure successfully completed.

sys@EBANK>sys@EBANK > set autotrace trace

sys@EBANK>sys@EBANK>select /*+ parallel_index(tt,tt_index,2)*/object_id from tt where object_id<30;

28 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2004946057

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    26 |   104 |     2   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |    26 |   104 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION HASH ALL|          |    26 |   104 |     2   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWC |            |
|*  4 |     INDEX RANGE SCAN    | TT_INDEX |    26 |   104 |     2   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("OBJECT_ID"<30)

Statistics
----------------------------------------------------------
        919  recursive calls
          3  db block gets
        176  consistent gets
          2  physical reads
        680  redo size
        741  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
         28  rows processed

再看一下非分区索引的情况：

sys@EBANK>drop index tt_index ;

Index dropped.

sys@EBANK>create index tt_index on tt(object_id) tablespace users;

Index created.

sys@EBANK>select /*+ parallel_index(tt,tt_index,2)*/object_id from tt where object_id<30;

Execution Plan
----------------------------------------------------------
Plan hash value: 3896265072

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |    26 |   104 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TT_INDEX |    26 |   104 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"<30)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-678430/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-678430/