'오라클' 카테고리의 글 목록 (21 Page)

오라클

rownum 2014.03.28

rownum

2014. 3. 28. 15:36

728x90

ROWNUM

ROWNUM

오라클에서 제공해 주는 가상의 row number 컬럼임.

SQL에서 조건을 만족하는 데이터에 대하여 가상의 번호를 제공.

rownum은 1번 부터 사용되어야 한다.

FROM 절에서 조인되는 순서에 우선순위를 주거나, 특정 Row 개수만큼만 가져오기 위한 부분범위 처리를 위해 많이 사용.

-- r_c1 ASC 순으로 5개의 데이터 추출 목적

SELECT * FROM count_10
WHERE rownum <= 5
ORDER BY r_c1 asc ;

C1       R_C1
---------- ----------
         5          6
         4          7
         3          8
         2          9
         1         10

5 rows

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |       |     4 (100)|      5 |00:00:00.01 |       3 |       |       |          |
|   1 |  SORT ORDER BY      |          |      1 |      5 |   130 |     4  (25)|      5 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|*  2 |   COUNT STOPKEY     |          |      1 |        |       |            |      5 |00:00:00.01 |       3 |       |       |          |
|   3 |    TABLE ACCESS FULL| COUNT_10 |      1 |     10 |   260 |     3   (0)|      5 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

월래 의도는 r_c1의 값이 1,2,3,4,5 이렇게 5개의 Row가 출력 되어야 하는데 6,7,8,9,10이 출력 됨.

SQL의 처리 순서는 다음과 같다.
   1. 조건을 만족 시키는 데이터 처리 (FROM,WHERE)
   2. SELECT 절
   3. GROUP BY 절
   4. HAVING 절
   5. ORDER BY 절

위의 플랜을 보게 되면 먼저 5건을 추출한 후에, 그 추출된 데이터에 대하여 ORDER BY를 수행 하고 있다.
즉, count_10 테이블에 대하여 Full Scan을 하면서 첫 row 부터 5건을 추출 (10,9,8,7,6)한 후에 그 추출된 
데이터(10,9,8,7,6)를 r_c1 asc 순으로 정렬을 하고 있다. 
즉, rownum을 잘못 사용하여서 원하지 않는 데이터가 출력이 된 것이다.

월래 목적에 맞는 데이터가 출력이 되게 하려면 다음과 같이 쿼리를 바꿔야 한다.

SELECT   t.*
FROM ( SELECT * FROM count_10 ORDER BY r_c1 asc ) t
WHERE rownum <= 5  ;

C1       R_C1
---------- ----------
        10          1
         9          2
         8          3
         7          4
         6          5

5 rows

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |       |     4 (100)|      5 |00:00:00.01 |       3 |       |       |          |
|*  1 |  COUNT STOPKEY          |          |      1 |        |       |            |      5 |00:00:00.01 |       3 |       |       |          |
|   2 |   VIEW                  |          |      1 |     10 |   260 |     4  (25)|      5 |00:00:00.01 |       3 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|          |      1 |     10 |   260 |     4  (25)|      5 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS FULL   | COUNT_10 |      1 |     10 |   260 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------

-- r_c1 asc 순으로 정렬 한 후에 6-10개의 데이터를 보기를 원함.

SELECT   t.*
FROM ( SELECT * FROM count_10 ORDER BY r_c1 asc ) t
WHERE rownum >= 6  ;

C1       R_C1
---------- ----------

0 rows

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |       |     4 (100)|      0 |00:00:00.01 |       3 |       |       |          |
|   1 |  COUNT                |          |      1 |        |       |            |      0 |00:00:00.01 |       3 |       |       |          |
|*  2 |   FILTER              |          |      1 |        |       |            |      0 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW               |          |      1 |     10 |   260 |     4  (25)|     10 |00:00:00.01 |       3 |       |       |          |
|   4 |     SORT ORDER BY     |          |      1 |     10 |   260 |     4  (25)|     10 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |      TABLE ACCESS FULL| COUNT_10 |      1 |     10 |   260 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------

