Requêtes et traitement PL/SQL

Version imprimable

22/06/2012, 10h43
keegzer

Requêtes et traitement PL/SQL
Bonjour,

J'ai une demande assez spécifique, je suis en train de chercher un moyen de faire un traitement avec 2 de mes requêtes PL/SQL directement dans une requête.

Je m'explique, j'ai deux requêtes, une qui me récupère des informations d'une table, une autre qui me récupères d'autres informations d'une autre table.

J'aimerai les mettre en relation et voir si TOUS les infos ressorti de ma requête 2 sont inclus dans le résultat de ma première requête.

Si c'est le cas ma fonction retourne 1, sinon je renvoi 0.
C'est au niveau du traitement que je bloque je vous donne mes deux requêtes :
Code:

1 2 3 4 SELECT t2.num_emplacement FROM t2, t3 WHERE t2.num_emplacement = t3.code_emplacement AND t3.type_emplacement = variable1;
Requête 2 :
Code:

1 2 3 SELECT t1.num_emplacement FROM t1 WHERE t1.date_fin > (SELECT TO_DATE(sysdate) FROM dual);
Merci d'avance.

Ca devrait faire un truc approchant

Code:

1
2
3
4
5
6
7
8
9
select min(1, count(*) ) rstl
from (  SELECT t2.num_emplacement
	FROM t2, t3
	WHERE t2.num_emplacement = t3.code_emplacement
	AND t3.type_emplacement = variable1
        MINUS
        SELECT t1.num_emplacement
        FROM t1
        WHERE t1.date_fin > sysdate )

Evitez tout de même les

Code:

where date > (select to_date(sysdate) from dual)

, ça fait de la peine au relecteur

22/06/2012, 10h57
lola06
Bonjour,

Tu veux faire une requête ou une fonction ?
Car la démarche dans une fonction est plutôt simple, pour les requêtes une différence devrait suffire :
Code:

1 2 3 MaRequete1 MINUS MaRequete2
22/06/2012, 10h58
Rams7s

Pas si specifique que ca, ce qu'il vous faut, c'est Minus et Cortex. Avec peut-etre un COUNT, ca evitera pas mal d'erreur a gerer (pas de lignes, ou trop de lignes), et simplement au lieu d'avoir 0 ou 1, vous avez 0 ou un nombre positif.

[ame="http://www.youtube.com/watch?v=tdk8vfKhoug"]Générique Minus et Cortex - YouTube[/ame] :mouarf:

En gros j'aimerai un truc du genre :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
OPEN c1 FOR
   SELECT XXXX
CLOSE c1;
 
OPEN c2 FOR
   SELECT XXXX
CLOSE c2;
 
// Bouclage sur c2
   // Bouclage sur c1
// SI la valeur actuelle de c2 est trouvé dans c1 on continue
// Si la valeur actuelle de c2 n'est pas dans c1 je retourne 0
// On fini de boucler tant qu on arrive a trouver c2 dans c1
// Si tout c1 a ete trouvé dans c2 on retourne 1

PS : ojo77:
pourquoi éviter

Code:

WHERE date > (SELECT to_date(sysdate) FROM dual)

Citation:
Envoyé par keegzer
En gros j'aimerai un truc du genre :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 OPEN c1 FOR SELECT XXXX CLOSE c1; OPEN c2 FOR SELECT XXXX CLOSE c2; // Bouclage sur c2 // Bouclage sur c1 // SI la valeur actuelle de c2 est trouvé dans c1 on continue // Si la valeur actuelle de c2 n'est pas dans c1 je retourne 0 // On fini de boucler tant qu on arrive a trouver c2 dans c1 // Si tout c1 a ete trouvé dans c2 on retourne 1
:fessee:
Vous direz de ma part à votre prof que c'est pas en apprenant à faire des conneries qu'on devient un bon développeur. Et si votre prof n'y est pour rien et que c'est la solution que vous préconisez, vous allez avoir une sale note à votre TP

Citation:
Envoyé par keegzer
En gros j'aimerai un truc du genre :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 OPEN c1 FOR SELECT XXXX CLOSE c1; OPEN c2 FOR SELECT XXXX CLOSE c2; // Bouclage sur c2 // Bouclage sur c1 // SI la valeur actuelle de c2 est trouvé dans c1 on continue // Si la valeur actuelle de c2 n'est pas dans c1 je retourne 0 // On fini de boucler tant qu on arrive a trouver c2 dans c1 // Si tout c1 a ete trouvé dans c2 on retourne 1
Les requêtes avec le minus correspond à votre besoin pas besoin de faire des boucles.

P.S : les jointures ne s'écrivent plus de cette manière, si vous le pouvez utiliser plutôt le JOIN.

