[오라클 질문] rowid 와 direct path load 연관 질문입니다.

2009-12-11 10:26:25 에 등록된 질문입니다. 김종훈 님께서 질문해 주셨습니다.

SQL> desc rowidtest
이름                        널?    유형
----------------------------------------- -------- ----------------------------
ROWIDCOL                            VARCHAR2(18)

 

SQL> SELECT * FROM ROWIDTEST;    <== 아래는 FULL SCAN한 결과로 ROWIDCOL에 ROWID에 있는 값들을 그대로 WHERE CURRENT 하게 넣었습니다.
 
ROWID            ROWIDCOL
================    ===================
AAANlCAAHAABKqkAAA  AAANlCAAHAABKqkAAA
AAANlCAAHAABKqkAAB  AAANlCAAHAABKqkAAB
AAANlCAAHAABKqkAAC  AAANlCAAHAABKqkAAC
AAANlCAAHAABKqkAAD  AAANlCAAHAABKqkAAD
AAANlCAAHAABKqkAAE  AAANlCAAHAABKqkAAE
AAANlCAAHAABKqkAAF  AAANlCAAHAABKqkAAF
AAANlCAAHAABKqkAAG  AAANlCAAHAABKqkAAG
AAANlCAAHAABKqkAAH  AAANlCAAHAABKqkAAH
AAANlCAAHAABKqkAAI  AAANlCAAHAABKqkAAI
AAANlCAAHAABKqkAAJ  AAANlCAAHAABKqkAAJ
AAANlCAAHAABKqkAAK  AAANlCAAHAABKqkAAK
AAANlCAAHAABKqkAAL  AAANlCAAHAABKqkAAL
AAANlCAAHAABKqkAAM  AAANlCAAHAABKqkAAM
AAANlCAAHAABKqkAAN  AAANlCAAHAABKqkAAN
AAANlCAAHAABKqkAAO  AAANlCAAHAABKqkAAO
AAANlCAAHAABKqkAAP  AAANlCAAHAABKqkAAP
AAANlCAAHAABKqkAAQ  AAANlCAAHAABKqkAAQ
AAANlCAAHAABKqkAAR  AAANlCAAHAABKqkAAR
AAANlCAAHAABKqkAAS  AAANlCAAHAABKqkAAS
AAANlCAAHAABKqkAAT  AAANlCAAHAABKqkAAT
AAANlCAAHAABKqkAAU  AAANlCAAHAABKqkAAU
AAANlCAAHAABKqkAAV  AAANlCAAHAABKqkAAV
AAANlCAAHAABKqkAAW  AAANlCAAHAABKqkAAW
AAANlCAAHAABKqkAAX  AAANlCAAHAABKqkAAX
AAANlCAAHAABKqkAAY  AAANlCAAHAABKqkAAY
AAANlCAAHAABKqkAAZ  AAANlCAAHAABKqkAAZ
AAANlCAAHAABKqkAAa  AAANlCAAHAABKqkAAa
AAANlCAAHAABKqkAAb  AAANlCAAHAABKqkAAb
AAANlCAAHAABKqkAAc  AAANlCAAHAABKqkAAc
AAANlCAAHAABKqkAAd  AAANlCAAHAABKqkAAd
AAANlCAAHAABKqkAAe  AAANlCAAHAABKqkAAe
AAANlCAAHAABKqkAAf  AAANlCAAHAABKqkAAf
AAANlCAAHAABKqkAAg  AAANlCAAHAABKqkAAg
AAANlCAAHAABKqkAAh  AAANlCAAHAABKqkAAh
AAANlCAAHAABKqkAAi  AAANlCAAHAABKqkAAi
AAANlCAAHAABKqkAAj  AAANlCAAHAABKqkAAj
AAANlCAAHAABKqkAAk  AAANlCAAHAABKqkAAk
AAANlCAAHAABKqkAAl  AAANlCAAHAABKqkAAl
AAANlCAAHAABKqkAAm  AAANlCAAHAABKqkAAm
AAANlCAAHAABKqkAAn  AAANlCAAHAABKqkAAn
AAANlCAAHAABKqkAAo  AAANlCAAHAABKqkAAo
AAANlCAAHAABKqkAAp  AAANlCAAHAABKqkAAp
AAANlCAAHAABKqkAAq  AAANlCAAHAABKqkAAq
AAANlCAAHAABKqkAAr  AAANlCAAHAABKqkAAr
AAANlCAAHAABKqkAAs  AAANlCAAHAABKqkAAs
AAANlCAAHAABKqkAAt  AAANlCAAHAABKqkAAt
AAANlCAAHAABKqkAAu  AAANlCAAHAABKqkAAu
AAANlCAAHAABKqkAAv  AAANlCAAHAABKqkAAv
AAANlCAAHAABKqkAAw  AAANlCAAHAABKqkAAw
AAANlCAAHAABKqkAAx  AAANlCAAHAABKqkAAx
AAANlCAAHAABKqkAAy  AAANlCAAHAABKqkAAy
AAANlCAAHAABKqkAAz  AAANlCAAHAABKqkAAz
AAANlCAAHAABKqkAA0  AAANlCAAHAABKqkAA0
AAANlCAAHAABKqkAA1  AAANlCAAHAABKqkAA1
AAANlCAAHAABKqkAA2  AAANlCAAHAABKqkAA2
AAANlCAAHAABKqkAA3  AAANlCAAHAABKqkAA3
AAANlCAAHAABKqkAA4  AAANlCAAHAABKqkAA4
AAANlCAAHAABKqkAA5  AAANlCAAHAABKqkAA5
AAANlCAAHAABKqkAA6  AAANlCAAHAABKqkAA6
AAANlCAAHAABKqkAA7  AAANlCAAHAABKqkAA7
AAANlCAAHAABKqkAA8  AAANlCAAHAABKqkAA8
AAANlCAAHAABKqkAA9  AAANlCAAHAABKqkAA9
AAANlCAAHAABKqkAA+  AAANlCAAHAABKqkAA+
AAANlCAAHAABKqkAA/  AAANlCAAHAABKqkAA/
AAANlCAAHAABKqkABA  AAANlCAAHAABKqkABA
AAANlCAAHAABKqkABB  AAANlCAAHAABKqkABB
AAANlCAAHAABKqkABC  AAANlCAAHAABKqkABC
AAANlCAAHAABKqkABD  AAANlCAAHAABKqkABD
AAANlCAAHAABKqkABE  AAANlCAAHAABKqkABE
AAANlCAAHAABKqkABF  AAANlCAAHAABKqkABF
AAANlCAAHAABKqkABG  AAANlCAAHAABKqkABG
AAANlCAAHAABKqkABH  AAANlCAAHAABKqkABH
AAANlCAAHAABKqkABI  AAANlCAAHAABKqkABI
AAANlCAAHAABKqkABJ  AAANlCAAHAABKqkABJ
AAANlCAAHAABKqkABK  AAANlCAAHAABKqkABK
AAANlCAAHAABKqkABL  AAANlCAAHAABKqkABL
AAANlCAAHAABKqkABM  AAANlCAAHAABKqkABM