rownum은 1번 부터 사용이 되질 않으면 안된다. 그러므로 다음과 같이 SQL을 수정해 주어야 한다.

SELECT v.c1 , v.r_c1
FROM  ( SELECT rownum rn , t.*
          FROM   ( SELECT * FROM count_10 ORDER BY r_c1 asc ) t
       ) v
WHERE rn >= 6 ;

C1       R_C1
---------- ----------
         5          6
         4          7
         3          8
         2          9
         1         10

5 rows

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |       |     4 (100)|      5 |00:00:00.01 |       3 |       |       |          |
|*  1 |  VIEW                 |          |      1 |     10 |   390 |     4  (25)|      5 |00:00:00.01 |       3 |       |       |          |
|   2 |   COUNT               |          |      1 |        |       |            |     10 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW               |          |      1 |     10 |   260 |     4  (25)|     10 |00:00:00.01 |       3 |       |       |          |
|   4 |     SORT ORDER BY     |          |      1 |     10 |   260 |     4  (25)|     10 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |      TABLE ACCESS FULL| COUNT_10 |      1 |     10 |   260 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------

-- row_number analytic function 사용

SELECT c1 , r_c1
FROM (
SELECT c1 , r_c1 , row_number() over(order by r_c1 asc) rn
FROM   count_10   ) v
WHERE rn >= 6 ;

C1       R_C1
---------- ----------
         5          6
         4          7
         3          8
         2          9
         1         10

5 rows

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |       |     4 (100)|      5 |00:00:00.01 |       3 |       |       |          |
|*  1 |  VIEW               |          |      1 |     10 |   390 |     4  (25)|      5 |00:00:00.01 |       3 |       |       |          |
|   2 |   WINDOW SORT       |          |      1 |     10 |   260 |     4  (25)|     10 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL| COUNT_10 |      1 |     10 |   260 |     3   (0)|     10 |00:00:00.01 |       3 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------

-- 부분 범위 처리를 위한 테스트 테이블 생성

CREATE TABLE count_stop
AS
SELECT level c1 , chr(64+level ) c2 , lpad ( level , 5 , 0) c3
FROM dual
CONNECT BY level <= 99999 ;

CREATE INDEX count_stop_idx on count_stop(c1) ;

CREATE INDEX count_stop_idx02 on count_stop(c2 , c1) ;

EXEC DBMS_STATS.GATHER_TABLE_STATS( user , 'count_stop' , method_opt=>'for all columns size skewonly for all indexes')

-- 최근 데이터 순(c1)으로 20건의 데이터를 가져다가 출력하는 쿼리

SELECT v.c1 , v.c2 , v.c3
FROM  ( SELECT rownum rn , t.*
        FROM   ( SELECT * FROM count_stop ORDER BY c1 desc ) t
        WHERE  rownum <= 20
       ) v
WHERE  rn >= 1 ;

C1 C2 C3        
---------- -- ----------
     99999 ？ 99999     
     99998 ？ 99998     
     99997 ？ 99997     
     99996 ？ 99996     
     99995 ？ 99995     
     99994 ？ 99994     
     99993 ？ 99993     
     99992 ？ 99992     
     99991 ？ 99991     
     99990 ？ 99990     
     99989 ？ 99989     
     99988 ？ 99988     
     99987 ？ 99987     
     99986 ？ 99986     
     99985 ？ 99985     
     99984 ？ 99984     
     99983 ？ 99983     
     99982 ？ 99982     
     99981 ？ 99981     
     99980 ？ 99980

20 rows

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |      1 |        |       |       |   576 (100)|     20 |00:00:00.05 |     266 |       |       |          |
|*  1 |  VIEW                    |            |      1 |     20 |   720 |       |   576   (2)|     20 |00:00:00.05 |     266 |       |       |          |
|*  2 |   COUNT STOPKEY          |            |      1 |        |       |       |            |     20 |00:00:00.05 |     266 |       |       |          |
|   3 |    VIEW                  |            |      1 |  99999 |  2246K|       |   576   (2)|     20 |00:00:00.05 |     266 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY|            |      1 |  99999 |  1367K|  2368K|   576   (2)|     20 |00:00:00.05 |     266 | 38912 | 38912 |34816  (0)|
|   5 |      TABLE ACCESS FULL   | COUNT_STOP |      1 |  99999 |  1367K|       |    78   (2)|  99999 |00:00:00.01 |     266 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------

