Pertanyaan Bagaimana cara membatasi jumlah baris yang dikembalikan oleh permintaan Oracle setelah memesan?


Apakah ada cara untuk membuatnya Oracle query berperilaku seperti itu mengandung MySQL limit ayat?

Di MySQL, Aku bisa melakukan ini:

select * 
from sometable
order by name
limit 20,10

untuk mendapatkan 21 ke baris ke-30 (lewati 20 pertama, berikan 10 berikutnya). Baris dipilih setelah order by, jadi itu benar-benar dimulai pada nama ke-20 menurut abjad.

Di Oracle, satu-satunya hal yang disebut orang adalah rownum pseudo-kolom, tetapi dievaluasi sebelum  order by, yang berarti ini:

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

akan mengembalikan satu set acak dari sepuluh baris yang diurutkan berdasarkan nama, yang biasanya bukan yang saya inginkan. Ini juga tidak memungkinkan untuk menentukan offset.


805
2018-01-22 19:48


asal


Jawaban:


Mulai dari Oracle 12c R1 (12.1), di sana aku s Sebuah klausa pembatasan baris. Itu tidak terbiasa LIMIT sintaks, tetapi dapat melakukan pekerjaan dengan lebih baik dengan lebih banyak opsi. Anda dapat menemukan sintaks lengkap di sini.

Untuk menjawab pertanyaan asli, berikut pertanyaannya:

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

(Untuk versi Oracle sebelumnya, silakan merujuk ke jawaban lain dalam pertanyaan ini)


Contoh:

Contoh berikut dikutip dari halaman yang ditautkan, dengan harapan mencegah pembusukan tautan.

Mempersiapkan

CREATE TABLE rownum_order_test (
  val  NUMBER
);

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

COMMIT;

Apa yang ada di meja?

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.

Dapatkan dulu N baris

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

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

5 rows selected.

Dapatkan dulu N baris, jika Nth baris memiliki ikatan, dapatkan semua baris yang diikat

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.

Puncak x% dari baris

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

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

4 rows selected.

Menggunakan offset, sangat berguna untuk pagination

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.

Anda dapat menggabungkan offset dengan persentase

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.

328
2017-09-26 04:01



Anda dapat menggunakan subkueri untuk ini seperti

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

Lihat juga topiknya Pada ROWNUM dan hasil yang membatasi di Oracle / AskTom untuk informasi lebih lanjut.

Memperbarui: Untuk membatasi hasil dengan batas bawah dan atas hal-hal menjadi sedikit lebih kembung

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;

(Disalin dari AskTom-artikel tertentu)

Perbarui 2: Dimulai dengan Oracle 12c (12.1) ada sintaks yang tersedia untuk membatasi baris atau mulai pada offset.

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

Lihat jawaban ini untuk lebih banyak contoh. Terima kasih untuk Krumia untuk petunjuknya.


713
2018-01-22 19:55



Saya melakukan beberapa pengujian kinerja untuk pendekatan berikut:

Asktom

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

Analitis

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

Alternatif Singkat

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

Hasil

Tabel memiliki 10 juta catatan, semacam pada baris data yang tidak terindeks:

  • Jelaskan rencana menunjukkan nilai yang sama untuk ketiga pilihan (323168)
  • Tapi pemenangnya adalah AskTom (dengan mengikuti analitik di belakang)

Memilih 10 baris pertama yang diambil:

  • AskTom: 28-30 detik
  • Analitis: 33-37 detik
  • Alternatif singkat: 110-140 detik

Memilih baris antara 100.000 dan 100.010:

  • AskTom: 60 detik
  • Analitis: 100 detik

Memilih baris antara 9.000.000 dan 9.000.010:

  • AskTom: 130 detik
  • Analitis: 150 detik

166
2018-06-30 14:20



Solusi analitik hanya dengan satu kueri tersarang:

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

Rank() bisa diganti Row_Number() tetapi mungkin mengembalikan lebih banyak catatan daripada yang Anda perkirakan jika ada nilai duplikat untuk nama.


52
2018-01-23 14:28



Pada Oracle 12c (lihat baris membatasi klausa di Referensi SQL):

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

27
2017-09-24 03:09



Pertanyaan paginasi dengan pemesanan benar-benar rumit dalam Oracle.

Oracle menyediakan pseudocolumn ROWNUM yang mengembalikan angka yang menunjukkan urutan di mana database memilih baris dari tabel atau kumpulan tampilan yang digabungkan.

ROWNUM adalah pseudocolumn yang membuat banyak orang mengalami kesulitan. Nilai ROWNUM tidak secara permanen ditetapkan ke baris (ini adalah kesalahpahaman umum). Ini mungkin membingungkan ketika nilai ROWNUM benar-benar ditetapkan. Nilai ROWNUM ditetapkan ke baris setelah melewati predikat filter dari kueri tetapi sebelum agregasi atau sortir kueri.

Terlebih lagi, nilai ROWNUM hanya bertambah setelah ditetapkan.

Inilah sebabnya mengapa kueri followin tidak menghasilkan baris:

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

Baris pertama hasil kueri tidak lulus predikat ROWNUM> 1, jadi ROWNUM tidak bertambah menjadi 2. Karena alasan ini, tidak ada nilai ROWNUM yang lebih besar dari 1, akibatnya, kueri tidak akan menghasilkan baris.

Kueri yang ditentukan dengan benar akan terlihat seperti ini:

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

Cari tahu lebih lanjut tentang kueri pagination di artikel saya di Vertabelo blog:


10
2018-04-12 17:32



Kurang PILIH pernyataan. Juga, kurang memakan kinerja. Kredit untuk: anibal@upf.br

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

7
2018-03-02 14:32