Réserver des lignes avec une requête top-N, Oracle et PostgreSQL
Posté : jeu. 18 avr. 2019 16:25
Merci à Richard pour sa question :
"J'ai un problème avec une requête top-N écrite avec le mot clé propriétaire "rownum" et exécutée sur une base Oracle. Elle est censée réserver une seule ligne après un tri sur 2 colonnes.
Avec la table des géants du site, elle pourrait donner ça :
Ce n'est pas bon fonctionnellement, le développeur est tombé dans le piège décrit ici : https://pgphil.ovh/top94.php
J'ai voulu corriger et ça fonctionne ... mais pas avec le "for update" .
Passer sur une syntaxe standard SQL est possible en 19c mais ça donne la même erreur et de toute façon la requête doit encore être compatible avec Oracle 11.2 qui n'a pas "fetch first N rows only" :
Quelle est la solution ?"
Réponse :
Passer sur PostgreSQL :
Avec Oracle 12c et versions supérieures, une écriture possible mais lourde est :
Pour les versions Oracle 11.2 et inférieures :
Encore un peu plus lourd comme écriture mais ça fonctionne.
"J'ai un problème avec une requête top-N écrite avec le mot clé propriétaire "rownum" et exécutée sur une base Oracle. Elle est censée réserver une seule ligne après un tri sur 2 colonnes.
Avec la table des géants du site, elle pourrait donner ça :
Code : Tout sélectionner
Oracle Database 19c Enterprise Edition - 19.2.0.0.0
create table geants(idge int primary key, dnge date, taille int);
insert into geants values(1, current_date - 10000, 450);
insert into geants values(2, current_date - 9000, 400);
insert into geants values(3, current_date - 8000, 450);
insert into geants values(4, current_date - 7000, 380);
insert into geants values(5, current_date - 6000, 410);
insert into geants values(6, current_date - 5000, 430);
select idge from geants where rownum = 1 order by taille desc, dnge desc for update;
1
J'ai voulu corriger et ça fonctionne ... mais pas avec le "for update" .
Code : Tout sélectionner
select idge from (select idge from geants order by taille desc, dnge desc) where rownum = 1;
3
select idge from (select idge from geants order by taille desc, dnge desc) where rownum = 1 for update;
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
Code : Tout sélectionner
select idge from geants order by taille desc, dnge desc fetch first 1 row only for update;
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
Réponse :
Passer sur PostgreSQL :
Code : Tout sélectionner
select version();
version
--------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.21 on x86_64-pc-linux-gnu (Ubuntu 9.4.21-1.pgdg18.04+1), compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit
(1 ligne)
create table geants(idge int primary key, dnge date, taille int);
insert into geants values(1, current_date - 10000, 450);
insert into geants values(2, current_date - 9000, 400);
insert into geants values(3, current_date - 8000, 450);
insert into geants values(4, current_date - 7000, 380);
insert into geants values(5, current_date - 6000, 410);
insert into geants values(6, current_date - 5000, 430);
select idge from geants order by taille desc, dnge desc fetch first 1 row only for update;
idge
------
3
(1 ligne)
Code : Tout sélectionner
Oracle Database 19c Enterprise Edition - 19.2.0.0.0
...
select idge
from geants
where
idge = (select idge from geants order by taille desc, dnge desc fetch first 1 row only)
for update;
3
Code : Tout sélectionner
select idge
from geants
where
idge = (select idge from (select idge from geants order by taille desc, dnge desc) where rownum = 1)
for update;
3