Requête paginée avec ROWID

Version imprimable

09/07/2012, 11h51
SheikYerbouti

Requête paginée avec ROWID
Bonjour,

Je souhaite mettre en place un système de requête paginée pour autoriser des temps de réponse correct avec des tables volumineuses.

J'utilise habituellement la requête suivante:
Code:

1 2 SELECT * from ( select subreq.*, rownum r from ( SELECT t.* FROM EMP t ) subreq where rownum <= 10) where r >= 5
Cependant, l'ajout du ROWID dans les colonnes de la reqête génère une infâme erreur ORA-01446:cannot select ROWID from view with DISTINCT, GROUP BY, etc.
Code:

1 2 SELECT * from ( select subreq.*, rownum r from ( SELECT t.ROWID, t.* FROM EMP t ) subreq where rownum <= 10) where r >= 5
Quelqu'un connait-il une solution de contournement ?
D'avance merci,

Il manque l'ORDER BY dans ta requête exemple :).
Apparement c'est à cause de rownum, avec row_number ça marche :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> SELECT *
  2    FROM ( SELECT subreq.*
  3             FROM ( SELECT t.ROWID, t.*,
  4                           row_number() over (order by empno) as rn
  5                      FROM EMP t ) subreq
  6            WHERE rn <= 10)
  7   WHERE rn >=  5;
 
ROWID                   EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO         RN
------------------ ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
AAAF8jAABAAALJJAAE       7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400         30          5
AAAF8jAABAAALJJAAF       7698 BLAKE      MANAGER         7839 01/05/81       2850                    30          6
AAAF8jAABAAALJJAAG       7782 CLARK      MANAGER         7839 09/06/81       2450                    10          7
AAAF8jAABAAALJJAAH       7788 SCOTT      ANALYST         7566 09/12/82       3000                    20          8
AAAF8jAABAAALJJAAI       7839 KING       PRESIDENT            17/11/81       5000                    10          9
AAAF8jAABAAALJJAAJ       7844 TURNER     SALESMAN        7698 08/09/81       1500          0         30         10
 
6 rows selected.
 
SQL>

Mais bon dans le plan on perd le STOP KEY... Donc pas sûr que ce soit optimal.
Mais comme on ne sait pas l'objectif le contournement est peut être suffisant.

09/07/2012, 13h37
Waldar

Citation:

Envoyé par SheikYerbouti

Quelqu'un connait-il une solution de contournement ?

Avec les pseudo-colonnes, si on veut les remonter il faut les aliaser, sinon au niveau du dessus Oracle s'y perd.

Citation:

Envoyé par skuatamad

Il manque l'ORDER BY dans ta requête exemple :).
Apparement c'est à cause de rownum, avec row_number ça marche :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> SELECT *
  2    FROM ( SELECT subreq.*
  3             FROM ( SELECT t.ROWID, t.*,
  4                           row_number() over (order by empno) as rn
  5                      FROM EMP t ) subreq
  6            WHERE rn <= 10)
  7   WHERE rn >=  5;
 
ROWID                   EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO         RN
------------------ ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
AAAF8jAABAAALJJAAE       7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400         30          5
AAAF8jAABAAALJJAAF       7698 BLAKE      MANAGER         7839 01/05/81       2850                    30          6
AAAF8jAABAAALJJAAG       7782 CLARK      MANAGER         7839 09/06/81       2450                    10          7
AAAF8jAABAAALJJAAH       7788 SCOTT      ANALYST         7566 09/12/82       3000                    20          8
AAAF8jAABAAALJJAAI       7839 KING       PRESIDENT            17/11/81       5000                    10          9
AAAF8jAABAAALJJAAJ       7844 TURNER     SALESMAN        7698 08/09/81       1500          0         30         10
 
6 rows selected.
 
SQL>

Mais bon dans le plan on perd le STOP KEY... Donc pas sûr que ce soit optimal.
Mais comme on ne sait pas l'objectif le contournement est peut être suffisant.

