[sql] Oracle로 페이징

Program / 글쓴이

저는 제가 원하는만큼 Oracle에 익숙하지 않습니다. 약 25 만 개의 레코드가 있으며 페이지 당 100 개를 표시하고 싶습니다. 현재 데이터 어댑터 및 데이터 세트를 사용하여 데이터 세트에 25 만 개의 레코드를 모두 검색하는 하나의 저장 프로 시저와 저장된 프로 시저의 결과에 대한 dataadapter.Fill (dataset) 메서드가 있습니다. 매개 변수로 전달할 수있는 정수 값으로 “페이지 번호”및 “페이지 당 레코드 수”가있는 경우 해당 특정 섹션 만 되 돌리는 가장 좋은 방법은 무엇일까요? 내가 페이지 번호로 10을 전달하고 페이지 수로 120을 전달하면 select 문에서 1880 번째에서 1200 번째까지를 줄 것입니다.

나는 C #으로 .NET에서 이것을하고 있는데, 그것이 중요하지 않다고 생각했다. 내가 SQL 측에서 바로 얻을 수 있다면 나는 멋지다.

업데이트 : Brian의 제안을 사용할 수 있었고 잘 작동합니다. 몇 가지 최적화 작업을하고 싶지만 페이지가 1 분이 아닌 4 ~ 5 초 내에 표시되고 내 페이징 제어가 새 저장된 프록과 매우 잘 통합 될 수있었습니다.

답변

이와 같은 것이 작동합니다. From Frans Bouma의 블로그 

SELECT * FROM
(
    SELECT a.*, rownum r__
    FROM
    (
        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

답변

페이지 매김과 매우 유용한 분석 기능에 대해 Tom에게 물어보십시오 .

다음은 해당 페이지에서 발췌 한 것입니다.

select * from (
    select /*+ first_rows(25) */
     object_id,object_name,
     row_number() over
    (order by object_id) rn
        from all_objects)
    where rn between :n and :m
        order by rn;

답변

완성도 를 높이기 위해 더 현대적인 솔루션을 찾는 사람들을 위해 Oracle 12c 에는 더 나은 페이징 및 상위 처리를 포함한 몇 가지 새로운 기능이 있습니다.

페이징

페이징은 다음과 같습니다.

SELECT *
FROM user
ORDER BY first_name
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

상위 N 개 기록

최고 기록을 얻는 것은 다음과 같습니다.

SELECT *
FROM user
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY

위의 두 쿼리 예제에 어떻게 ORDER BY절이 있는지 확인하십시오 . 새 명령은이를 존중하고 정렬 된 데이터에서 실행됩니다.

FETCH또는에 대한 좋은 Oracle 참조 페이지를 찾을 수 OFFSET없었지만 이 페이지 에는 이러한 새로운 기능에 대한 훌륭한 개요가 있습니다.

공연

@wweicker가 아래 주석에서 지적했듯이 성능은 12c의 새로운 구문에서 문제입니다. Oracle이 이후 개선했는지 테스트 할 18c ​​사본이 없었습니다.

흥미롭게도 새 메서드에 대해 테이블 ​​(1 억 1,300 만 개 이상의 행)에 대한 쿼리를 처음 실행했을 때 실제 결과가 약간 더 빨리 반환되었습니다.

  • 새로운 방법 : 0.013 초.
  • 이전 방법 : 0.107 초.

그러나 @wweicker가 언급했듯이 설명 계획은 새로운 방법에 대해 훨씬 더 나빠 보입니다.

  • 새로운 방법 비용 : 300,110
  • 기존 방법 비용 : 30

새로운 구문은 전체 비용이었던 내 열의 인덱스 전체를 스캔했습니다. 인덱싱되지 않은 데이터를 제한하면 상황이 훨씬 더 악화 될 가능성이 있습니다.

이전 데이터 세트에 인덱싱되지 않은 단일 열을 포함하는 경우를 살펴 보겠습니다.

  • 새로운 방법 시간 / 비용 : 189.55 초 / 998,908
  • 기존 방법 시간 / 비용 : 1.973 초 / 256

요약 : Oracle이이 처리를 개선 할 때까지주의해서 사용하십시오. 작업 할 색인이있는 경우 새 방법을 사용하지 않아도됩니다.

조만간 사용할 18c ​​사본을 갖고 업데이트 할 수 있기를 바랍니다.

답변

답변과 의견을 요약하고 싶습니다. 페이지 매김을 수행하는 방법에는 여러 가지가 있습니다.

Oracle 12c 이전에는 OFFSET / FETCH 기능이 없었으므로 @jasonk가 제안한 백서 를 살펴보십시오 . 장단점에 대한 자세한 설명과 함께 다양한 방법에 대해 찾은 가장 완벽한 기사입니다. 여기에 복사하여 붙여 넣는 데 상당한 시간이 걸리므로하지 않습니다.

오라클 및 기타 데이터베이스 페이지 매김에 대한 몇 가지 일반적인주의 사항을 설명하는 jooq 제작자의 좋은 기사도 있습니다. jooq의 블로그 포스트

좋은 소식입니다. Oracle 12c 이후로 새로운 OFFSET / FETCH 기능이 있습니다. OracleMagazine 12c의 새로운 기능 . “Top-N 쿼리 및 페이지 매김”을 참조하십시오.

다음 진술을 발행하여 Oracle 버전을 확인할 수 있습니다. 

SELECT * FROM V$VERSION

답변

다음을 시도하십시오.

SELECT *
FROM
  (SELECT FIELDA,
    FIELDB,
    FIELDC,
    ROW_NUMBER() OVER (ORDER BY FIELDC) R
  FROM TABLE_NAME
  WHERE FIELDA = 10
  )
WHERE R >= 10
AND R   <= 15;

[tecnicume]을 통해

답변

내 프로젝트에서는 Oracle 12c 및 java를 사용했습니다 . 페이징 코드는 다음과 같습니다.

 public public List<Map<String, Object>> getAllProductOfferWithPagination(int pageNo, int pageElementSize, Long productOfferId, String productOfferName) {
    try {

        if(pageNo==1){
            //do nothing
        } else{
            pageNo=(pageNo-1)*pageElementSize+1;
        }
        System.out.println("algo pageNo: " + pageNo +"  pageElementSize: "+ pageElementSize+"  productOfferId: "+ productOfferId+"  productOfferName: "+ productOfferName);

        String sql = "SELECT * FROM ( SELECT * FROM product_offer po WHERE po.deleted=0 AND (po.product_offer_id=? OR po.product_offer_name LIKE ? )" +
             " ORDER BY po.PRODUCT_OFFER_ID asc) foo OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ";

       return jdbcTemplate.queryForList(sql,new Object[] {productOfferId,"%"+productOfferName+"%",pageNo-1, pageElementSize});

    } catch (Exception e) {
        System.out.println(e);
        e.printStackTrace();
        return null;
    }

답변