Programing

주문 후 Oracle 쿼리에서 반환되는 행 수를 어떻게 제한합니까?

crosscheck 2020. 9. 28. 08:16

주문 후 Oracle 쿼리에서 반환되는 행 수를 어떻게 제한합니까?

Oracle쿼리가 MySQL limit절이 포함 된 것처럼 동작하도록 하는 방법이 있습니까?

에서는 다음 MySQL을 수행 할 수 있습니다.

select * 
from sometable
order by name
limit 20,10

21 번째 행에서 30 번째 행을 얻으려면 (처음 20 개를 건너 뛰고 다음 10 개를 제공) 행은 뒤에 선택 order by되므로 실제로 20 번째 이름에서 알파벳순으로 시작됩니다.

에서은 Oracle, 사람들이 말할 수있는 유일한 것은입니다 rownum의사 열하지만, 평가 전에 order by 이 의미 :

select * 
from sometable
where rownum <= 10
order by name

일반적으로 내가 원하는 것이 아닌 이름순으로 정렬 된 임의의 10 개 행 집합을 반환합니다. 또한 오프셋을 지정할 수 없습니다.

오라클 12C R1 (12.1)에서 시작, 거기에 있다 절을 제한하는 행 . 익숙한 LIMIT구문을 사용하지 않지만 더 많은 옵션을 사용하면 작업을 더 잘 수행 할 수 있습니다. 여기 에서 전체 구문을 찾을 수 있습니다 .

원래 질문에 답하기위한 쿼리는 다음과 같습니다.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(이전 Oracle 버전의 경우이 질문의 다른 답변을 참조하십시오)

예 :

다음 예제는 링크 부패를 방지하기 위해 링크 된 페이지 에서 인용되었습니다 .

설정

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

테이블에 뭐가 있지?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

첫 번째 `N`행 가져 오기

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

첫째 얻을 `N`경우, 행 `N`^번째 행이 관계를 가지고, 모든 연결 행을 얻을

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

`x`행의 상위 %

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

오프셋을 사용하면 페이지 매김에 매우 유용합니다.

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

오프셋과 백분율을 결합 할 수 있습니다.

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

이와 같은 하위 쿼리를 사용할 수 있습니다.

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

자세한 내용은 Oracle / AskTom에서 ROWNUM 에 대한 주제 및 결과 제한 을 참조하십시오.

업데이트 : 하한과 상한으로 결과를 제한하려면 상황이 조금 더 부풀어 오릅니다.

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(지정된 AskTom 기사에서 복사)

업데이트 2 : Oracle 12c (12.1)부터 행을 제한하거나 오프셋에서 시작하는 데 사용할 수있는 구문이 있습니다.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

더 많은 예를 보려면 이 답변 을 참조하십시오 . 힌트를 주신 Krumia에게 감사드립니다.

다음 접근 방식에 대해 성능 테스트를 수행했습니다.

Asktom

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

분석

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

짧은 대안

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

결과

테이블에는 1000 만 개의 레코드가 있으며 정렬은 인덱싱되지 않은 datetime 행에 있습니다.

Explain plan이 세 가지 선택 모두에 대해 동일한 값을 표시 함 (323168)
하지만 승자는 AskTom입니다 (분석적 후속 작업이 뒤처짐)

처음 10 개 행을 선택하는 데 걸리는 시간 :

AskTom : 28-30 초
분석 : 33-37 초
짧은 대안 : 110-140 초

100,000에서 100,010 사이의 행 선택 :

AskTom : 60 초
분석 : 100 초

9,000,000에서 9,000,010 사이의 행 선택 :

AskTom : 130 초
분석 : 150 초

중첩 된 쿼리가 하나만있는 분석 솔루션 :

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank()대체 될 수 Row_Number()있지만 name에 대한 중복 값이있는 경우 예상보다 더 많은 레코드를 리턴 할 수 있습니다 .

Oracle 12c ( SQL 참조의 행 제한 절 참조 ) :

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

순서가있는 페이지 매김 쿼리는 Oracle에서 정말 까다 롭습니다.

Oracle은 데이터베이스가 테이블 또는 조인 된 뷰 집합에서 행을 선택하는 순서를 나타내는 숫자를 반환하는 ROWNUM 의사 열을 제공합니다.

ROWNUM은 많은 사람들을 문제에 빠뜨리는 의사 열입니다. ROWNUM 값은 행에 영구적으로 할당되지 않습니다 (일반적인 오해입니다). ROWNUM 값이 실제로 할당되면 혼란 스러울 수 있습니다. ROWNUM 값은 쿼리의 필터 조건 자를 통과 한 후 쿼리 집계 또는 정렬 전에 행에 할당됩니다 .

또한 ROWNUM 값은 할당 된 후에 만 증가합니다.

이것이 후속 쿼리가 행을 반환하지 않는 이유입니다.

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1;

쿼리 결과의 첫 번째 행은 ROWNUM> 1 술어를 전달하지 않으므로 ROWNUM은 2로 증가하지 않습니다. 따라서 ROWNUM 값은 1보다 크지 않으므로 쿼리는 행을 반환하지 않습니다.

올바르게 정의 된 쿼리는 다음과 같아야합니다.

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1;

Vertabelo 블로그의 내 기사에서 페이지 매김 쿼리에 대해 자세히 알아보십시오 .

더 적은 SELECT 문. 또한 성능 소모가 적습니다. 크레딧 : anibal@upf.br

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

Oracle 12C가 아닌 경우 아래와 같이 TOP N 쿼리를 사용할 수 있습니다.

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

다음과 같이 with 절에서이 from 절을 이동할 수도 있습니다.

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

