Compter les lignes renvoyées par une requête qui plante
La mesure de l’échec
Il est possible dans une requête SQL d’utiliser une expression dans la clause de projection, cette expression pouvant être une sous-requête. Cela signifie que la syntaxe
SELECT col1, col2, SELECT ..., col3 FROM nom_table est correcte.
La limitation est que le SELECT utilisé comme expression doit ici renvoyer au plus UNE ligne pour chaque ligne de la table NOM_TABLE. A défaut, vous obtiendrez une erreur explicite ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression.
Il est par ailleurs possible de compter les résultats renvoyés par une requête en écrivant SELECT cout(*) FROM (SELECT ...) ALIAS.
Vous pouvez combiner ces deux possibilités avec une requête du style SELECT count(*) FROM (SELECT col1, col2, SELECT ..., col3 FROM nom_table) ALIAS.
Intuitivement, nous pourrions penser que la limitation citée précédemment s’applique toujours et que si la requête (SELECT col1, col2, SELECT ..., col3 FROM nom_table) plante alors la requête SELECT count(*) FROM (SELECT col1, col2, SELECT ..., col3 FROM nom_table) ALIAS plantera également.
Qu’en est-il en pratique ? Démonstration avec PostgreSQL 10 :
select version();
version
--------------------------------------------------------------------------------------------------
PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.2.0-12) 7.2.1 20171025, 64-bit
(1 ligne)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
create table geants(
idg integer primary key,
idgu uuid,
nmge varchar(128),
devise varchar(128));
CREATE TABLE
with recursive serie(i) as
(select 1
UNION ALL
select i+1 from serie where i < 500000)
insert into geants(idg, idgu, nmge, devise)
select
i,
uuid_generate_v4(),
upper(md5(random()::text)),
upper(md5(random()::text))
from serie;
INSERT 0 500000
create table lancers(
idg integer,
perf integer);
CREATE TABLE
with recursive serie(i) as
(select 1
UNION ALL
select i+1 from serie where i < 500000)
insert into lancers(idg, perf)
select
trunc(random() * 500000 + 1),
case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end
from serie;
INSERT 0 500000
select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g;
ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression
select count(*)
from
(select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g) r;
count
--------
500000
(1 ligne)
explain select count(*)
from
(select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g) r;
QUERY PLAN
-------------------------------------------------------------------------------------------
Finalize Aggregate (cost=10747.38..10747.39 rows=1 width=8)
-> Gather (cost=10747.17..10747.38 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=9747.17..9747.18 rows=1 width=8)
-> Parallel Seq Scan on geants g (cost=0.00..9226.33 rows=208333 width=0)
(5 lignes)
Temps : 1,056 ms
with r as (select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g)
select count(*) from r;
ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression
explain
with r as (select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g)
select count(*) from r;
QUERY PLAN
------------------------------------------------------------------------------
Aggregate (cost=4231523393.00..4231523393.01 rows=1 width=8)
CTE r
-> Seq Scan on geants g (cost=0.00..4231512143.00 rows=500000 width=8)
SubPlan 1
-> Seq Scan on lancers l (cost=0.00..8463.00 rows=2 width=4)
Filter: (idg = g.idg)
-> CTE Scan on r (cost=0.00..10000.00 rows=500000 width=0)
(7 lignes)
Cornegidouille ! Le premier select count(*) ne plante pas en raison d'une optimisation. Le planner PostgreSQL ne considère pas la clause de projection qui n’est pas censée avoir une influence sur le nombre de lignes renvoyées. Le plan d’exécution ne fait donc aucune référence à la table lancers. Il n’y a ici aucune clause de restriction WHERE, le planner en conclut que le nombre de lignes à compter correspond au nombre de lignes de la table geants.
La situation est différente avec une CTE (with query) en raison du comportement du planner en ce qui les concerne. A part si elle n’est pas citée dans la requête principale, une with query est systématiquement exécutée avec PostgreSQL, ce qui explique le plantage du select count(*) dans ce cas.
A présent, réalisons le même test avec Oracle Database 12.2.0.1 :
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
create table geants(
idg integer primary key,
idgu raw(16),
nmge varchar(128),
devise varchar(128)) tablespace users;
Table created.
insert into geants(idg, idgu, nmge, devise)
with serie(i) as
(select 1 from dual
UNION ALL
select i+1 from serie where i < 500000)
select
i,
sys_guid(),
dbms_random.string('P', 32) ,
dbms_random.string('P', 32)
from serie;
500000 rows created.
create table lancers(
idg integer,
perf integer);
Table created.
insert into lancers(idg, perf)
with serie(i) as
(select 1 from dual
UNION ALL
select i+1 from serie where i < 500000)
select
trunc(dbms_random.value*500000+1) ,
trunc(dbms_random.value*50000+1)
from serie;
500000 rows created.
select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g;
ERROR:
ORA-01427: single-row subquery returns more than one row
select count(*)
from
(select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g) r;
COUNT(*)
----------
500000
explain plan for select count(*)
from
(select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g) r;
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3332795107
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 272 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C005652 | 524K| 272 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic statistics used: dynamic sampling (level=2)
13 rows selected.
with r as (select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g)
select count(*) from r;
COUNT(*)
----------
500000
explain plan for with r as (select g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g)
select count(*) from r;
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3332795107
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 272 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C005652 | 524K| 272 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
- dynamic statistics used: dynamic sampling (level=2)
13 rows selected.
with r as (select /*+ materialize */ g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g)
select count(*) from r;
with r as (select /*+ materialize */ g.idg, (select perf from lancers l where l.idg = g.idg)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
explain plan for
with r as (select /*+ materialize */ g.idg, (select perf from lancers l where l.idg = g.idg)
from geants g)
select count(*) from r;
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4125088535
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 895K (1)| 00:00:35 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660D_1185C1 | | | | |
|* 3 | TABLE ACCESS FULL | LANCERS | 4459 | 113K| 2 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN | SYS_C005652 | 524K| 6657K| 272 (0)| 00:00:01 |
| 5 | SORT AGGREGATE | | 1 | | | |
| 6 | VIEW | | 524K| | 256 (2)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_1185C1 | 524K| 6657K| 256 (2)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("L"."IDG"=:B1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
23 rows selected.
Oracle Database réalise le même type d’optimisation et le comportement observé est donc ici largement similaire à celui observé avec PostgreSQL.
Seule différence, les with queries ne sont pas systématiquement exécutées avec Oracle Database, à part si le hint /*+ materialize */ est utilisé. Cela explique qu’aucun plantage ne survienne sur le deuxième SELECT count(*) alors que le dernier plante.
Mise à jour : 01/12/2017