Discussion :

[punkoff]

installez une version express (XE) oracle sur votre poste ..

[pacmann]

Tu peux demander l'explain dans TOAD sans utiliser ses paramétrages :
Exécute en mode script (F5) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
EXPLAIN PLAN FOR 
TaRequête
/
 
SELECT * FROM TABLE(dbms_xplan.display)

Et sinon oui, c'est équivalent
Mais franchement, sauf si tu n'as rien d'autre à faire, essayer toutes les combinaisons de transformation de SQLPro n'est qu'une perte de temps à mon sens...

(Tiens, ton lapin a abandonné sa conquête du monde ?)

[tatayo]

Malheureusement pour le moment je ne peux toujours pas tester.
Par contre je veux bien que tu m'explique pourquoi tu met "IdB IS NULL".

J'ai ajouté cette clause pour éliminer de la table résultat les lignes pour lequelles la jointure externe ne renvoie rien.

Par rapport au dernier jeu de test, je corrige ma requête:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT 
FROM TableA A
JOIN TableB B
ON A.IdB = B.IdB
AND b.IdB LIKE 'c%'
LEFT OUTER JOIN TableB AS C ON C.idb = A.idb AND C.a = '1' AND C.b IN ('2','3','5')
WHERE C.idb IS null

Tatayo.

[Rei Ichido]

Salut !

Attention, les requêtes qu'on te propose ne donnent pas le même résultat que ta requête initiale :
- Avec idb NOT IN (...), ça exclut tout les idb si un seul remplit la condition de ta sous-requête.
- En remplaçant la sous-requête par des conditions directes sur tes lignes, tu n'exclut que les occurences de idb qui remplissent ces conditions.

C'est tout à fait vrai, mais si idb est un identifiant unique (ce que son nom suggère un peu, mais soyons prudents) cela redevient équivalent Auquel cas on aurait tort de se priver de cette information !

[pacmann]

Ah ben s'il y a une contrainte d'unicité sur IdB, elle a du sauter pour créer le jeu de test

Table B

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
IdB | a | b 
------------
c2  | 1 | 2
c1  | 1 | 4
f3  | 1 | 6
c4  | 2 | 3
e4  | 6 | 5
c2  | 1 | 3
d4  | 4 | 4

[Rei Ichido]

Ah ben s'il y a une contrainte d'unicité sur IdB, elle a du sauter pour créer le jeu de test

Judicieuse remarque !

[lola06]

Désolée pour le retard mais impossible de répondre à cette conversation. Pourquoi ? --> le mystère reste entier !!!

installez une version express (XE) oracle sur votre poste ..

Malheureusement je ne peux pas faire ce que je veux sur mon poste

Et sinon oui, c'est équivalent
Mais franchement, sauf si tu n'as rien d'autre à faire, essayer toutes les combinaisons de transformation de SQLPro n'est qu'une perte de temps à mon sens...

En fait c'est une demande afin que ce soit plus compréhensible. Mais rassure toi je ne vais pas faire toutes les combinaisons de SQLPro.
J'attend d'avoir de nouveau mes bases de recettes pour lancer l'explain plan, d'ici là...

C'est tout à fait vrai, mais si idb est un identifiant unique (ce que son nom suggère un peu, mais soyons prudents) cela redevient équivalent Auquel cas on aurait tort de se priver de cette information !

Ah ben s'il y a une contrainte d'unicité sur IdB, elle a du sauter pour créer le jeu de test

Effectivement IdB est un identifiant unique, j'ai remarqué ma première erreur et comme vous pouvez le constater elle n’apparaît plus dans mon 2ieme jeu de test .

Jeu de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 IdB | a | b 
-------------
 c2  | 1 | 2
 c1  | 1 | 4
 f3  | 1 | 6
 c4  | 2 | 3
 e4  | 6 | 5
 c5  | 1 | 4
 d4  | 4 | 4

[lola06]

J'ai ajouté cette clause pour éliminer de la table résultat les lignes pour lequelles la jointure externe ne renvoie rien.