Quelque chose comme ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create or replace function f as
  l_num_emplacement t1.num_emplacement%type;
begin
  select num_emplacement into l_num_emplacement
    from (
  SELECT t1.num_emplacement
    FROM t1
   WHERE t1.date_fin > sysdate
   minus
  SELECT t2.num_emplacement
    FROM t2, t3
   WHERE t2.num_emplacement = t3.code_emplacement
     AND t3.type_emplacement = variable1
         )
   where rownum = 1;
  return 0;
exception when no_data_found then
  return 1;
end;

On ne fait pas un TO_DATE sur une date
[edit] en fait c'est pas trunc(sysdate) que tu voulais, mais juste sysdate.

22/06/2012, 11h12
keegzer

Citation:

Envoyé par lola06

Les requêtes avec me minus correspond à votre besoin pas besoin de faire des boucles.

P.S : les jointures ne s'écrivent plus de cette manière, si vous le pouvez utiliser plutôt le JOIN.

Merci beaucoup à vous tous, je vais regarder du coté de MINUS.

PS : Le WHERE et le JOIN ont exactement le même plan d'execution :ccool:
22/06/2012, 11h16
lola06

Citation:

Envoyé par keegzer

PS : Le WHERE et le JOIN ont exactement le même plan d'execution :ccool:

Peut-être mais le WHERE n'est pas une vrai jointure mais un produit cartésien. Et il est mieux de s'habituer à écrire selon la norme.

J'aurais plutôt tendance à faire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT 1-count(*)
  INTO l_result
  FROM t1
 WHERE t1.date_fin > sysdate
     AND NOT EXISTS (SELECT 1 
                       FROM t2
                           INNER JOIN t3 ON t2.num_emplacement = t3.code_emplacement
                     WHERE t3.type_emplacement = variable1
                        AND t2.num_emplacement  = t1.num_emplacement 
                         )
     AND rownum = 1

Le NOT EXISTS me semble plus parlant que le minus, et éviter une exception systématique me semble mieux.

22/06/2012, 12h35
Mohamed.Houri

Citation:

Envoyé par lola06

Peut-être mais le WHERE n'est pas une vrai jointure mais un produit cartésien. Et il est mieux de s'habituer à écrire selon la norme.

Juste pour que vous soyez au courant. Ecrivez vos jointures en AINSI JOIN et tracez les avec l'event 10053. Vous seriez surprise de voir que le CBO transformera les ANSI JOIN en Oracle JOIN.

Donc, conseillez l'utilisation des ANSI JOIN n'est pas mal en soit mais n'en faites pas une fixation.

D'ailleurs, pour votre gouverne, il y a plusieurs "bugs" liés à l'utilisation des AINSI JOIN.

Citation:

Envoyé par Rei Ichido

J'aurais plutôt tendance à faire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT 1-count(*)
  INTO l_result
  FROM t1
 WHERE t1.date_fin > sysdate
     AND NOT EXISTS (SELECT 1 
                       FROM t2
                           INNER JOIN t3 ON t2.num_emplacement = t3.code_emplacement
                     WHERE t3.type_emplacement = variable1
                        AND t2.num_emplacement  = t1.num_emplacement 
                         )
     AND rownum = 1

Le NOT EXISTS me semble plus parlant que le minus, et éviter une exception systématique me semble mieux.

Oui mais le

Code:

1
2
 
     AND rownum = 1

Vous fait passer en mode FIRST_ROWS ce qui signifie que vous seriez alors sujet à des surprises générées par le CBO.

22/06/2012, 14h24
mnitu

Auriez-vous une démonstration prouvant ce comportement ?

Citation:

Envoyé par mnitu

Auriez-vous une démonstration prouvant ce comportement ?

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
 
mohamed@mhouri> create table t_first as
  2  select rownum id
  3  , mod(rownum,100) id2
  4  , lpad('x',10) vc
  5  from dual
  6  connect by level <= 100000;
 
Table created.
 
mohamed@mhouri> create index t_id2 on t_first(id2);
 
Index created.
 
mohamed@mhouri> exec dbms_stats.gather_table_stats(user, 't_first');
 
PL/SQL procedure successfully completed.
 
mohamed@mhouri> alter session set events '10053 trace name context forever, level 1';
 
Session altered.
 
mohamed@mhouri> select
  2        id, id2, vc
  3  from t_first
  4  where rownum=1;
 
        ID        ID2 VC
---------- ---------- ----------
         1          1          x
 
1 row selected.
 
mohamed@mhouri> alter session set events '10053 trace name context off';
 
Session altered.

