Versions Compared

Version Old Version 10 New Version 11
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> EXPLAIN SELECT /*+ NOPARALLEL(testRID_RANGE(TEST,45,50) */ sensor_RID, 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;
 
PLANTEST;
_RID                                                                                        
------------I1
------------------------------------------------------------------------
 QPX_NODE_TYPE_PROJ
49                   1
48                   1
47                   1
46      QPX_NODE_TYPE_GRAG             1
45                                               
   INDEX SCAN                                                                    
    *BITMAP RANGE (t:92, c:2, i:94)                                              
[41
[5] row(s) selected.
Elapsed time: 0.000001

ROLLUP

ROLLUP TABLE

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


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