count_stop 테이블에 대하여 전체 데이터를 읽은 후에 c1컬럼에 대하여 desc 정렬을 수행 (SORT ORDER BY STOPKEY)

C1 컬럼으로 생성 되어 있는 인덱스를 이용하여 20건만 추출 한 후에, 데이터를 처리 하게 변경을 해 보자 .

SELECT v.c1 , v.c2 , v.c3
FROM  ( SELECT rownum rn , t.*
        FROM   ( SELECT /*+ index_rs_desc(count_stop count_stop_idx )*/* FROM count_stop ORDER BY c1 desc ) t
        WHERE  rownum <= 20
       ) v
WHERE  rn >= 1 ;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |      1 |        |       |       |   576 (100)|     20 |00:00:00.05 |     266 |       |       |          |
|*  1 |  VIEW                    |            |      1 |     20 |   720 |       |   576   (2)|     20 |00:00:00.05 |     266 |       |       |          |
|*  2 |   COUNT STOPKEY          |            |      1 |        |       |       |            |     20 |00:00:00.05 |     266 |       |       |          |
|   3 |    VIEW                  |            |      1 |  99999 |  2246K|       |   576   (2)|     20 |00:00:00.05 |     266 |       |       |          |
|*  4 |     SORT ORDER BY STOPKEY|            |      1 |  99999 |  1367K|  2368K|   576   (2)|     20 |00:00:00.05 |     266 | 38912 | 38912 |34816  (0)|
|   5 |      TABLE ACCESS FULL   | COUNT_STOP |      1 |  99999 |  1367K|       |    78   (2)|  99999 |00:00:00.01 |     266 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------

인덱스 힌트를 주었음에도 사용되지 않고 있다. 
count_stop 테이블의 데이터가 1부터 99999까지의 데이터를 가지고 있다는 것을 알고 있으므로 , c1 >=1 이라는 조건을 추가해 보자

SELECT v.c1 , v.c2 , v.c3
FROM  ( SELECT rownum rn , t.*
        FROM   ( SELECT * FROM count_stop WHERE c1 >= 1  ORDER BY c1 desc ) t
        WHERE  rownum <= 20
       ) v
WHERE  rn >= 1 ;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                |      1 |        |       |     3 (100)|     20 |00:00:00.01 |       3 |
|*  1 |  VIEW                           |                |      1 |     20 |   720 |     3   (0)|     20 |00:00:00.01 |       3 |
|*  2 |   COUNT STOPKEY                 |                |      1 |        |       |            |     20 |00:00:00.01 |       3 |
|   3 |    VIEW                         |                |      1 |     20 |   460 |     3   (0)|     20 |00:00:00.01 |       3 |
|   4 |     TABLE ACCESS BY INDEX ROWID | COUNT_STOP     |      1 |  99999 |  1367K|     3   (0)|     20 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN DESCENDING| COUNT_STOP_IDX |      1 |     20 |       |     2   (0)|     20 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------

원하는데로 인덱스만을 이용하여 20건의 데이터를 추출하였다.

WHERE 조건이 있고, ORDER BY가 있고, WHERE절에서 사용되는 컬럼과 ORDER BY절에 있는 컬럼 
조합의 인덱스가 있을 경우, ORDER BY절에 데이터의 정합성을 해치치 않는다는 가정하에 인덱스 컬럼 
순으로 ORDER BY절에 컬럼을 나열하여 주면 인덱스 만으로 부분 범위 처리가 가능하다.

PREV 1 ···18 19 20 21 22 23 24 ···43 NEXT

멋지게 놀아라

오라클

rownum

+ Recent posts

티스토리툴바