/
SELECT Hint

SELECT Hint

Owned by Ryan Park (Unlicensed)
Last updated: Nov 11, 2021 by Grey Shim


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.

SCAN_FORWARD, SCAN_BACKWARD

Specifies the direction of scanning for TAGDATA table. With SCAN_FORWARD, the oldest record input is retrieved first, whereas with SCAN_BACKWARD, the newest record input is retrieved first.

These hints have no effect on LOG tables.

SELECT /*+ SCAN_FORWARD(table_name) */ ...
SELECT /*+ SCAN_BACKWARD(table_name) */ ...


Mach> SELECT /*+ SCAN_FORWARD(tag) */  * FROM tag WHERE t_name='TAG_99' LIMIT 10;
T_NAME                T_TIME                          T_VALUE                    
--------------------------------------------------------------------------------------
TAG_99                2017-01-01 00:00:49 500:000:000 0                          
TAG_99                2017-01-01 00:01:39 500:000:000 1                          
TAG_99                2017-01-01 00:02:29 500:000:000 2                          
TAG_99                2017-01-01 00:03:19 500:000:000 3                          
TAG_99                2017-01-01 00:04:09 500:000:000 4                          
TAG_99                2017-01-01 00:04:59 500:000:000 5                          
TAG_99                2017-01-01 00:05:49 500:000:000 6                          
TAG_99                2017-01-01 00:06:39 500:000:000 7                          
TAG_99                2017-01-01 00:07:29 500:000:000 8                          
TAG_99                2017-01-01 00:08:19 500:000:000 9                          
[10] row(s) selected.

Mach> SELECT /*+ SCAN_BACKWARD(tag) */ * FROM tag WHERE t_name='TAG_99' LIMIT 10;
T_NAME                T_TIME                          T_VALUE                    
--------------------------------------------------------------------------------------
TAG_99                2017-02-27 20:53:19 500:000:000 9                          
TAG_99                2017-02-27 20:52:29 500:000:000 8                          
TAG_99                2017-02-27 20:51:39 500:000:000 7                          
TAG_99                2017-02-27 20:50:49 500:000:000 6                          
TAG_99                2017-02-27 20:49:59 500:000:000 5                          
TAG_99                2017-02-27 20:49:09 500:000:000 4                          
TAG_99                2017-02-27 20:48:19 500:000:000 3                          
TAG_99                2017-02-27 20:47:29 500:000:000 2                          
TAG_99                2017-02-27 20:46:39 500:000:000 1                          
TAG_99                2017-02-27 20:45:49 500:000:000 0                          
[10] row(s) selected.

Mach>

Related content