Paging z Oracle

Nie znam Oracle tak dobrze, jak bym chciał. Mam jakieś 250 tys. Rekordów i chcę wyświetlać je po 100 na stronie. Obecnie mam jedną procedurę składowaną, która pobiera wszystkie ćwierć miliona rekordów do zestawu danych za pomocą adaptera danych i zestawu danych oraz metody dataadapter.Fill (zestaw danych) na wynikach z przechowywanego procesu. Jeśli mam „Numer strony” i „Liczba rekordów na stronie” jako wartości całkowite, które mogę przekazać jako parametry, jaki byłby najlepszy sposób na odzyskanie tylko tej konkretnej sekcji. Powiedzmy, że jeśli przepuszczę 10 jako numer strony i 120 jako liczbę stron, z polecenia select da mi to liczbę od 1880 do 1200, czy coś w tym rodzaju, moja matematyka w mojej głowie może się nie udać.

Robię to w .NET z C #, myślę, że to nie jest ważne, jeśli mogę to zrobić po stronie sql, to powinienem być spoko.

Aktualizacja: udało mi się skorzystać z sugestii Briana i działa świetnie. Chciałbym popracować nad optymalizacją, ale strony pojawiają się w ciągu 4 do 5 sekund, a nie minuty, a moja kontrola stronicowania była w stanie bardzo dobrze zintegrować się z moimi nowymi przechowywanymi procesami.

Coś takiego powinno działać: z bloga Fransa Boumy

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)

Zapytaj Toma o paginację i bardzo, bardzo przydatne funkcje analityczne.

Oto fragment tej strony:

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;

W trosce o kompletność, dla osób poszukujących nowocześniejszego rozwiązania w Oracle 12c zawiera kilka nowych funkcji, w tym lepsze stronicowanie i najwyższą obsługę.

Paging

Strona wygląda następująco:

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

Najlepsze rekordy N.

Uzyskanie najlepszych rekordów wygląda następująco:

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

Zwróć uwagę, jak mają oba powyższe przykłady zapytań ORDER BY klauzule. Nowe polecenia uwzględniają je i są uruchamiane na posortowanych danych.

Nie mogłem znaleźć dobrej strony referencyjnej Oracle dla FETCHlub, OFFSETale ta strona zawiera świetny przegląd tych nowych funkcji.

Występ

Jak @wweicker wskazuje w komentarzach poniżej, wydajność jest problemem w nowej składni w 12c. Nie miałem kopii 18c, aby sprawdzić, czy Oracle od tego czasu ulepszyło ją.

Co ciekawe, moje rzeczywiste wyniki zostały zwrócone nieco szybciej, gdy po raz pierwszy uruchomiłem zapytania na mojej tabeli (113 milionów + wierszy) dla nowej metody:

• Nowa metoda: 0,013 sekundy.
• Stara metoda: 0,107 sekundy.

Jednak, jak wspomniał @wweicker, plan wyjaśniający wygląda znacznie gorzej dla nowej metody:

• Koszt nowej metody: 300,110
• Koszt starej metody: 30

Nowa składnia spowodowała pełne skanowanie indeksu w mojej kolumnie, czyli całego kosztu. Są szanse, że sytuacja jest znacznie gorsza, gdy ograniczamy dane niezindeksowane.

Spójrzmy, dołączając pojedynczą niezindeksowaną kolumnę do poprzedniego zbioru danych:

• Czas / koszt nowej metody: 189,55 sekund / 998908
• Czas / koszt starej metody: 1,973 sekundy / 256

Podsumowanie: używaj ostrożnie, dopóki Oracle nie poprawi tej obsługi. Jeśli masz indeks do pracy, być może nowa metoda może ci się udać.

Mam nadzieję, że wkrótce będę mieć kopię 18c i mogę ją zaktualizować

Chcę tylko podsumować odpowiedzi i komentarze. Istnieje wiele sposobów tworzenia stronicowania.

Przed oracle 12c nie było funkcji OFFSET / FETCH, więc zapoznaj się z raportem jak sugerował @jasonk. Jest to najbardziej kompletny artykuł, jaki znalazłem na temat różnych metod, zawierający szczegółowe wyjaśnienie ich zalet i wad. Kopiowanie i wklejanie ich tutaj zajęłoby dużo czasu, więc nie zrobię tego.

Jest też dobry artykuł od twórców jooq, wyjaśniający niektóre typowe zastrzeżenia dotyczące stronicowania Oracle i innych baz danych. post na blogu jooq

Dobra wiadomość, od oracle 12c mamy nową funkcjonalność OFFSET / FETCH. Nowe funkcje OracleMagazine 12c . Zobacz sekcję „Najczęstsze zapytania i paginacja N”

Możesz sprawdzić swoją wersję Oracle, wydając następujące oświadczenie

SELECT * FROM V$VERSION

Spróbuj wykonać następujące czynności:

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;

via [tecnicume]

W moim projekcie wykorzystałem Oracle 12c i java . Kod stronicowania wygląda następująco:

 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;
    }