-
[오라클 질문] rowid 와 direct path load 연관 질문입니다.기술이야기/Ask 엑셈 2009. 12. 15. 13:292009-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을 어떻게 만들어 줘야 하는지요?
위 글은 (주) 엑셈 온라인 서비스 ASK EXEM 에서 발췌한 것임을 알려 드립니다.
감사합니다.