Comment remonter le 1er item absent d'une table ?

Version imprimable

06/08/2013, 17h52
david2109

Comment remonter le 1er item absent d'une table ?
Bonjour,

La requete suivante remonte les NUCP qui ne sont pas dans la liste fournie en paramétre
Code:

1 2 3 SELECT NUCP FROM TBCP WHERE NUCP NOT IN ('300030120000020000010 300030120000020000020 300030120000020000030')
remonte

Citation:

300030250000020009991
300030250000020009992
300030450000020009900
300030450000020009901
300030450000020009990
ES8601080030200030001000
ES7101080030200030010000
ES4401080030200030010001
300030250000020001511
300030250000020001495
BDB01GDB0100020000117
BIT01GIT0156983652366
BIT01GIT0100000235687
300030250010000250001
BES01GES0100000256003
300030250015689475396
BDB01GDB014568569321567
BIT01GIT015869423512689
08705260038610405
08705260037016212
300030250045869326598
300030250000023658741
300030451000020125232

En fait, je cherche à faire l'inverse :une requete qui me remonte le 1er numéro de la liste qui ne soit pas dans la table.

Je ne vois pas bien comment m'y prendre en une seule passe. ?

PS : en terme de perf, vaut-il mieux essayer de faire ceci ou bien un simple select qui vérifie si qqchose remonte est tout aussi performant ? (la liste en paramétre peut avoir une cinquantaine d'elements mais pas bcp plus)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH 
T1 AS 
(SELECT '300030120000020000010' AS N FROM DUAL UNION ALL
 SELECT '300030120000020000020' AS N FROM DUAL UNION ALL
 SELECT '300030120000020000030'),
T2 AS
(SElECT N FROM T
 MINUS
 SELECT NUCP FROM TBCP)
T3 AS
(SELECT N, ROW_NUMBER() OVER(ORDER BY N) AS RANG
 FROM   T2)
SELECT N
FROM   T3
WHERE  RANG = 1;

A +

07/08/2013, 09h03
david2109
Bonjour,

Je ne parviens pas à executer cette requete. J'obtiens une erreur

ORA-00923: mot-clé FROM absent à l'emplacement prévu

Par ailleurs, si je comprends bien, la partie
Code:

1 2 3 4 5 6 T1 AS (SELECT '300030120000020000010' AS N FROM DUAL UNION ALL SELECT '300030120000020000020' AS N FROM DUAL UNION ALL SELECT '300030120000020000030'),
permet de simuler la table TBCP. Dès lors, il serait plus juste d'indiquer des numéros de cette liste :

Citation:

300030250000020009991
300030250000020009992
300030450000020009900
300030450000020009901
300030450000020009990
ES8601080030200030001000
ES7101080030200030010000
ES4401080030200030010001
300030250000020001511
300030250000020001495

et je ne vois pas bien ensuite à quel endroit dois-je faire figurer lle parametre permettant d'indiquer les numéros dont je cherche à déterminer s'ils sont absent de la table ?
07/08/2013, 09h51
pacmann
Salut,

Justement non, T1 ne simule pas ta table, mais liste les numéros paramétrés pour la recherche.

Il manque juste un petit FROM DUAL là-dedans :
Code:

1 2 3 4 5 T1 AS (SELECT '300030120000020000010' AS N FROM DUAL UNION ALL SELECT '300030120000020000020' AS N FROM DUAL UNION ALL SELECT '300030120000020000030' FROM DUAL),
@SQLPro, tu veux te convertir à Oracle ? Grande nouvelle, et sois le bienvenu !

Maintenant, côté performance, l'important est d'avoir un index sur ta colonne.

Un petit test :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
SQL> create table TBCP as select level NUCP from dual connect by level <= 100000;
 
Table created.
 
Elapsed: 00:00:00.29
SQL> create index tbcpix on tbcp(nucp);
 
Index created.
 
SQL> delete from tbcp where nucp = 99999;
 
1 row deleted.
 
Elapsed: 00:00:00.04
 
SQL> delete from tbcp where nucp = 88888;
 
1 row deleted.
 
Elapsed: 00:00:00.04
SQL> commit;
 
Commit complete.
 
SQL> set autot on explain

La requête, avec son plan :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
SQL> WITH T1 AS
  2   (SELECT 1 AS N FROM DUAL UNION ALL
  3    SELECT 88888 AS N FROM DUAL UNION ALL
  4    SELECT 99999 FROM DUAL)
  5   SELECT *
  6   FROM T1
  7   WHERE n NOT IN (SELECT nucp
  8              FROM tbcp
  9              WHERE nucp IS NOT NULL)
 10     AND n IS NOT NULL
 11     AND rownum <= 1;
 
         N
----------
     88888
 
Elapsed: 00:00:00.11
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1660097535
 
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    17 |     6   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |        |       |       |            |          |
|   2 |   NESTED LOOPS ANTI|        |     2 |    34 |     6   (0)| 00:00:01 |
|   3 |    VIEW            |        |     2 |     8 |     5  (20)| 00:00:01 |
|   4 |     UNION-ALL      |        |       |       |            |          |
|   5 |      FAST DUAL     |        |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL     |        |     1 |       |     2   (0)| 00:00:01 |
|   7 |      FAST DUAL     |        |     1 |       |     2   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN| TBCPIX |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=1)
   8 - access("N"="NUCP")
       filter("NUCP" IS NOT NULL)

Tu noteras que je filtre les NULL, parce que je n'ai pas déclaré la colonne en NOT NULL. Comme les entrées d'index totalement NULL ne sont pas stockées, il n'est pas possible de vérifier la non existance d'une valeur dans cet index si elle est potentiellement nulle...

12/08/2013, 10h04
david2109

Bonjour,

Comme les paramétres passés à la requete SQL seront envoyé par du code, il ne m'est pas possible de modifier dynamiquement la partie T1 de la requete.

Auriez-vous une solution qui permette de passer plusieurs paramétres en entrée sans devoir modifier la requete à chaque fois ?

Sinon, ce n'est pas tres grave : j'ai toujours la possibilité de faire une boucle sur chaque élément de ma liste de paramétre et de boucler jusqu'à ce que la requete ne renvoie rien.

Cdt
12/08/2013, 10h53
pacmann
Salut,

Sous quel format sont passé les paramètres ?
Si tu arrives à le passer sous forme de liste, T1 peut donner un truc de ce genre :
Code:

1 2 3 4 5 6 7 8 9 10 SQL> select column_value n 2 from table(sys.odcinumberlist(1,88888,99999)) 3 / N ---------- 1 88888 99999
Si c'est sous forme de caractères, il faut "tokenizer" la chose, ce qui se fait par exemple à base de connect by level, instr, et substr.

Il faut découper la chaine de caractère :
Lisez Varying in lists pour plus d'information :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
SQL> var txt varchar2(4000);
SQL> exec :txt := '300030120000020000010,300030120000020000020,300030120000020000030';
 
PL/SQL procedure successfully completed.
 
SQL>   with data as (
  2  select level as lvl,
  3         trim( substr (txt,
  4                       instr (txt, ',', 1, level  ) + 1,
  5                       instr (txt, ',', 1, level+1)
  6                       - instr (txt, ',', 1, level) -1 )
  7              ) as token
  8    from (select ','||:txt||',' as txt
  9            from dual)
 10  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
 11  ),
 12         t as (
 13  select lvl, token, row_number() over (order by lvl) as rn
 14    from data
 15   where token not in (select nucp from tbcp)
 16  )
 17  select token
 18    from t
 19   where rn = 1;
 
TOKEN
------------------------------
300030120000020000010
 
SQL>