여기서 실제로 인라인 뷰를 만들고 rownum을 rnum으로 이름을 바꿉니다. 기본 쿼리에서 rnum을 필터 기준으로 사용할 수 있습니다.

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

값이 알아내는 것보다 크다

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

값보다 적다.

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

받아 들여지는 대답 의 확장으로 Oracle은 내부적으로 ROW_NUMBER/RANK기능을 사용 합니다. OFFSET FETCH구문은 구문 설탕입니다.

DBMS_UTILITY.EXPAND_SQL_TEXT절차 를 사용하여 관찰 할 수 있습니다 .

샘플 준비 :

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;
COMMIT;

질문:

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

규칙적 :

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
               ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" 
      FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;

db <> 바이올린 데모

확장 된 SQL 텍스트를 가져 오는 중 :

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

WITH TIES다음과 같이 확장됩니다 RANK.

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
              RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

및 오프셋 :

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/


SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
             ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
       WHERE "A1"."rowlimit_$$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) 
             ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4 
ORDER BY "A1"."rowlimit_$_0"

저는 Oracle 1z0-047 시험 준비를 시작했으며, 12c에 대해 검증되었습니다. 준비하는 동안 'FETCH FIRST'로 알려진 12c 개선 사항을 발견했습니다. 그것은 당신의 편의에 따라 행 / 제한 행을 가져올 수 있습니다. 여러 옵션을 사용할 수 있습니다.

- FETCH FIRST n ROWS ONLY
 - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
 - n % rows via FETCH FIRST N PERCENT ROWS ONLY

예:

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY

쿼리에서 반환 된 각 행에 대해 ROWNUM 의사 열은 Oracle이 테이블 또는 조인 된 행 집합에서 행을 선택하는 순서를 나타내는 숫자를 반환합니다. 선택한 첫 번째 행에는 1의 ROWNUM이 있고 두 번째 행에는 2가있는 식입니다.

  SELECT * FROM sometable1 so
    WHERE so.id IN (
    SELECT so2.id from sometable2 so2
    WHERE ROWNUM <=5
    )
    AND ORDER BY so.somefield AND ROWNUM <= 100

나는 이것을 oracle서버 에서 구현했다.11.2.0.1.0

SQL 표준

이 기사 에서 설명했듯이 SQL : 2008 표준은 SQL 결과 집합을 제한하기 위해 다음 구문을 제공합니다.

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g 및 이전 버전

Prior to version 12c, to fetch the Top-N records, you had to use a derived table and the ROWNUM pseudocolumn:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

In oracle

SELECT val FROM   rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY;

VAL

5 rows selected.

SQL>

(untested) something like this may do the job

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

There is also the analytic function rank, that you can use to order by.

Same as above with corrections. Works but definitely not pretty.

   WITH
    base AS
    (
        select *                   -- get the table
        from sometable
        order by name              -- in the desired order
    ),
    twenty AS
    (
        select *                   -- get the first 30 rows
        from base
        where rownum <= 30
        order by name              -- in the desired order
    )
    select *                       -- then get rows 21 .. 30
    from twenty
    where rownum < 20
    order by name                  -- in the desired order

Honestly, better to use the above answers.

참고URL : https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering

'Programing' 카테고리의 다른 글

로컬 저장소와 쿠키 (0)	2020.09.28
Node.js + Nginx-이제 무엇입니까? (0)	2020.09.28
문자의 ASCII 값을 얻는 방법은 무엇입니까? (0)	2020.09.28
.gitignore가 추적되지 않는 파일 목록에 나타나지 않도록하려면 어떻게해야합니까? (0)	2020.09.28
문자열에 부분 문자열이 포함되어 있는지 어떻게 확인합니까? (0)	2020.09.28

현재글주문 후 Oracle 쿼리에서 반환되는 행 수를 어떻게 제한합니까?

crosscheck

주문 후 Oracle 쿼리에서 반환되는 행 수를 어떻게 제한합니까?

주문 후 Oracle 쿼리에서 반환되는 행 수를 어떻게 제한합니까?

예 :

설정

테이블에 뭐가 있지?

첫 번째 `N`행 가져 오기

첫째 얻을 `N`경우, 행 `N`^번째 행이 관계를 가지고, 모든 연결 행을 얻을

`x`행의 상위 %

오프셋을 사용하면 페이지 매김에 매우 유용합니다.

오프셋과 백분율을 결합 할 수 있습니다.

Asktom

분석

짧은 대안

결과

SQL 표준

Oracle 11g 및 이전 버전

VAL

'Programing' 카테고리의 다른 글

'Programing'의 다른글

티스토리툴바

주문 후 Oracle 쿼리에서 반환되는 행 수를 어떻게 제한합니까?

주문 후 Oracle 쿼리에서 반환되는 행 수를 어떻게 제한합니까?

예 :

설정

테이블에 뭐가 있지?

첫 번째 N행 가져 오기

첫째 얻을 N경우, 행 N번째 행이 관계를 가지고, 모든 연결 행을 얻을

x행의 상위 %

오프셋을 사용하면 페이지 매김에 매우 유용합니다.

오프셋과 백분율을 결합 할 수 있습니다.

Asktom

분석

짧은 대안

결과

SQL 표준

Oracle 11g 및 이전 버전

VAL

'Programing' 카테고리의 다른 글

'Programing'의 다른글

관련글

티스토리툴바

첫 번째 `N`행 가져 오기

첫째 얻을 `N`경우, 행 `N`^번째 행이 관계를 가지고, 모든 연결 행을 얻을

`x`행의 상위 %