Par rapport au dernier jeu de test, je corrige ma requête:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT 
FROM TableA A
JOIN TableB B
ON A.IdB = B.IdB
AND b.IdB LIKE 'c%'
LEFT OUTER JOIN TableB AS C ON C.idb = A.idb AND C.a = '1' AND C.b IN ('2','3','5')
WHERE C.idb IS null

Tatayo.

Merci et idem que l'explain plan, je testerai ça quand j'aurai mes bases !!

[skuatamad]

Or je souhaite ici n'exclure que les couples (a,b) tels que (1,2), (1,3) et (1,5).

Ca peut s'écrire comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT * 
  FROM TableA A 
  JOIN TableB B ON A.IdB = B.IdB 
 where IdB LIKE 'c%' 
   AND (a,b) NOT IN ((1,2),(1,3),(1,5))

[CinePhil]

45 minutes pour exécuter une requête sur un serveur de production (donc j'espère suffisamment dimensionné) sous Oracle (qui est quand même plutôt performant parait-il), même avec 6 tables de plusieurs millions de lignes en jointure, ça me semble énorme !

Peux-tu poster la requête réelle, la structure des tables impliquées et exprimer le besoin en français STP ?

[lola06]

45 minutes pour exécuter une requête sur un serveur de production (donc j'espère suffisamment dimensionné) sous Oracle (qui est quand même plutôt performant parait-il), même avec 6 tables de plusieurs millions de lignes en jointure, ça me semble énorme !

Peux-tu poster la requête réelle, la structure des tables impliquées et exprimer le besoin en français STP ?

Malheureusement je ne peux pas me permettre de poster la requête entière.
Pour le besoin initial, il évolue aussi régulièrement.
Je vais tester l'explain plan pour voir ce que ça donne.
A priori ça n'a pas l'air de choquer mes collègues mes temps de traitement.
Je ne sais pas comment sont dimensionnées les serveurs et n'ai pas la main sur les index.
Pour info mes tables font respectivement 20,15,10,9,9 et 10 millions de lignes...

[lola06]

Explain plan requête initiale :

PLAN_TABLE_OUTPUT
Plan hash value: 1994463508

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2340 | 406K| | 290K (2)| 00:58:05 | | |
| 1 | HASH GROUP BY | | 2340 | 406K| | 290K (2)| 00:58:05 | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| | 1 | 26 | | 5 (0)| 00:00:01 | ROWID | ROWID |
| 3 | NESTED LOOPS | | 2340 | 406K| | 290K (2)| 00:58:05 | | |
|* 4 | HASH JOIN | | 2082 | 309K| | 279K (2)| 00:56:00 | | |
|* 5 | HASH JOIN | | 2146 | 251K| 1072K| 156K (3)| 00:31:14 | | |
|* 6 | HASH JOIN | | 9677 | 954K| 12M| 111K (3)| 00:22:14 | | |
|* 7 | HASH JOIN | | 190K| 10M| 8576K| 75049 (3)| 00:15:01 | | |
|* 8 | TABLE ACCESS FULL | | 186K| 6386K| | 43118 (3)| 00:08:38 | | |
| 9 | TABLE ACCESS FULL | | 8768K| 175M| | 17666 (3)| 00:03:32 | | |
|* 10 | TABLE ACCESS FULL | | 438K| 18M| | 34257 (3)| 00:06:52 | | |
| 11 | VIEW | | 1909K| 34M| | 42155 (2)| 00:08:26 | | |
| 12 | HASH GROUP BY | | 1909K| 27M| 103M| 42155 (2)| 00:08:26 | | |
|* 13 | TABLE ACCESS FULL | | 1943K| 27M| | 32175 (2)| 00:06:27 | | |
| 14 | VIEW | | 8353K| 254M| | 123K (2)| 00:24:46 | | |
| 15 | HASH GROUP BY | | 8353K| 278M| 831M| 123K (2)| 00:24:46 | | |
|* 16 | TABLE ACCESS FULL | | 8353K| 278M| | 45596 (2)| 00:09:08 | | |
|* 17 | INDEX RANGE SCAN | | 2 | | | 2 (0)| 00:00:01 | | |

Explain plan requête LEFT OUTER JOIN:

PLAN_TABLE_OUTPUT
Plan hash value: 2164745441

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 210K| 35M| | 296K (2)| 00:59:14 | | |
| 1 | HASH GROUP BY | | 210K| 35M| 78M| 296K (2)| 00:59:14 | | |
| 2 | NESTED LOOPS OUTER | | 210K| 35M| | 287K (2)| 00:57:35 | | |
| 3 | NESTED LOOPS | | 2341 | 356K| | 287K (2)| 00:57:28 | | |
|* 4 | HASH JOIN | | 2082 | 280K| | 276K (2)| 00:55:23 | | |
|* 5 | HASH JOIN | | 2146 | 222K| | 153K (3)| 00:30:37 | | |
|* 6 | HASH JOIN | | 9677 | 822K| 11M| 110K (3)| 00:22:11 | | |
|* 7 | HASH JOIN | | 190K| 9471K| 7664K| 75005 (3)| 00:15:01 | | |
|* 8 | TABLE ACCESS FULL | | 186K| 5473K| | 43117 (3)| 00:08:38 | | |
| 9 | TABLE ACCESS FULL | | 8768K| 175M| | 17666 (3)| 00:03:32 | | |
|* 10 | TABLE ACCESS FULL | | 438K| 15M| | 34257 (3)| 00:06:52 | | |
| 11 | VIEW | | 1909K| 34M| | 42155 (2)| 00:08:26 | | |
| 12 | HASH GROUP BY | | 1909K| 27M| 103M| 42155 (2)| 00:08:26 | | |
|* 13 | TABLE ACCESS FULL | | 1943K| 27M| | 32175 (2)| 00:06:27 | | |
| 14 | VIEW | | 8353K| 254M| | 123K (2)| 00:24:46 | | |
| 15 | HASH GROUP BY | | 8353K| 278M| 831M| 123K (2)| 00:24:46 | | |
|* 16 | TABLE ACCESS FULL | | 8353K| 278M| | 45596 (2)| 00:09:08 | | |
|* 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| | 1 | 18 | | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 18 | INDEX RANGE SCAN | | 2 | | | 2 (0)| 00:00:01 | | |
| 19 | VIEW | | 90 | 1890 | | 0 (0)| 00:00:01 | | |
|* 20 | TABLE ACCESS BY GLOBAL INDEX ROWID| | 1 | 27 | | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 21 | INDEX RANGE SCAN | | 2 | | | 2 (0)| 00:00:01 | | |

Avez des pistes pour mon optimisation ? Merci d'avance

[Rei Ichido]

Sans la requête, c'est difficile à dire.

Une bonne piste serait d'avoir des traces étendues, pour pouvoir notamment comparer ce qu'attend l'optimiseur Oracle et la réalité en terme de cardinalité :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER session SET events '10046 trace name context forever, level 12';
<ta requête>
ALTER session SET events '10046 trace name context off';

Ce qui aidera à savoir ce qui se passe vraiment !

[Waldar]

Après avoir lu le sujet, je n'ai pas la moindre idée de ce que vous cherchez à obtenir.
On a des données, des requêtes, des filtres, des explain plan mais on n'a pas le besoin !

[lola06]

Bonjour,

Mon besoin est l'optimisation de ma requête car elle met environ 40 minutes pour s’exécuter. J'ai réussi à optimiser au maximum ma requête globale.
Je pense que je vais arrêter là puisque de toute manière je n'ai pas la main sur les index, ect.. et que j'ai d'autres requêtes à créer qui sont plus importante que l'optimisation.

Merci pour votre aide, j'ai pu découvrir de nouvelles choses !!

[Waldar]

Je me suis mal exprimé, j'ai compris pourquoi vous vouliez améliorer votre requête, mais je n'ai pas compris ce que vous voulez faire.

Votre écrivez des données de départ :
TableA

IdA | IdB
---------
 1  | c2
 2  | c1
 3  | f3
 4  | c4
 5  | e4
 6  | c2
 7  | d4

TableB

IdB | a | b 
------------
c2  | 1 | 2
c1  | 1 | 4
f3  | 1 | 6
c4  | 2 | 3
e4  | 6 | 5
c2  | 1 | 3
d4  | 4 | 4

Vous nous dites chercher à obtenir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
IdA | IdB | a | b 
------------
 2  | c1  | 1 | 4
 4  | c4  | 2 | 3

Mais comment ? Quelle règle ?
La requête pour y parvenir est très simple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT A.IdA, B.IdB, B.a, B.b
  FROM TableA A
       INNER JOIN TableB B
         ON A.IdB = B.IdB
 WHERE A.IdA in (2, 4);

Et en plus c'est super optimisé !

[lola06]

Il est vrai que je n'ai pas précisé mes conditions mais comme elles apparaissent dans ma requête je n'ai pas détaillé.
Mais j'avais bien précisé mon besoin :

Je dois retourner seulement les IdA qui respectent ces conditions pour tous leurs IdB.
(on voit bien la différence avec IdA = 1 qui ne les respectent pas, on ne renvoi pas la ligne suivante)

Conditions :
- IdB doit commencer par c
- (a,b) est différent de (1,2), (1,3) et (1,5)

Ma requête marche correctement.

[Waldar]

Ok je pense avoir cerné le problème (division relationnelle).

J'ai trois propositions de requêtes, je vous les mets dans ce que je pense être de la plus lente à la plus rapide.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
With TableA as
(
select 1 as IdA, 'c2' as IdB from dual union all
select 1       , 'c1'        from dual union all
select 2       , 'f3'        from dual union all
select 3       , 'c4'        from dual union all
select 3       , 'e4'        from dual union all
select 3       , 'c5'        from dual union all
select 4       , 'd4'        from dual
)
  ,  TableB as
(
select 'c2' as IdB, 1 as a, 2 as b from dual union all
select 'c1'       , 1     , 4      from dual union all
select 'f3'       , 1     , 6      from dual union all
select 'c4'       , 2     , 3      from dual union all
select 'e4'       , 6     , 5      from dual union all
select 'c5'       , 1     , 4      from dual union all
select 'd4'       , 4     , 4      from dual
)
SELECT A.IdA, A.IdB, B.a, B.b
  FROM TableA A
       INNER JOIN TableB B
         ON B.IdB = A.IdB
       INNER JOIN TableA C
         ON C.IDA <> A.IDA
       INNER JOIN TableB D
         ON D.IdB = C.IdB
 WHERE A.IDB LIKE 'c%'
   AND (D.A, D.B) IN ((1,2), (1,3), (1,5));

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
With TableA as
(
select 1 as IdA, 'c2' as IdB from dual union all
select 1       , 'c1'        from dual union all
select 2       , 'f3'        from dual union all
select 3       , 'c4'        from dual union all
select 3       , 'e4'        from dual union all
select 3       , 'c5'        from dual union all
select 4       , 'd4'        from dual
)
  ,  TableB as
(
select 'c2' as IdB, 1 as a, 2 as b from dual union all
select 'c1'       , 1     , 4      from dual union all
select 'f3'       , 1     , 6      from dual union all
select 'c4'       , 2     , 3      from dual union all
select 'e4'       , 6     , 5      from dual union all
select 'c5'       , 1     , 4      from dual union all
select 'd4'       , 4     , 4      from dual
)
SELECT A.IdA, A.IdB, B.a, B.b
  FROM TableA A
       INNER JOIN TableB B
         ON B.IdB = A.IdB
 where B.IdB like 'c%'
   and not exists (select null
                     from TableA C
                          inner join TableB D
                            on D.IdB = C.IdB
                    where C.IdA = A.IdA
                      and (D.a, D.b) in ((1,2), (1,3), (1,5)));

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
With TableA as
(
select 1 as IdA, 'c2' as IdB from dual union all
select 1       , 'c1'        from dual union all
select 2       , 'f3'        from dual union all
select 3       , 'c4'        from dual union all
select 3       , 'e4'        from dual union all
select 3       , 'c5'        from dual union all
select 4       , 'd4'        from dual
)
  ,  TableB as
(
select 'c2' as IdB, 1 as a, 2 as b from dual union all
select 'c1'       , 1     , 4      from dual union all
select 'f3'       , 1     , 6      from dual union all
select 'c4'       , 2     , 3      from dual union all
select 'e4'       , 6     , 5      from dual union all
select 'c5'       , 1     , 4      from dual union all
select 'd4'       , 4     , 4      from dual
)
  ,  SR as
(
SELECT A.IdA, B.IdB, B.a, B.b,
       max(case when (B.a, B.b) in ((1,2), (1,3), (1,5)) then 1 end) over(partition by A.idA) as chk
  FROM TableA A
       INNER JOIN TableB B
         ON B.IdB = A.IdB
 WHERE A.IdB like 'c%'
)
select IdA, IdB, a, b
  from SR
 where chk is null;

[lola06]

Merci pour ces réponses mais j'ai actuellement 19 millions d'enregistrements dans ma table. Je ne pense pas que cette solution soit réalisable dans ces conditions.

[Waldar]

La volumétrie en soit, est un problème annexe d'une requête.

Voici deux tables de 26 millions de lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
69
70
71
72
73
74
75
create table TableB
(
    IdB varchar2(10) not null
  , a   number(1)    not null
  , b   number(1)    not null
  , constraint pk_TableB
      primary key (IdB) rely
      using index
)
compress for all operations;
-- Table created.
 
create table TableA
(
    IdA number(18)   not null
  , IdB varchar2(10) not null
  , constraint pk_TableA
      primary key (IdA, IdB)
      using index
  , constraint fk_TableA_TableB
      foreign key (IdB)
      references TableB (IdB)
      rely disable novalidate
)
organization index
compress;
-- Table created.
 
set timing on;
 
insert /*+ append */ into TableB (IdB, a, b)
With SR1 as
(
     select chr(ascii('a') + level - 1) as letter
       from dual
connect by level <= 26
)
  ,  SR2 as
(
     select level as num
       from dual
connect by level <= 1e3
)
select SR1.letter || to_char(rownum)   as IdB
     , round(dbms_random.value(0, 9)) as a
     , round(dbms_random.value(0, 9)) as b
  from SR1 cross join SR2 cross join SR2 SR3
 where rownum < 26e6;
 
-- 25999999 rows created.
-- Elapsed: 00:05:39.99
 
commit;
-- Commit complete.
 
insert into TableA (IdA, IdB)
select round(dbms_random.value(1, 1e7))
     , IdB
  from TableB;
 
-- 25999999 rows created.
-- Elapsed: 00:07:33.84
 
commit;
-- Commit complete.
 
begin
    dbms_stats.gather_table_stats(ownname => user, tabname => 'TABLEA', cascade => true, degree => 16);
    dbms_stats.gather_table_stats(ownname => user, tabname => 'TABLEB', cascade => true, degree => 16);
end;
 
-- PL/SQL procedure successfully completed.
-- Elapsed: 00:00:17.47
 
set timing off;

Je ne teste pas la première requête, je sais qu'elle est pas top.

Je corrige la troisième, je n'avais pas mis le filtre c% au bon endroit.
Comparons :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
SELECT A.IdA, A.IdB, B.a, B.b
  FROM TableA A
       INNER JOIN TableB B
         ON B.IdB = A.IdB
 WHERE B.IdB LIKE 'c%'
   AND NOT EXISTS (SELECT NULL
                     FROM TableA C
                          INNER JOIN TableB D
                            ON D.IdB = C.IdB
                    WHERE C.IdA = A.IdA
                      AND (D.a, D.b) IN ((1,2), (1,3), (1,5)));
 
-- Elapsed: 00:01:41.03
 
With SR as
(
SELECT A.IdA, B.IdB, B.a, B.b,
       max(case when (B.a, B.b) IN ((1,2), (1,3), (1,5)) then 1 end) over(partition BY A.idA) AS chk
  FROM TableA A
       INNER JOIN TableB B
         ON B.IdB = A.IdB
)
SELECT IdA, IdB, a, b
  FROM SR
 WHERE chk IS NULL
   AND IdB LIKE 'c%';
 
-- Elapsed: 00:03:16.28

Finalement la deuxième est la plus rapide, et on est quand même très loin des 40 minutes.