SQL> CREATE INDEX SSCPOWN.IX_ROWIDTEST_COL ON SSCPOWN.ROWIDTEST
  (ROWIDCOL);

 

SQL> select min(rowid),max(rowid) from rowidtest;

MIN(ROWID)      MAX(ROWID)
------------------ ------------------
AAANlCAAHAABKqkAAA AAANlCAAHAABKqkABM

SQL> select min(rowidcol),max(rowidcol) from rowidtest;

MIN(ROWIDCOL)                MAX(ROWIDCOL)
------------------------------------ ------------------------------------
AAANlCAAHAABKqkAA+            AAANlCAAHAABKqkABM

근데 바로 위의 두 SQL의 MIN과 MAX를 보면 오라클이 리턴해주는게 다릅니다.
저는 두번째 처럼 min(rowidcol),max(rowidcol)이 리턴해주는 값은 이해가 가는데
첫번째 SQL의 min(rowid),max(rowid)의 리턴값은 이해가 안가네요.
이것을 어떻게 이해 해야 할까요?

 

그리고 데용량 데이타를 DIRECT LOAD PATH를 통해 HWM위로 로딩하고 나서
그 로딩된 데이타만을 삭제 하기를 원한다면
위의 경우 SQL을 어떻게 만들어 줘야 하는지요?

감사합니다.

A:                               엑셈 답변 보기

위 글은 (주) 엑셈 온라인 서비스 ASK EXEM 에서 발췌한 것임을 알려 드립니다.

저작자표시

• 카카오스토리
• 트위터
• 페이스북