/*
INDEX를 이용한 정렬과 ORDER BY에 의한 정렬 순서의 차이점을 테스트 한다.
테스트를 위해서 ORDER_TEST라는 테이블을 만들고 DUAL을 이용하여 데이터를 1000을 입력한다.
이때 C3컬럼을 DATE 타입으로 설정하고 SYSDATE를 입력한다.
그리고 INDEX를 이용한 정렬을 테스트하기위하여 C3컬럼에 인덱스를 생성한다.
*/
CREATE TABLE ORDER_TEST
(
C1 NUMBER(10) NOT NULL PRIMARY KEY,
C2 VARCHAR2(10) NOT NULL,
C3 DATE NOT NULL
);
INSERT INTO ORDER_TEST
SELECT ROWNUM, LPAD(ROWNUM, 10, '0'), SYSDATE
FROM DUAL
CONNECT BY LEVEL <= 1000;
COMMIT;
CREATE INDEX I_ORDER_TEST ON ORDER_TEST(C3);
/*
아래와 같이 두가지의 SQL을 준비한다.
첫번째 SQL을 테이블 전체를 읽고 SORT OPERATION을 수행하도록 하며,
두번째 SQL은 C3컬럼에 생성된 I_ORDER_TEST 인덱스를 이용하여 SORT OPERATION을 제거한 경우이다.
우선 SESSION에 TRACE를 설정하고 두가지의 SQL에 대한 출력결과를 비교해보자.
*/
ALTER SESSION SET SQL_TRACE = TRUE;
SELECT /*+ FULL(ORDER_TEST) */ *
FROM ORDER_TEST
ORDER BY C3 DESC;
/*
C1 C2 C3
523 0000000523 13/01/01
524 0000000524 13/01/01
525 0000000525 13/01/01
526 0000000526 13/01/01
527 0000000527 13/01/01
528 0000000528 13/01/01
529 0000000529 13/01/01
530 0000000530 13/01/01
531 0000000531 13/01/01
532 0000000532 13/01/01
*/
SELECT /*+ INDEX_DESC(ORDER_TEST I_ORDER_TEST) */ *
FROM ORDER_TEST
ORDER BY C3 DESC;
/*
C1 C2 C3
522 0000000522 13/01/01
521 0000000521 13/01/01
520 0000000520 13/01/01
519 0000000519 13/01/01
518 0000000518 13/01/01
517 0000000517 13/01/01
516 0000000516 13/01/01
515 0000000515 13/01/01
514 0000000514 13/01/01
513 0000000513 13/01/01
*/
/*
위의 결과를 보면 출력 결과가 상당히 다른것을 알 수 있다.
C3컬럼은 데이터를 INSERT 할때 SYSDATE를 입력하였으므로 대부분의 데이터가 동일한 값이 입력 되었을 것이다.
왜 이런 현상이 발생한 것일까? TRACE결과를 보면서 확인해 보자
*/
SELECT /*+ FULL(ORDER_TEST) */ *
FROM ORDER_TEST
ORDER BY C3 DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 9 0 50
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
50 SORT ORDER BY (cr=7 pr=0 pw=0 time=305 us)
1000 TABLE ACCESS FULL ORDER_TEST (cr=7 pr=0 pw=0 time=16 us)
/*
첫번째 SQL의 TRACE 결과를 보면 TABLE를 FULL SCAN하여서 SORT를 수행하였고
*/
SELECT /*+ INDEX_DESC(ORDER_TEST I_ORDER_TEST) */ *
FROM ORDER_TEST
ORDER BY C3 DESC
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 50
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
50 TABLE ACCESS BY INDEX ROWID ORDER_TEST (cr=3 pr=0 pw=0 time=80 us)
50 INDEX FULL SCAN DESCENDING I_ORDER_TEST (cr=2 pr=0 pw=0 time=20 us)(object id 59148)
/*
두번째 SQL의 TRACE 결과는 I_ORDER_TEST INDEX를 FULL SCAN DESCENDING하여
INDEX에의해 정렬된 순서대로 출력 하였다.
위의 2가지 CASE에서 알 수 있는 결과는 동일한 값을 가지는 데이터에 대하여
INDEX의 경우에는 인덱스컬럼이외에 ROWID로 정렬이 되어 있으나, SORT ORDER OPERATION에 의한 정렬은 ROWID로 정렬이 되지 않기 때문이다.
그러면 SORT ORDER OPERATION을 수행하는 첫번째 SQL의 ORDER BY절에 ROWID를 추가하여 보자.
*/
SELECT /*+ FULL(ORDER_TEST) */ *
FROM ORDER_TEST
ORDER BY C3 DESC, ROWID DESC;
/*
C1 C2 C3
522 0000000522 13/01/01
521 0000000521 13/01/01
520 0000000520 13/01/01
519 0000000519 13/01/01
518 0000000518 13/01/01
517 0000000517 13/01/01
516 0000000516 13/01/01
515 0000000515 13/01/01
514 0000000514 13/01/01
513 0000000513 13/01/01
*/
/*
이제 INDEX를 이용하여 SORT OPERATION을 제거한 경우와 동일하게 출력되었다.
대부분의 APPLICATION에서는 문제가 되지 않을 수 있으나, 특별한 경우에는 이것이 문제가 될 수 있다.
만일 INDEX를 이용하여 정렬을 수행하는 않는것이 성능상에 문제가 되어 INDEX를 생성하고 SORT OPERATION을 제거하였을 때
화면에 출력되는 결과가 달라 문제가 될 수도 있는 것이다.
또한 페이지 처리를 위하여 ROWNUM 을 활용할 때도 SORT OPERATION을 수행하던 SQL을 옵티마이져가 INDEX를 이용하여 SORT OPERATION을 제거함으로써
이러한 문제가 발생하는 경우도 있었다. 이러한 경우에는 정렬을 수행할 때 변별력 있는 또다른 정렬조건을 추가하여 주는것도 하나의 방법이 될것이다.
*/
댓글 없음:
댓글 쓰기