Réserver des lignes avec une requête top-N, Oracle et PostgreSQL

La norme SQL évolue : tirez le meilleur d'un SGBD qui la respecte en écrivant du SQL moderne avec en complément PL/pgSQL, php, java etc.
Répondre
Phil
Administrateur du site
Messages : 225
Enregistré le : mar. 1 sept. 2015 00:38
Localisation : France
Contact :

Réserver des lignes avec une requête top-N, Oracle et PostgreSQL

Message par Phil » 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 :

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
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" .

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.
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" :

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.
Quelle est la solution ?"

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)
Avec Oracle 12c et versions supérieures, une écriture possible mais lourde est :

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
Pour les versions Oracle 11.2 et inférieures :

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
Encore un peu plus lourd comme écriture mais ça fonctionne.
Cdlt. Phil - pgphil.ovh

Répondre