SSISO Community

시소당

row id

BULLETIN CATEGORY 
BULLETIN TOPIC
: SQL*Plus  
: ROWID에 대한 이해

  올바른 환경에서의 ROWID의 사용은 SQL이나 PL/SQL Code의 실행을 보다 빠르게 하여 준다. 그러나 ROWID는 경우에 따라 Program을 전혀 엉뚱한 동작을 하게 만들 수 있으므로 ROWID의 사용은 특별한 주의를 기울여야 한다.  

Oracle ROWID란 무엇인가 ?    

  Oracle Table의 모든 Row는 ROWID라고 하는 물리적인 Address로 Assign 된다.   
 Non-Clustered Table에 대해서는 이러한 Address는 Unique하다.  Clustered Table에 대해서는 서로 다른 Table에서 추출된 Row들은 동일한 Data Block내에 존재하며, 똑같은 ROWID을 가지게 될 것이다.  

  누군가가 ROWID을 사용하여 한 Table안의 서로 다른 Column에 대하여 설계하고, 제한하는 기능들을 사용한다 하더라도 이것은 Pseudo-Column이다. Oracle의 Pseudo Column은 Table의 실제적인 Column은 아니므로 Table의describe를 통해서는 보이지 않지만 매우 유용하게 사용된다.  

  Pseudo Column의 다른 예는 Currval, Nextval ( Sequence Number Generator )와 Level ( For Hierarchical Queries ), Rownum ( A Row's Relative Number ) 같은 것들이 있다.  

  내부적으로 ROWID는 Binary 값을 가진다. 그러나 외부적으로 Display 될 때에는 Varchar2 Type의 Column에 저장된 3 Part의 Hexadecimal String으로 표현된다.  

첫번째 부분( 1 - 8 ) 은 Block Id를 나타낸다.  
두번째 부분( 10 - 13 ) 은 Block 내부에서 Row의 Sequence Number를 나타낸다.  
세번째 부분( 15 - 18 ) 은 Data File Id를 나타낸다.  

  Substr Function을 사용하여 ROWID를 구분하여 Select해 보면,  
      Select ROWID,  
             Substr(ROWID,  15,  4)  As  "File",  
             Substr(ROWID,   1,  8)  As  "Block",  
             Substr(ROWID,  10,  4)  As  "Row"  
      From    Dual;  
  

 위의 Select 구문은 다음과 같은  ROWID에 대하여 아래의 결과를 출력할 것이다.  
  
      ROWID    file blockrow  
      0000033d.0000.000100010000033d0000  

      ROWID  0000033d.0000.0001  
      File   0001  
      Block  0000033d  
      Row    0000  

위의 예와 같이 Row의 값은 Block 내에서의  첫번째 Row이다.   
( Block 내에서의 첫번째 Row의 Offset은 Zero이기 때문 ) 또한, 이 Row는 한 Data File내의  829 Block 내에 위치하고 있다. 이것이 이 Row의 Physical Address 이다.  

  n  It's Fast!  

  ROWID를 사용하여 Table의 Row를 Access하는 것은 빠르다. 사실상 Rule-Based Optimizer에서는 ROWID를 이용하여 Access하는 것이 가장 상위로 Rank 된 Access 방법이다.  

다음은 Access Path 선택의 Rule을 나타낸다.  

       Rank Access Path  
        1     Single Row By ROWID  
        2     Single Row By Cluster Join  
        3     Single Row By Hash Cluster Key With Unique Or Primary Key  
        4     Single Row By Unique Or Primary Key  
        5     Cluster Join  
        6     Hash Cluster Key  
        7     Indexed Cluster Key  
        8     Composite Key  
        9     Single-Column Indexes  
        10    Bounded Range Search On Indexed Columns  
        11    Unbounded Range Search On Indexed Columns  
        12    Sort-Merge Join  
        13    Max Or Min Of Indexed Column  
        14    Order By On Indexed Columns  
        15    Full Table Scan  

  위에서처럼 가장 빠른 Access 방법은 ROWID를 사용하는 것이다. 이러한 Access 방법은 아래의 사항이 만족되어 질 때에만 사용 가능 하다.  

 1. Where 절에서 ROWID로 Row를 선택하거나  
 2. Current Of 를 포함하는 Cursor 절로 기술되어야 한다.  

  위의 사항을 만족한다면 Oracle Server는 해당하는 Physical Address를 가지고 Data Row를 Access 할 것이며 결과 또한 가장 빠를 것이다. 그러나 빠른 Access가 가능한 만큼 허점도 가지고 있는데, ROWID가 변할 수 있다는 점이 그것이다.  
  

ROWID 변경  

  Row의 Address는 서로 다른 환경하에서는 변할 수 있다.  예를 들면, Import 직후의 Export는 일반적으로 서로 다른 ROWID를 생성한다. 그러나 이 경우는 문제가 되지 않는다.  왜냐하면 ROWID가 User의 Transaction의 영역 밖에서 일어났기 때문이며, 이러한 변화는 전혀 고려할 만한 사항이 아니다.  

  반면에 어떤 User가 DML 작업을 하는 도중에 ROWID가 변하였다고 가정해보자.  
만약 User의 Program이 전체에 걸쳐 ROWID의 일관성이 유지되어야 하는 경우라면, 이것은 정말 큰 문제가 될 것이다.  
  한 Transaction은 Connect 되는 순간 시작되어 Commit이나 Rollback을 만나면 종료되며 이 시점부터 다음Transaction 이 시작된다. 그러므로 Program 내부에 하나 이상의 Transaction이 존재한다면, ROWID 가 변할 수도 있다.  

아래는 이러한 문제점을 잘 보여주는 예제이다.  
  

    Procedure Transfer_All  
     (Commit_After_In In Integer := 100,  
      Requery_After_In In Integer := 1000)  
    Is  
     More_Data Boolean := True;  
     Commit_Count Integer := 0;  

     Cursor Trans_Cur  
     Is  
      Select ROWID From Transfer_Table  
      Where Transfer_Indictor = 'U'  
      And Rownum <= Requery_After_In;  

    Begin  
     While More_Data  
     Loop  
      More_Data := False;  
      For Trans_Rec In Trans_Cur  
      Loop  
       More_Data := True;  

       Insert Into Master_Table  
       Select  *  
       From     Transfer_Table  
       Where ROWID = Trans_Rec.ROWID;  

       Update Transfer_Table  
       Set        Transfer_Indicator = 'T'  
       Where   ROWID= Trans_Rec.ROWID;  

       Commit_Count := Commit_Count + 1;  
       If Commit_Count = Commit_After_In  
       Then  
        Commit;  
        Commit_Count := 0;  
       End If;  
      End Loop;  
     End Loop;  
    End; 

  위의 Procedure는 몇 가지 제한 조건을 가지고 ROWID를 Select 하여 Trans_Cur 라는 Cursor를  생성한다. 이 Cursor로 Return 되는 Row는 For Loop 안에서 사용된다.  
  Loop 내에서는 Current Cursor Record의  ROWID을 Reference 하는 Insert 문장과 Update 문장이 함께 사용되고 있다. 또한 이 Cursor For Loop 안에서는 Argument로 넘겨받은 Interval 변수에 따라 Commit 문이 실행된다.  Commit 문이 실행되면, Cursor에 의해 사용되던 ROWID는 변할 수 있다.  

 이러한 상황이  Single-User의 환경에서 발생하는 것이라면 아무런 문제가 없으나, Multi-User의 Concurrent Transaction 환경이라면 많은 문제가 발생할 것이다.  

* 다른 User 가 현재 사용하고 있는 ROWID를  Invalid 하게 하는Update나 Insert,   
  Delete 작업을 수행할 수도 있다.  
* Insert나 Length를 줄이는 Update 작업 등에서 자동적으로 Compression이 일어나  
  는 경우 ROWID가 변할 수도 있다.  

  이러한 문제는 잘 드러나지 않을 뿐만 아니라 Error를 Detect 하기도 힘들고, 거의 Debug 가 불가능하다.  
  

결 론   

  ROWID는 엄격한 제한 조건을 가지고 주의 깊게 사용되어야 한다. 대상 Row에 Lock을 걸지 않는 SQL문장내에는 ROWID를 사용해서는 안된다.  

Transfer_All Procedure의 경우 Error를 Fix 하기 위하여 2가지를 수정해야 한다.  

첫째, Cursor의 선언 부분에 " For Update Of ROWID" 가 추가 되어야 하며,  
둘째, For Loop의 밖으로 Commit 문을 옮겨주어야 한다.  

 이러한 수정이 Program을 다소 유연하지 못하게 만드는 것처럼 보일지도 모르지만, 확실히 Program을 신뢰성있게 만들어 준다.  
 


Oracle Korea Customer Support Technical Bulletins

2100 view

4.0 stars