Limiting SQL Rows ( SQL 행제한)
오라클 문서에서 확인 방법은 아래와 같습니다.
https://docs.oracle.com/database/121/DWHSG/analysis.htm#DWHSG9188
12cR1 부터 사용 가능 하고 잘 황용을 하시면 페이징 처리시 사용 하시면 좋을 듯 합니다.
Oracle 뿐만 아니라 MS SQL에서도 동일한 문법이 있다는것도 찾았습니다. (전 아직 MSSQL에서는 테스트를 안 해 보았습니다.)
위의 메뉴얼 보시면 예제도 있습니다.
문법 :
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
OFFSET :
행 제한이 시작되기 전에 건너 뛸 행 수를 지정.
오프셋은 숫자 여야합니다. 음수를 지정하면 오프셋이 0으로 처리.
NULL을 지정하거나 조회가 리턴 한 행 수보다 크거나 같은 수를 지정하면 0 개의 행 린턴.
오프셋에 분수가 포함 된 경우 분수 부분이 잘림. OFFSET 지정하지 않으면 오프셋이 0이고 행 제한이 첫 번째 행으로 시작.
가독성을 높이기 위해 Oracle은 ROW 또는 ROWS 둘 중 어느것을 사용 해도 동일
FIRST 와 NEXT 둘 중 어느것을 사용 해도 동일
FETCH :
이것은 리턴 할 행 수 또는 백분율을 지정.
이 절을 지정하지 않으면 오프셋 + 1 행에서 시작하여 모든 행이 반환.
WITH TIES 키워드를 사용하면 쿼리에는 마지막으로 한정된 행의 정렬 키와 일치하는 모든 행도 포함.
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
OFFSET :
행 제한이 시작되기 전에 건너 뛸 행 수를 지정.
오프셋은 숫자 여야합니다. 음수를 지정하면 오프셋이 0으로 처리.
NULL을 지정하거나 조회가 리턴 한 행 수보다 크거나 같은 수를 지정하면 0 개의 행 린턴.
오프셋에 분수가 포함 된 경우 분수 부분이 잘림. OFFSET 지정하지 않으면 오프셋이 0이고 행 제한이 첫 번째 행으로 시작.
가독성을 높이기 위해 Oracle은 ROW 또는 ROWS 둘 중 어느것을 사용 해도 동일
FIRST 와 NEXT 둘 중 어느것을 사용 해도 동일
FETCH :
이것은 리턴 할 행 수 또는 백분율을 지정.
이 절을 지정하지 않으면 오프셋 + 1 행에서 시작하여 모든 행이 반환.
WITH TIES 키워드를 사용하면 쿼리에는 마지막으로 한정된 행의 정렬 키와 일치하는 모든 행도 포함.
에제1) : 결과만 놓고 이야기 하면 느립니다.
SELECT count(*) FROM T_OFFSET; -- 73039000
예제1 >>
SELECT *
FROM (SELECT ROWNUM RN,
T.*
FROM (SELECT *
FROM T_OFFSET
) T
)
WHERE RN BETWEEN 11 AND 20 -- rn
;
예제1 >>
SELECT *
FROM (SELECT ROWNUM RN,
T.*
FROM (SELECT *
FROM T_OFFSET
) T
)
WHERE RN BETWEEN 11 AND 20 -- rn
;
PLAN 확인
SQL_ID 8rw0889v22xg4, child number 1
-------------------------------------
SELECT * FROM (SELECT ROWNUM RN, T.* FROM
(SELECT * FROM T_OFFSET ) T
) WHERE RN BETWEEN 11 AND 20 -- rn
Plan hash value: 124904871
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 160K(100)| | 10 |00:00:15.43 | 592K| 393K|
|* 1 | VIEW | | 1 | 48M| 5441M| 160K (1)| 00:00:07 | 10 |00:00:15.43 | 592K| 393K|
| 2 | COUNT | | 1 | | | | | 73M|00:00:09.83 | 592K| 393K|
| 3 | TABLE ACCESS FULL| T_OFFSET | 1 | 48M| 4841M| 160K (1)| 00:00:07 | 73M|00:00:09.59 | 592K| 393K|
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$335DD26A / from$_subquery$_001@SEL$1
2 - SEL$335DD26A
3 - SEL$335DD26A / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN"<=20 AND "RN">=11))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22], "from$_subquery$_001"."LV"[NUMBER,22],
"from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=256) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROWNUM[8]
3 - (rowset=256) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
-------------------------------------
SELECT * FROM (SELECT ROWNUM RN, T.* FROM
(SELECT * FROM T_OFFSET ) T
) WHERE RN BETWEEN 11 AND 20 -- rn
Plan hash value: 124904871
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 160K(100)| | 10 |00:00:15.43 | 592K| 393K|
|* 1 | VIEW | | 1 | 48M| 5441M| 160K (1)| 00:00:07 | 10 |00:00:15.43 | 592K| 393K|
| 2 | COUNT | | 1 | | | | | 73M|00:00:09.83 | 592K| 393K|
| 3 | TABLE ACCESS FULL| T_OFFSET | 1 | 48M| 4841M| 160K (1)| 00:00:07 | 73M|00:00:09.59 | 592K| 393K|
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$335DD26A / from$_subquery$_001@SEL$1
2 - SEL$335DD26A
3 - SEL$335DD26A / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN"<=20 AND "RN">=11))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22], "from$_subquery$_001"."LV"[NUMBER,22],
"from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=256) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROWNUM[8]
3 - (rowset=256) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
데이타 결과는 맞지만 느립니다. 전체를 다 읽어서 처리 하여 늦습니다.
에제2) : 느립니다.
예제2 >>
SELECT *
FROM (SELECT ROWNUM RN, T.*
FROM (
SELECT *
FROM T_OFFSET
) T
)WHERE RN BETWEEN 11 AND 21 -- rn
AND RN < 21;
SELECT *
FROM (SELECT ROWNUM RN, T.*
FROM (
SELECT *
FROM T_OFFSET
) T
)WHERE RN BETWEEN 11 AND 21 -- rn
AND RN < 21;
PLAN
SQL_ID 7qwryrd7jvrkz, child number 1
-------------------------------------
SELECT * FROM (SELECT ROWNUM RN, T.* FROM (
SELECT * FROM T_OFFSET ) T
)WHERE RN BETWEEN 11 AND 21 -- rn AND RN < 21
Plan hash value: 124904871
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 160K(100)| | 10 |00:00:15.49 | 592K| 393K|
|* 1 | VIEW | | 1 | 48M| 5441M| 160K (1)| 00:00:07 | 10 |00:00:15.49 | 592K| 393K|
| 2 | COUNT | | 1 | | | | | 73M|00:00:09.87 | 592K| 393K|
| 3 | TABLE ACCESS FULL| T_OFFSET | 1 | 48M| 4841M| 160K (1)| 00:00:07 | 73M|00:00:09.62 | 592K| 393K|
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$335DD26A / from$_subquery$_001@SEL$1
2 - SEL$335DD26A
3 - SEL$335DD26A / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN"<21 AND "RN">=11))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22], "from$_subquery$_001"."LV"[NUMBER,22],
"from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=256) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROWNUM[8]
3 - (rowset=256) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
-------------------------------------
SELECT * FROM (SELECT ROWNUM RN, T.* FROM (
SELECT * FROM T_OFFSET ) T
)WHERE RN BETWEEN 11 AND 21 -- rn AND RN < 21
Plan hash value: 124904871
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 160K(100)| | 10 |00:00:15.49 | 592K| 393K|
|* 1 | VIEW | | 1 | 48M| 5441M| 160K (1)| 00:00:07 | 10 |00:00:15.49 | 592K| 393K|
| 2 | COUNT | | 1 | | | | | 73M|00:00:09.87 | 592K| 393K|
| 3 | TABLE ACCESS FULL| T_OFFSET | 1 | 48M| 4841M| 160K (1)| 00:00:07 | 73M|00:00:09.62 | 592K| 393K|
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$335DD26A / from$_subquery$_001@SEL$1
2 - SEL$335DD26A
3 - SEL$335DD26A / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN"<21 AND "RN">=11))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=256) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22], "from$_subquery$_001"."LV"[NUMBER,22],
"from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=256) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROWNUM[8]
3 - (rowset=256) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
에제3) : 느립니다.
SELECT *
FROM (SELECT ROWNUM RN, T.*
FROM (
SELECT *
FROM T_OFFSET
) T
)
WHERE RN BETWEEN 11 AND 20 -- rn
AND ROWNUM < 20; -- rownum
FROM (SELECT ROWNUM RN, T.*
FROM (
SELECT *
FROM T_OFFSET
) T
)
WHERE RN BETWEEN 11 AND 20 -- rn
AND ROWNUM < 20; -- rownum
PLAN 확인
SQL_ID fhj0gsa24b77m, child number 1
-------------------------------------
SELECT * FROM (SELECT ROWNUM RN, T.* FROM (
SELECT * FROM T_OFFSET ) T
) WHERE RN BETWEEN 11 AND 20 -- rn AND ROWNUM < 20
Plan hash value: 2679304203
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 10 |00:00:19.89 | 592K| 393K|
|* 1 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:19.89 | 592K| 393K|
|* 2 | VIEW | | 1 | 19 | 2242 | 2 (0)| 00:00:01 | 10 |00:00:19.89 | 592K| 393K|
| 3 | COUNT | | 1 | | | | | 73M|00:00:11.73 | 592K| 393K|
| 4 | TABLE ACCESS FULL| T_OFFSET | 1 | 19 | 1995 | 2 (0)| 00:00:01 | 73M|00:00:10.30 | 592K| 393K|
------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$335DD26A / from$_subquery$_001@SEL$1
3 - SEL$335DD26A
4 - SEL$335DD26A / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<20)
2 - filter(("RN"<=20 AND "RN">=11))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=19) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22], "from$_subquery$_001"."LV"[NUMBER,22],
"from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=19) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22], "from$_subquery$_001"."LV"[NUMBER,22],
"from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
3 - (rowset=19) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROWNUM[8]
4 - (rowset=19) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
-------------------------------------
SELECT * FROM (SELECT ROWNUM RN, T.* FROM (
SELECT * FROM T_OFFSET ) T
) WHERE RN BETWEEN 11 AND 20 -- rn AND ROWNUM < 20
Plan hash value: 2679304203
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 10 |00:00:19.89 | 592K| 393K|
|* 1 | COUNT STOPKEY | | 1 | | | | | 10 |00:00:19.89 | 592K| 393K|
|* 2 | VIEW | | 1 | 19 | 2242 | 2 (0)| 00:00:01 | 10 |00:00:19.89 | 592K| 393K|
| 3 | COUNT | | 1 | | | | | 73M|00:00:11.73 | 592K| 393K|
| 4 | TABLE ACCESS FULL| T_OFFSET | 1 | 19 | 1995 | 2 (0)| 00:00:01 | 73M|00:00:10.30 | 592K| 393K|
------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$335DD26A / from$_subquery$_001@SEL$1
3 - SEL$335DD26A
4 - SEL$335DD26A / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<20)
2 - filter(("RN"<=20 AND "RN">=11))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=19) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22], "from$_subquery$_001"."LV"[NUMBER,22],
"from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=19) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22], "from$_subquery$_001"."LV"[NUMBER,22],
"from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
3 - (rowset=19) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROWNUM[8]
4 - (rowset=19) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
예제 1 ~ 3 까지 결과는 동일 하지만 느립니다. 아 저건 아니지 하시는 분도 있겠지만, 모든 사람이 다 SQL문을 잘 구사 하는것은 아니므로 저렇게 구사도 가능 할 것이라 판단 하여 예제를 올려 봅니다. 결과적으로 전체 테이블 읽어서 ROWNUM 을 이용하여 짤랐지만 뭔가 부족하기는 합니다.
예제4) : 빠릅니다. 메인 SQL문을 읽는것은 동일 하면서 ROWNUM을 이용방법의 차이에 따른 속도 차이를 보게 됩니다.
읽는 버퍼 와 읽는 ROWS도 다릅니다.
SELECT *
FROM (SELECT ROWNUM RN,
T.*
FROM (SELECT *
FROM T_OFFSET
) T
WHERE ROWNUM < 21 -- rownum
)
WHERE RN > 10; -- rn
FROM (SELECT ROWNUM RN,
T.*
FROM (SELECT *
FROM T_OFFSET
) T
WHERE ROWNUM < 21 -- rownum
)
WHERE RN > 10; -- rn
PLAN 확인
SQL_ID 2vz4rcduz0tfn, child number 1
-------------------------------------
SELECT * FROM (SELECT ROWNUM RN, T.* FROM
(SELECT * FROM T_OFFSET ) T
WHERE ROWNUM < 21 -- rownum ) WHERE RN > 10
Plan hash value: 3997450112
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 20 | 2360 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 5 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 20 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS FULL| T_OFFSET | 1 | 20 | 2100 | 2 (0)| 00:00:01 | 20 |00:00:00.01 | 5 |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$335DD26A / from$_subquery$_001@SEL$1
2 - SEL$335DD26A
3 - SEL$335DD26A / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<21)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=20) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22],
"from$_subquery$_001"."LV"[NUMBER,22], "from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23],
"from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=20) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22],
"T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23], "T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROWNUM[8]
3 - (rowset=20) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22],
"T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23], "T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
-------------------------------------
SELECT * FROM (SELECT ROWNUM RN, T.* FROM
(SELECT * FROM T_OFFSET ) T
WHERE ROWNUM < 21 -- rownum ) WHERE RN > 10
Plan hash value: 3997450112
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 20 | 2360 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 5 |
|* 2 | COUNT STOPKEY | | 1 | | | | | 20 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS FULL| T_OFFSET | 1 | 20 | 2100 | 2 (0)| 00:00:01 | 20 |00:00:00.01 | 5 |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$335DD26A / from$_subquery$_001@SEL$1
2 - SEL$335DD26A
3 - SEL$335DD26A / T_OFFSET@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<21)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (rowset=20) "RN"[NUMBER,22], "from$_subquery$_001"."RID"[NUMBER,22],
"from$_subquery$_001"."LV"[NUMBER,22], "from$_subquery$_001"."OBJECT_TYPE"[VARCHAR2,23],
"from$_subquery$_001"."OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=20) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22],
"T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23], "T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROWNUM[8]
3 - (rowset=20) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22],
"T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23], "T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
또는 아래 처럼도 사용 가능 하겠습니다.
SELECT *
FROM (SELECT ROWNUM RN, T.*
FROM (
SELECT *
FROM T_OFFSET
) T
WHERE ROWNUM <=20
)
WHERE RN BETWEEN 11 AND 20
;
FROM (SELECT ROWNUM RN, T.*
FROM (
SELECT *
FROM T_OFFSET
) T
WHERE ROWNUM <=20
)
WHERE RN BETWEEN 11 AND 20
;
버전에 맞게 우리는 sql문을 수정이 가능 한 방법을 이용하여 아래 처럼 수정 해 봅니다.
SELECT *
FROM T_OFFSET
OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY;
RID LV OBJECT_TYPE OBJECT_NAME
--------- --------- ----------------------- --------------------------
11 1 CLUSTER C_TS#
12 1 INDEX I_CON1
13 1 INDEX I_UNDO1
14 1 INDEX I_USER#
15 1 CLUSTER C_COBJ#
16 1 INDEX I_COL2
17 1 TABLE CCOL$
18 1 TABLE SEG$
19 1 TABLE PROXY_DATA$
20 1 INDEX I_FILE2
FROM T_OFFSET
OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY;
RID LV OBJECT_TYPE OBJECT_NAME
--------- --------- ----------------------- --------------------------
11 1 CLUSTER C_TS#
12 1 INDEX I_CON1
13 1 INDEX I_UNDO1
14 1 INDEX I_USER#
15 1 CLUSTER C_COBJ#
16 1 INDEX I_COL2
17 1 TABLE CCOL$
18 1 TABLE SEG$
19 1 TABLE PROXY_DATA$
20 1 INDEX I_FILE2
sql문이 갑자기 단순해 지는 느낌이 듭니다.
SQL_ID 1p3x68p8bspyu, child number 1
-------------------------------------
SELECT * FROM T_OFFSET OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY
Plan hash value: 439429039
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 20 | 2360 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 5 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 20 | 2100 | 2 (0)| 00:00:01 | 20 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS FULL | T_OFFSET | 1 | 20 | 2100 | 2 (0)| 00:00:01 | 20 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / from$_subquery$_002@SEL$2
2 - SEL$1
3 - SEL$1 / T_OFFSET@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=20 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">10))
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=20)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_002"."RID"[NUMBER,22], "from$_subquery$_002"."LV"[NUMBER,22],
"from$_subquery$_002"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_002"."OBJECT_NAME"[VARCHAR2,128],
"from$_subquery$_002"."rowlimit_$$_rownumber"[NUMBER,22]
2 - (#keys=0) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROW_NUMBER() OVER ( ORDER BY NULL )[22]
3 - "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
-------------------------------------
SELECT * FROM T_OFFSET OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY
Plan hash value: 439429039
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 10 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 20 | 2360 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 5 |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 20 | 2100 | 2 (0)| 00:00:01 | 20 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS FULL | T_OFFSET | 1 | 20 | 2100 | 2 (0)| 00:00:01 | 20 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / from$_subquery$_002@SEL$2
2 - SEL$1
3 - SEL$1 / T_OFFSET@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=20 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">10))
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=20)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "from$_subquery$_002"."RID"[NUMBER,22], "from$_subquery$_002"."LV"[NUMBER,22],
"from$_subquery$_002"."OBJECT_TYPE"[VARCHAR2,23], "from$_subquery$_002"."OBJECT_NAME"[VARCHAR2,128],
"from$_subquery$_002"."rowlimit_$$_rownumber"[NUMBER,22]
2 - (#keys=0) "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128], ROW_NUMBER() OVER ( ORDER BY NULL )[22]
3 - "T_OFFSET"."RID"[NUMBER,22], "T_OFFSET"."LV"[NUMBER,22], "T_OFFSET"."OBJECT_TYPE"[VARCHAR2,23],
"T_OFFSET"."OBJECT_NAME"[VARCHAR2,128]
Predicate Information 내용을 보시면 예전 방식과 다른 동작으로 수행 되고 있습니다.
바인드 처리를 하면 아래와 같습니다.
전통적인 방식으로 표현을 하면
SELECT *
FROM (SELECT ROWNUM RN, T.*
FROM (
SELECT *
FROM T_OFFSET
) T
WHERE ROWNUM <=:1
)
WHERE RN BETWEEN :2 AND :1
;
-- 바인드 값 정리
:1 ==> 화면에 보여주는 종료 번호
:2 ==> 화면에 보여주는 시작 번호
Limiting SQL Rows 로 표현 할때는
SELECT *
FROM T_OFFSET
OFFSET :1 ROWS FETCH FIRST :2 ROWS ONLY;
:1 => 화면에 보여주는 시작 번호 + 1 부터 출력
:2 => 화면에 보여주고 싶은 리스트 개수
FROM (SELECT ROWNUM RN, T.*
FROM (
SELECT *
FROM T_OFFSET
) T
WHERE ROWNUM <=:1
)
WHERE RN BETWEEN :2 AND :1
;
-- 바인드 값 정리
:1 ==> 화면에 보여주는 종료 번호
:2 ==> 화면에 보여주는 시작 번호
Limiting SQL Rows 로 표현 할때는
SELECT *
FROM T_OFFSET
OFFSET :1 ROWS FETCH FIRST :2 ROWS ONLY;
:1 => 화면에 보여주는 시작 번호 + 1 부터 출력
:2 => 화면에 보여주고 싶은 리스트 개수
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 10 |
events 활용 (10384,10046,10053 ) MOS 기준
[2]  | 우뽕 | 2023.04.06 | 660 |
| 9 | 튜닝시 SQL 문장의 OutLine 정보를 추출하는 SQL 문장 | 명품관 | 2021.01.14 | 35591 |
| 8 | SQL Advisor 사용법을 이용한 튜닝 가이드 | 우뽕 | 2020.04.09 | 3519 |
| » | [12cR1 이상] Limiting SQL Rows 활용하여 페이징 처리 해 보기 [1] | 우뽕 | 2020.03.27 | 2068 |
| 6 | DB 유저가 DBMS_XPLAN 패키기 사용시 필요한 권한 | 명품관 | 2020.03.20 | 1329 |
| 5 | DBMS_SHARED_POOL 사용 | 우뽕 | 2020.03.11 | 1014 |
| 4 | Column 를 이용한 hint 사용하기 [1] | 우뽕 | 2020.02.28 | 1972 |
| 3 |
_smu_debug_mode 와_undo_autotune 관련 내용 정리
| 우뽕 | 2020.01.23 | 1241 |
| 2 | [12cR2 이상] _cursor_obsolete_threshold 기본값으로 변경 하기 | 우뽕 | 2020.01.23 | 1382 |
| 1 | V$SQL_HINT를 통해 HINT 종류 확인 | 명품관 | 2019.05.29 | 778 |
좋은 내용 감사합니다~~ 좋은 글 더 자주 올려주세요