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>