PARALLEL

Specifies parallel factor for parallel query execution.

SELECT /*+ PARALLEL(table_name, parallel_factor) */ ...
Mach> EXPLAIN SELECT /*+ PARALLEL(test, 8) */ sensor, frequency, avg(value)
FROM test
WHERE ts >= TO_DATE('2007-07-01', 'YYYY-MM-DD') and ts <= TO_DATE('2007-07-31', 'YYYY-MM-DD')
GROUP BY sensor,frequency;
 
PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  QPX_NODE_TYPE_GRAG                                                             
   PARALLEL INDEX SCAN                                                           
    *BITMAP RANGE (t:92, c:2, i:94)                                              
[4] row(s) selected.

Index


NOPARALLEL

Does not perform in parallel.

SELECT /*+ NOPARALLEL(table_name) */ ...
Mach> EXPLAIN SELECT /*+ NOPARALLEL(test) */ sensor, frequency, avg(value)
FROM test
WHERE ts >= TO_DATE('2007-07-01', 'YYYY-MM-DD') and ts <= TO_DATE('2007-07-31', 'YYYY-MM-DD')
GROUP BY sensor,frequency;
 
PLAN                                                                             
------------------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ                                                              
  QPX_NODE_TYPE_GRAG                                                             
   INDEX SCAN                                                                    
    *BITMAP RANGE (t:92, c:2, i:94)                                              
[4] row(s) selected.

FULL

Does not use INDEX SCAN.

SELECT /*+ FULL(table_name) */ ...
Mach> EXPLAIN SELECT * FROM TEST WHERE I1 = 1;
PLAN
------------------------------------------------------------------------------------
 PROJECT
  INDEX SCAN
   *BITMAP RANGE (t:7, c:1, i:8) with BLOOMFILTER
[3] row(s) selected.
Elapsed time: 0.001

Mach> EXPLAIN SELECT /*+ FULL(TEST) */ * FROM TEST WHERE I1 = 1;
PLAN
------------------------------------------------------------------------------------
 PROJECT
  FULL SCAN
[2] row(s) selected.

NO_INDEX

Does not use the corresponding INDEX.

SELECT /*+ NO_INDEX(table_name,index_name) */ ...
Mach> EXPLAIN SELECT * FROM TEST WHERE I1 = 1;
PLAN
------------------------------------------------------------------------------------
 PROJECT
  INDEX SCAN
   *BITMAP RANGE (t:7, c:1, i:8) with BLOOMFILTER
[3] row(s) selected.
Elapsed time: 0.001

Mach> EXPLAIN SELECT /*+ NO_INDEX(TEST,TEST_IDX) */ * FROM TEST WHERE I1 = 1;
PLAN
------------------------------------------------------------------------------------
 PROJECT
  FULL SCAN
[2] row(s) selected.

RID_RANGE

Runs within RID range.

SELECT /*+ RID_RANGE(table_name,number,number) */ ...
Mach> SELECT /*+ RID_RANGE(TEST,45,50) */ _RID, * FROM TEST;
_RID                 I1
------------------------------------
49                   1
48                   1
47                   1
46                   1
45                   1
[5] row(s) selected.

ROLLUP

It is possible to inquire statistical data in hours, minutes and seconds.

SELECT /*+ ROLLUP(table_name,(HOUR | MIN | SEC) [, aggr_func]) */ ...
Mach> EXPLAIN SELECT T_TIME, T_VALUE FROM TAG;
PLAN
------------------------------------------------------------------------------------
 PROJECT
  TAG READ (RAW)
   KEYVALUE FULL SCAN
[3] row(s) selected.


Mach> EXPLAIN SELECT /*+ ROLLUP(TAG, SEC) */ T_TIME, T_VALUE FROM TAG;
PLAN
------------------------------------------------------------------------------------
 PROJECT
  GROUP AGGREGATE
   TAG READ (SEC, AVG)
    KEYVALUE FULL SCAN
[4] row(s) selected.
 
Mach> EXPLAIN SELECT /*+ ROLLUP(TAG, SEC, MAX) */ T_TIME, T_VALUE FROM TAG;
PLAN
------------------------------------------------------------------------------------
 PROJECT
  GROUP AGGREGATE
   TAG READ (SEC, MAX)
    KEYVALUE FULL SCAN
[4] row(s) selected.
  • No labels