오라클 페이징 쿼리.

 

 

-------------------------------------------------------------------------------------------------------------------------

 

▣ 문제 : 페이징 쿼리가 느리다.

▣ 데이터 건수 : 1억건

▣ 인덱스 :  PK_TB_TABLE ( COL_01, COL_02 )

 

-------------------------------------------------------------------------------------------------------------------------

 

그냥 페이징인데! 속도가 느리다.

페이징 방식은 많이 있다. 그중에 대용량 데이터에서 많이 사용하는 ROWNUM 방식으로 쿼리 했지만 결과가 느리다.

데이터는 1000만건~ 1억건 정도이다.

기본적인 ROWNUM을 이용한 방식을 사용하려 한다. ROWNUM 을 이용하기전에는 빠른데 ROWNUM을 쿼리에 넣는 순간 느려진다. 오우~ 생각보다 잘 안풀린다.

 

▼ 이건 빠름 : 달랑 인덱스만 읽으면 되기 때문에 빠름

SELECT  
        COL_01, COL_02 
FROM TB_TABLE T 
ORDER BY COL_01, COL_02 ;

 

-------------------------------------------------------------------------------------------------------------------------

 

▼ 이건 느림

SELECT  
        COL_01, COL_02, COL_03, COL_04
FROM TB_TABLE T 
ORDER BY COL_01, COL_02 ;

 

어우..... 인덱스에 없는 컬럼이 있어서 그런가 ROWNUM을 써보자

 

-------------------------------------------------------------------------------------------------------------------------

 

▼ 이건 더 느림 : ROWNUM 을 쓰면 빨라야 하는데 느리다.

SELECT

    *

FROM  (

    SELECT

        ROWNUM AS RNUM

        , TT.*

    FROM (

        SELECT  
                COL_01, COL_02, COL_03, COL_04
        FROM TB_TABLE T 
        ORDER BY COL_01, COL_02 ;

    ) TT

) WHERE RNUM BETWEEN 1 AND 100 ;

 

/************************************************************************

 

흠~ ROWNUM 쓰면 빨라야 되는데 빠르지 않다.

나의 사고방식으로는, 내가 옵티마이저라면 먼저 PK_TB_TABLE ( COL_01, COL_02 ) 인덱스로 소팅이후 100건만 랜덤 엑세스 해서 COL_03, COL_04를 가져 오려나? 했는데 안 가져 온다~ 그냥 느리다.

 

그래서 내 계획대로 되라고 인덱스만 존재하게 하여 인라인 뷰에서 페이징을 진행하고 그 결과를 다시 내 테이블과 조인하여 페이징된 100건을 가져와라~ 라고 쿼리 했지만 이놈 오라클이 Nested Loop Join로 조인 하지 않고 Hash Join 으로 조인을 시도하여 더럽게 느린 속도로 쿼리가 돌아 간다.

 

************************************************************************/

 

▼ 그래도 느림! Nested Loop Join 하지 않고 Hash Join 되면서 느림

SELECT

    A.*

FROM  (

    SELECT

        ROWNUM AS RNUM

        , TT.*

    FROM (

        SELECT  
                COL_01, COL_02, COL_03, COL_04
        FROM TB_TABLE T 
        ORDER BY COL_01, COL_02 ;

    ) TT

) TTT

INNER JOIN TB_TABLE A ON TTT.COL_01 = A.COL_01 AND TTT.COL_02 = A.COL_02 

WHERE RNUM BETWEEN 1 AND 100 ;

 

/************************************************************************

 

왜 느리지? 아~ 힌트~

내 계획대로 Nested Loop Join 으로 풀리라고 힌트를 줬다.

 

************************************************************************/

 

▼ 빠름 위에꺼보다 10000000000000000000000000배 빠름

SELECT /*+ USE_NL(TTT A) */ 
    A.* 
FROM (     
    SELECT  
        ROWNUM AS RNUM 
        , TT.* 
    FROM (     
        SELECT  /* INDEX(T PK_TB_TABLE) */ 
            COL_01, COL_02 
        FROM TB_TABLE T 
        ORDER BY COL_01, COL_02 
    )TT 
)TTT 
INNER JOIN TB_TABLE A ON TTT.COL_01 = A.COL_01 AND TTT.COL_02 = A.COL_02 
WHERE RNUM BETWEEN 1000 AND 1100 ;

 

-------------------------------------------------------------------------------------------------------------------------

 

▼ 빠름 위에꺼보다 10000000000000000000000001배 빠름  
SELECT /*+ USE_NL(TTT A) */
    A.*
