Search

PARALLEL_INDEX HINT





The PARALLEL_INDEX hint instructs the optimizer to use the specified number of concurrent servers to parallelize index range scans, full scans, and fast full scans for partitioned indexes.


The single most common mistake with parallel_index hint is using it against a non-partitioned index, where it will be ignored.

The parallel_index hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.

When using the parallel_index hint, specifying DEFAULT or no DEGREE value tells the query coordinator check the initialization parameter default degree of parallelism (parallel_automatic_tuning).


create table t1
as
with generator as (
        select  
                rownum id
        from dual
        connect by
                level <= 50000
)
select
        rownum                  id,
        mod(rownum,5000)         modded,
        lpad(rownum,10,'0')     v1,
        lpad('x',30,'x')        padding
from
        generator       v1,
        generator       v2
where
        rownum <= 50000
;
-----------------------------------------------------------
create index t1_i1 on t1(modded);

Check the execution Plan for the below query.




Select /*+ parallel_index(t1 t1_i1 20) */ count(modded) from t1;
----------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows  | Bytes  | Cost | Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |     1 |      4 |   31 | 00:00:01 |
|  1 |   SORT AGGREGATE            |          |     1 |      4 |      |          |
|  2 |    PX COORDINATOR           |          |       |        |      |          |
|  3 |     PX SEND QC (RANDOM)     | :TQ10000 |     1 |      4 |      |          |
|  4 |      SORT AGGREGATE         |          |     1 |      4 |      |          |
|  5 |       PX BLOCK ITERATOR     |          | 50000 | 200000 |   31 | 00:00:01 |
|  6 |        INDEX FAST FULL SCAN | T1_I1    | 50000 | 200000 |   31 | 00:00:01 |
----------------------------------------------------------------------------------


Lets do a comparison of the above plan with other index hints



select /*+ index_ffs(t1 t1_i1) */ count(modded) from t1;
 Plan Hash Value  : 2608623722 

---------------------------------------------------------------------------
| Id | Operation               | Name  | Rows  | Bytes  | Cost | Time     |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |       |     1 |      4 |   31 | 00:00:01 |
|  1 |   SORT AGGREGATE        |       |     1 |      4 |      |          |
|  2 |    INDEX FAST FULL SCAN | T1_I1 | 50000 | 200000 |   31 | 00:00:01 |
---------------------------------------------------------------------------





select /*+ parallel_index(t1 t1_i1 20) parallel(t1 10) */ count(modded) from t1;
----------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows  | Bytes  | Cost | Time     |
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |     1 |      4 |    2 | 00:00:01 |
|  1 |   SORT AGGREGATE            |          |     1 |      4 |      |          |
|  2 |    PX COORDINATOR           |          |       |        |      |          |
|  3 |     PX SEND QC (RANDOM)     | :TQ10000 |     1 |      4 |      |          |
|  4 |      SORT AGGREGATE         |          |     1 |      4 |      |          |
|  5 |       PX BLOCK ITERATOR     |          | 50000 | 200000 |    2 | 00:00:01 |
|  6 |        INDEX FAST FULL SCAN | T1_I1    | 50000 | 200000 |    2 | 00:00:01 |

Also go through FISRT_ROWS hint in oracle.


9 views
 
  • LinkedIn
  • Facebook