Search

PARALLEL_INDEX HINT

Updated: Nov 25, 2020





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 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.


12 views0 comments

Recent Posts

See All
 
  • LinkedIn
  • Facebook