ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [오라클 질문] rowid 와 direct path load 연관 질문입니다.
    기술이야기/Ask 엑셈 2009. 12. 15. 13:29
    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 에서 발췌한 것임을 알려 드립니다.

    댓글 0

© Copyright 2001 ~ 2021 EXEM CO., LTD. All Rights Reserved