Merci, seulement:

Code:

row_number() over (ORDER BY empno) AS rn

ici ORDERBY empno est "en dur" alors que ce système est sensé s'adapter à un nom de table donné dynamiquement.
Le but est d'afficher dans un bloc dynamique (un JTable Java) les données de n'importe quelle table.

Il suffit d'aliaser rowid

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL> SELECT * FROM 
  2    ( SELECT subreq.*, ROWNUM r 
  3       FROM ( SELECT t.ROWID , t.* FROM dual t ORDER BY 1) subreq 
  4     WHERE ROWNUM <= 10   
  5        ) 
  6  WHERE r >=  5;
SELECT * FROM
       *
ERROR at line 1:
ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.
 
 
SQL> SELECT * FROM 
  2    ( SELECT subreq.*, ROWNUM r 
  3       FROM ( SELECT t.ROWID idrow, t.* FROM dual t ORDER BY 1) subreq 
  4     WHERE ROWNUM <= 10   
  5        ) 
  6  WHERE r >=  5;
 
no rows selected
 
SQL>

09/07/2012, 15h44
mnitu

ORDER BY rowid

[Edit]
Attention, n’importe quelle solution pose le problème de tri
[/Edit]
09/07/2012, 15h55
SheikYerbouti

Toutefois, ceci semble fonctionner dans tous les cas ;)

Code:

row_number() over (ORDER BY 1) AS rn
09/07/2012, 15h56
mnitu

Citation:

Envoyé par SheikYerbouti

Toutefois, ceci semble fonctionner dans tous les cas ;)

Code:

row_number() over (ORDER BY 1) AS rn

Ce n’est pas sérieux ! 8O
Utilise Order by rowid.
09/07/2012, 15h57
Waldar

J'ai un problème avec le concept, si l'ordre n'a pas d'importance, pourquoi ne pas faire simplement un WHERE ROWNUM <= 5 ?
09/07/2012, 16h00
mnitu

Pareil j’ai un problème avec le concept requête paginée sans tri

@Waldar
Rownum between N and N+5
09/07/2012, 16h09
pachot

Bonjour,

Citation:

Envoyé par SheikYerbouti

Toutefois, ceci semble fonctionner dans tous les cas ;)

Code:

row_number() over (ORDER BY 1) AS rn

attention ici 1 ne fait pas référence à la première colonne (comme dans le order by d'un select) mais est simplement la constante 1 ... donc il n'y a pas de tri.

Mais j'ai du mal à imaginer l’intérêt de faire de la pagination sans avoir un ordre de tri. L'utilisateur fait quoi quand on lui afficher les 5 premiers sans lui dire dans quel ordre ? Sauf s'il en veut juste 5 au hasard.

Cordialement,
Franck.
09/07/2012, 16h13
SheikYerbouti

La sous requête contient éventuellement une clause ORDER BY

Merci à tous :ccool:
09/07/2012, 16h32
Waldar
Citation:

Envoyé par mnitu

Rownum between N and N+5

Ça doit être l'effet de la pluie en juillet :
Code:

1 2 3 4 5 SELECT * FROM scott.emp WHERE ROWNUM BETWEEN 5 AND 10; -- no records

Citation:

Envoyé par Waldar

Ça doit être l'effet de la pluie en juillet :

Code:

1
2
3
4
5
SELECT *
  FROM scott.emp
 WHERE ROWNUM BETWEEN 5 AND 10;
 
-- no records

Certainement :lol:

[Edit]

Code:

1
2
3
4
5
6
7
8
9
 
Select ename
  From (
        Select rownum rn, e.*
          From scott.emp e
          order by empno 
       )  
 Where rn between 5 and 10  
/

[/Edit]

09/07/2012, 16h42
McM

C'est juste pour dire que ce que veux faire Sheik, c'est un simili rownum between N and N+5 (équivalent de LIMIT en mysql)