Come limitare il numero di righe restituite da una query Oracle dopo l’ordine?

C’è un modo per far si che una query Oracle comporti come se contenga una clausola MySQL limit ?

In MySQL , posso farlo:

 select * from sometable order by name limit 20,10 

per ottenere il 21esimo alla 30a riga (salta il primo 20, dai il prossimo 10). Le righe vengono selezionate dopo l’ order by , quindi inizia realmente sul nome XX in ordine alfabetico.

In Oracle , l’unica cosa che la gente menziona è la pseudo colonna di rownum , ma viene valutata prima order by , il che significa che:

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

restituirà un set casuale di dieci righe ordinate per nome, che di solito non è quello che voglio. Inoltre non consente di specificare un offset.

A partire da Oracle 12c R1 (12.1), esiste una clausola di limitazione delle righe . Non usa la syntax LIMIT conosciuta, ma può fare il lavoro meglio con più opzioni. Puoi trovare la syntax completa qui .

Per rispondere alla domanda originale, ecco la query:

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

(Per versioni Oracle precedenti, fare riferimento ad altre risposte in questa domanda)


Esempi:

Gli esempi seguenti sono stati citati dalla pagina collegata , nella speranza di impedire il link rot.

Impostare

 CREATE TABLE rownum_order_test ( val NUMBER ); INSERT ALL INTO rownum_order_test SELECT level FROM dual CONNECT BY level <= 10; COMMIT; 

Cosa c'è nel tavolo?

 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. 

Ottieni le prime N righe

 SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY; VAL ---------- 10 10 9 9 8 5 rows selected. 

Prendi le prime N righe, se N una fila ha delle cravatte, prendi tutte le righe legate

 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. 

Top x % di righe

 SELECT val FROM rownum_order_test ORDER BY val FETCH FIRST 20 PERCENT ROWS ONLY; VAL ---------- 1 1 2 2 4 rows selected. 

Usando un offset, molto utile per l'impaginazione

 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. 

È ansible combinare l'offset con le percentuali

 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. 

Puoi usare una subquery per questo come

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

Dai un'occhiata anche all'argomento su ROWNUM e ai risultati limitati su Oracle / AskTom per maggiori informazioni.

Aggiornamento : per limitare il risultato con entrambi i limiti inferiore e superiore le cose si gonfiano un po 'di più

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

(Copiato dall'articolo AskTom specificato)

Aggiornamento 2 : a partire da Oracle 12c (12.1) è disponibile una syntax per limitare le righe o iniziare con gli offset.

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

Vedi questa risposta per ulteriori esempi. Grazie a Krumia per il suggerimento.

Ho fatto alcuni test delle prestazioni per i seguenti approcci:

Asktom

 select * from ( select a.*, ROWNUM rnum from (  

analitico

 select * from (  

Breve alternativa

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

risultati

La tabella aveva 10 milioni di record, l'ordinamento era su una riga datetime non indicizzata:

  • Spiega piano ha mostrato lo stesso valore per tutti e tre i seletti (323168)
  • Ma il vincitore è AskTom (con seguito analitico dietro)

La selezione delle prime 10 righe ha richiesto:

  • AskTom: 28-30 secondi
  • Analitico: 33-37 secondi
  • Breve alternativa: 110-140 secondi

Selezione di righe tra 100.000 e 100.010:

  • AskTom: 60 secondi
  • Analitico: 100 secondi

Selezione delle righe tra 9.000.000 e 9.000.010:

  • AskTom: 130 secondi
  • Analitico: 150 secondi

Una soluzione analitica con una sola query nidificata:

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

Rank() potrebbe essere sostituito da Row_Number() ma potrebbe restituire più record di quanto ci si aspetti se ci sono valori duplicati per nome.

Su Oracle 12c (vedere la clausola di limitazione delle righe in riferimento SQL ):

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

Le query di impaginazione con l’ordine sono davvero complicate in Oracle.

Oracle fornisce una pseudocolonna ROWNUM che restituisce un numero che indica l’ordine in cui il database seleziona la riga da una tabella o una serie di viste congiunte.

ROWNUM è una pseudocolonna che mette molte persone nei guai. Un valore ROWNUM non viene assegnato in modo permanente a una riga (questo è un malinteso comune). Può essere fonte di confusione quando viene assegnato un valore ROWNUM. Un valore ROWNUM viene assegnato a una riga dopo che ha superato i predicati di filtro della query ma prima di aggregazione o ordinamento di query .

Inoltre, un valore ROWNUM viene incrementato solo dopo che è stato assegnato.

Questo è il motivo per cui la seguente query non restituisce righe:

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

La prima riga del risultato della query non passa il predicato ROWNUM> 1, quindi ROWNUM non aumenta a 2. Per questo motivo, nessun valore ROWNUM diventa maggiore di 1, di conseguenza, la query non restituisce righe.

La query correttamente definita dovrebbe assomigliare a questa:

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

Scopri di più sulle query di impaginazione nei miei articoli sul blog di Vertabelo :

  • Oracle ROWNUM spiegato
  • Top-N e query di impaginazione

Meno istruzioni SELECT. Inoltre, meno consumo di prestazioni. Crediti a: [email protected]

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

Se non si è su Oracle 12C, è ansible utilizzare la query TOP N come di seguito.

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

Puoi anche spostare questo da clausola in clausola come segue

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

Qui in realtà stiamo creando una vista in linea e rinominando rownum come rnum. Puoi utilizzare rnum nella query principale come criterio di filtro.

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

più grande poi i valori lo scoprono

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

meno poi i valori lo scoprono

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

Ho iniziato a prepararmi per l’esame Oracle 1z0-047, convalidato contro 12c. Mentre lo stavo preparando mi sono imbattuto in un miglioramento 12c noto come “FETCH FIRST” che ti consente di recuperare righe / righe limite secondo la tua convenienza. Diverse opzioni sono disponibili con esso

 - 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 

Esempio:

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

In oracle

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

VAL

  10 10 9 9 8 

5 righe selezionate.

SQL>

(non testato) qualcosa come questo potrebbe fare il lavoro

 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 

C’è anche il rango della funzione analitica, che puoi usare per ordinare.

Come sopra con le correzioni. Funziona ma sicuramente non è carina.

  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 

Onestamente, meglio usare le risposte di cui sopra.