2.16 WCOTR* (Where Current Of To Rowid) : Where Current Of를 사용하여 Index Scan을 회피하라

• PRO* C나 PL/SQL에서 많은 양의 데이터를 Cursor For Loop로 선언한 후 건건이 Update/Delete등을 처리하는 경우가 많이 있다.
• 개발자들이 일반적으로 Cusor For Loop내에서 아직도 PK인덱스나 Unique 인덱스를 사용하여 DML문을 실행하고 있다.
• 이럴때 WOORT을 사용하면 인덱스 Access단계를 제거할 수 있다.

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY(ID); --> id 에 PK 인덱스를 생성함.

EXEC dbms_stats.gather_table_stats(user, 'T1', cascade => true);

|
|{code:SQL|title= 1. Cusor For Loop를 사용한 가장흔하게 볼수있는 PL/SQL 패턴 |borderStyle=solid}
DECLARE
   CURSOR c IS
SELECT *
        FROM t1
      FOR UPDATE;
BEGIN
   FOR rec IN c
   LOOP
      UPDATE /*+ pk_index_test */t1   --> 10만 번 PK 인덱스를 사용하여 UPDATE
         SET score = score + 0
       WHERE ID = rec.ID;
   END LOOP;

   COMMIT;
END; --55.24 sec

위 PL/SQL의 ELAPSED TIME은 11.80초이며 이 수치는 10번을 싱행하여 평균값을 취한것.|

COMMIT;
END;--45.27 sec

* 위 PL/SQL의 ELAPSED TIME은 10.80초이며 이 수치 또한 10번싱행한 평균값
* PK인덱스를 이용한 UPDATE보다 Current of를사용한 UPDATE가 10%정도 빠름|
|{code:SQL|title= 10%차이 확인 |borderStyle=solid}
SQL> SELECT sql_text
  FROM v$sqlarea
 WHERE sql_text LIKE '%current_of_test%';

SQL_TEXT
--------------------------------------------------------------------------
UPDATE /*+ current_of_test */ T1 SET SCORE = SCORE + 0 WHERE ROWID = :B1    -->ROWID를 이용하여 Access

• WHERE CURRENT OF를 WHERE ROWID=로 변환하는작업은 Oracle Transgormer가 변환한 것이아니다.
• Transformer가 변환한 SQL은 절대 v$sqlarea에 나타나지 않는다.
• Parsing 이전 단계에서 SQL을 변환한 것이다.
• 이것은 PL/SQL이나 PRO*C의 컴파일러가 SQL을 수정한 것이다.
• 10046이벤트를 이용해도 마찬가지로 미리 변환된 SQL을 볼 수 있다.|

COMMIT;
END;

|