Versions Compared

Version Old Version 14 New Version 15
Changes made by

Hyeonmin Lee (Unlicensed)

Hyeonmin Lee (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.


PARALLEL

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

Code Block
SELECT /*+ PARALLEL(table_name, parallel_factor) */ ...


Code Block
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.



Ui text box

목차

Table of Contents
maxLevel1
indent30px
exclude목차
classtoc


NOPARALLEL

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

Code Block
SELECT /*+ NOPARALLEL(table_name) */ ...


Code Block
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.
Elapsed time: 0.000

FULL

INDEX SCAN을 사용하지 않는다.

Code Block
SELECT /*+ FULL(table_name) */ ...


Code Block
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.
Elapsed time: 0.001

NO_INDEX

해당하는 INDEX를 사용하지 않는다.

Code Block
SELECT /*+ NO_INDEX(table_name,index_name) */ ...


Code Block
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.
Elapsed time: 0.001

RID_RANGE

RID 범위 내 에서 내에서 수행한다.

Code Block
SELECT /*+ RID_RANGE(table_name,number,number) */ ...


Code Block
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.
Elapsed time: 0.001

ROLLUP

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

Code Block
SELECT /*+ ROLLUP(table_name,(HOUR | MIN | SEC) [, aggr_func]) */ ...


Code Block
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.
Elapsed time: 0.001
 
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.
Elapsed time: 0.001