Dans le trace file généré, je vois ceci (en me concentrant que sur l'essentiel)

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

******************************************

*** 2012-06-22 18:45:21.296
----- Current SQL Statement for this session (sql_id=3vh3t91zt7vwz) -----
select
      id, id2, vc
from t_first
where rownum=1
*******************************************


 *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
....
optimizer_mode                      = all_rows
....

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T_FIRST"."ID" "ID","T_FIRST"."ID2" "ID2","T_FIRST"."VC" "VC" FROM "MOHAMED"."T_FIRST" "T_FIRST" WHERE ROWNUM=1
*************************
First K Rows: Setup begin
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=680, alloc=16360), compile(in-use=53252, alloc=54408), execution(in-use=1540, alloc=4060)

kkoqbc-subheap (create addr=0x0CE6FB38)
****************
QUERY BLOCK TEXT
****************
select
      id, id2, vc
from t_first
where rownum=1

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T_FIRST  Alias: T_FIRST
    #Rows: 100000  #Blks:  345  AvgRowLen:  19.00
Index Stats::
  Index: T_ID2  Col#: 2
    LVLS: 1  #LB: 195  #DK: 100  LB/K: 1.00  DB/K: 331.00  CLUF: 33100.00
Access path analysis for T_FIRST
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T_FIRST[T_FIRST] 
  Table: T_FIRST  Alias: T_FIRST
    Card: Original: 100000.000000  Rounded: 100000  Computed: 100000.00  Non Adjusted: 100000.00
  Access Path: TableScan
    Cost:  97.33  Resp: 97.33  Degree: 0
      Cost_io: 95.00  Cost_cpu: 21456897
      Resp_io: 95.00  Resp_cpu: 21456897
  Best:: AccessPath: TableScan
         Cost: 97.33  Degree: 1  Resp: 97.33  Card: 100000.00  Bytes: 0

kkoqbc: finish optimizing query block SEL$1 (#0)
First K Rows: K = 1.00, N = 100000.00
First K Rows: Setup end
***********************

Vous voyez bien que c'est le rownum = 1 qui pousse l'optimisateur à envisager implicitement le mode FIRST_ROWS_K ou K = 1 dans ce cas.

Mohamed,
Merci pour votre démonstration. Mon interprétation du fichier trace est un peu différente : on voit en fait que la présence du filtre rownum = 1 déclenche aussi une tentative d’optimisation First_Row_N en plus de celle lié au All_Rows.
ALL_ROWS

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
 
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: T_FIRST  Alias: T_FIRST
    Card: Original: 100000  Rounded: 100000  Computed: 100000.00  Non Adjusted: 100000.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  78.65  Resp: 78.65  Degree: 0
      Cost_io: 77.00  Cost_cpu: 21456897
      Resp_io: 77.00  Resp_cpu: 21456897
  Best:: AccessPath: TableScan
         Cost: 78.65  Degree: 1  Resp: 78.65  Card: 100000.00  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  T_FIRST[T_FIRST]#0
***********************
Best so far: Table#: 0  cost: 78.6527  card: 100000.0000  bytes: 1800000

FIRST_ROWS

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
First K Rows: K = 1.00, N = 100000.00
First K Rows: old pf = -1.0000000, new pf = 0.0000100
***************************************
SINGLE TABLE ACCESS PATH (First K Rows)
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: T_FIRST  Alias: T_FIRST
    Card: Original: 1  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7311
      Resp_io: 2.00  Resp_cpu: 7311
  Best:: AccessPath: TableScan
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 18
First K Rows: unchanged join prefix len = 1

Si le paramètre caché _optimizer_rownum_pred_based_fkr est mis à faux cette tentative n’est plus présente dans le fichier de trace.

Dans le cas de cette requête il y a un unique plan possible et finalement les deux chemins d’optimisation aboutissent à la même chose. Le plan First_Rows_N gagné parce que son coût est meilleur que celui de l’All_Rows.
ALL_ROWS (caché _optimizer_rownum_pred_based_fkr = false)

Code:

1
2
3
4
5
6
7
8
9
10
11
 
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |    79 |           |
| 1   |  COUNT STOPKEY      |         |       |       |       |           |
| 2   |   TABLE ACCESS FULL | T_FIRST |   98K | 1758K |    79 |  00:00:01 |
--------------------------------------+-----------------------------------+

FIRST_ROWS

Code:

1
2
3
4
5
6
7
8
9
10
11
 
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |     2 |           |
| 1   |  COUNT STOPKEY      |         |       |       |       |           |
| 2   |   TABLE ACCESS FULL | T_FIRST |     1 |    18 |     2 |  00:00:01 |
--------------------------------------+-----------------------------------+