FROM (       
        SELECT  /*+ INDEX_ASC(T PK_TB_TABLE) */
            COL_01, COL_02
            , ROWNUM AS RNUM
        FROM TB_TABLE T
        ORDER BY COL_01, COL_02
)TTT
INNER JOIN TB_TABLE A ON TTT.COL_01 = A.COL_01 AND TTT.COL_02 = A.COL_02
WHERE RNUM BETWEEN 1000 AND 1100 ; 

 

INDEX_ASC 를 이용해서 인라인 뷰 하나를 줄였다. 솔직히 좀더 빠를꺼라고 생각은 하지만 확인은 못했다^^;

 

-------------------------------------------------------------------------------------------------------------------------

 

USE_NL을 이용하여 Hash Join 말고 Nested Loop Join 으로 유도했다.

/*+ USE_NL(TTT A) */ 의 뜻은 TTT 데이터의 ROWID 로 A 테이블을 랜덤엑세스 하여 딱~ 페이징 수 만큼만 읽고 끝내시오 이올씨다. 하여 빠른 속도를 보장 받을 수 있게 되었습니다.

 

흠! 이제와서 생각나는 건데~ JOIN 하기전에 WHERE RNUM BETWEEN 1000 AND 1100 하고 한번더 감싸서 조인했으면 빨랐을라나~~ 그러게! 그랬을수도 있겠네요!@

 

암틈~ 1억건 데이터 페이징수 저는 위와 같은 방식으로 빠른 페이징을 수행할수 있었습니다.

COL_01, COL_02 가 키이기 때문에 저는 위와 같이 사용했지만 키가 아닌경우 ROWID 를 사용할 수 있을것 같습니다.

 

 

ㅡㅡ; 수십만, 수백만일때는 그냥 어떻게 쿼리 하던 빨랐는데~ 이젠 어우~ 힘드네요~^^;

 

그리고 데용량 데이터 쿼리에서 Hash Join은 많이 이용되지만 본 페이징 쿼리에서는 제 계획되로 Nested Loop Join 으로풀리지않아 변경한 것이니 Hash Join을 절대 미워하지 마시길~

 

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------

 

운영에 배포를 했다~

헐~ 느리다! 분명히 빨랐었는데 다시 느리다. 원인을 찾아봤다.

 

SELECT /*+ USE_NL(TTT A) */ 
    A.* 
FROM (     
    SELECT  
        ROWNUM AS RNUM 
        , TT.* 
    FROM (     
        SELECT  /* INDEX(T PK_TB_TABLE) */ 
            COL_01, COL_02 
        FROM TB_TABLE T 
        ORDER BY COL_01, COL_02 
    )TT 
WHERE ROWNUM <= 1100
)TTT 
INNER JOIN TB_TABLE A ON TTT.COL_01 = A.COL_01 AND TTT.COL_02 = A.COL_02 
WHERE RNUM > 1000

 

-------------------------------------------------------------------------------------------------------------------------

 

헐... WHERE RNUM BETWEEN 1000 AND 1100 ;  때문에 속도저하가 발생했다.

물론 대용량 페이징에 대표적인 페이징 방식이 ROWNUM < 10, RNUM > 0 방식일 것인데 BETWEEN 도 비슷하겠지하고 사용했건만 결과는 너무 다르다. 어떤 쿼리는 BETWEEN 을 사용해도 잘되고 어떤 쿼리는 답이 없었다. 흠~ 인덱스 및 통계정보에 차이로 실행계획이 다르게 풀려서 그런것 같다.

이걸 방지하게 위해서 대용량 데이터는 모든 책에서 말하는 ROWNUM < 10, RNUM > 0 을 쓰도록 하자.

 

아~! 위 쿼리에서 테이블 조인시 pk 말고 ROWID 를 이용하여 조인하는 방식도 있다. (성능고도화 원칙)

인덱스에는 테이블의 ROWID 를 가지고 있는데 이 ROWID로 테이블을 바로 찾아가 데이터를 조회할 수 있다.

 

SELECT /*+ USE_NL(TTT A) */      

          A.*  

 FROM (          

                 SELECT ROWNUM AS RNUM          

                         , TT.*      

                 FROM (              

                                    SELECT  /* INDEX(T PK_TB_TABLE) */              

                                               COL_01, COL_02, ROWID        

                                    FROM TB_TABLE T          

                                    ORDER BY COL_01, COL_02      

                           )TT  WHERE ROWNUM <= 1100

                 )TTT  INNER JOIN TB_TABLE A ON TTT.ROWID = A.ROWID

       WHERE RNUM > 1000