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구문을 사용하지 않지만 더 많은 옵션으로 작업을 더 잘 수행 할 수 있습니다. 전체 구문은 여기에서 찾을 수 있습니다 . ( 이 답변 에서 Oracle의 내부 작동 방식에 대해 자세히 읽어보십시오 ).

원래 질문에 대답하기 위해 다음과 같은 쿼리가 있습니다.

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;

자세한 정보는 On ROWNUM 주제 및 Oracle / AskTom의 결과 제한 주제 를 참조하십시오.

업데이트 : 하한과 상한으로 결과를 제한하기 위해 약간 더 부풀어 오른다.

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

결과

테이블에 천만 개의 레코드가 있고 정렬되지 않은 날짜 시간 행에 정렬되었습니다.

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 블로그의 기사에서 페이지 매김 쿼리에 대해 자세히 알아보십시오 .

답변

SQL 표준

이 기사 에서 설명했듯이 SQL : 2008 Standard는 다음과 같은 구문을 제공하여 SQL 결과 집합을 제한합니다.

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

Oracle 11g 및 이전 버전

버전 12c 이전에는 Top-N 레코드를 가져 오기 위해 파생 테이블과 ROWNUM 의사 열을 사용해야했습니다.

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

답변

예 :

설정