let startDate = new Date(); let elapsedTime = 0; const focus = function() { startDate = new Date(); }; const blur = function() { const endDate = new Date(); const spentTime = endDate.getTime() - startDate.getTime(); elapsedTime += spentTime; }; const beforeunload = function() { const endDate = new Date(); const spentTime = endDate.getTime() - startDate.getTime(); elapsedTime += spentTime; // elapsedTime contains the time spent on page in milliseconds }; window.addEventListener('focus', focus); window.addEventListener('blur', blur); window.addEventListener('beforeunload', beforeunload);
 
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.


14 views0 comments

Recent Posts

See All