/
SELECT Hint

SELECT Hint

Owned by Former user (Deleted)
Last updated: Nov 09, 2021 by Kyle Song (Unlicensed)


PARALLEL

Parallel query 수행을 위한 parallel factor를 지정한다.

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.

NOPARALLEL

병렬로 수행되지 않도록 한다.

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

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

해당하는 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

RID 범위 내에서 수행한다.

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

시, 분, 초 단위의 통계데이터를 조회할 수 있다.

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

TAG 테이블의 스캔 방향을 지정한다. SCAN_FORWARD를 힌트로 사용하면 가장 먼저 입력한 레코드 우선으로, SCAN_BACKWARD를 힌트로 사용하면 가장 나중에 입력한 레코드 우선으로 조회한다.

LOG 테이블에 대해서는 효과가 없다.

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