Requête à optimiser

**farenheiit** · 16/07/2009, 14h09

Bonjour,

voyez-vous un autre moyen d'écrire la requête suivante afin d'optimiser le plan d'execution que j'ai mis en pièce jointe:

Code :

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

1
2
3
4
5
 
UPDATE WAREHO X SET X.CTRAI=1 
WHERE (X.COMAR IN ('CME') OR X.COMAR IS NULL) 
AND (EXISTS (SELECT 1 FROM WARALL Y WHERE Y.CTRAI=1 AND Y.NUWAR=X.NUWAR) 
OR EXISTS(SELECT 1 FROM WARPOR Y WHERE X.NUWAR=Y.NUWAR))

**jsd03** · 16/07/2009, 14h27

Bonjour,

A la place de

Code :

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

...X.COMAR IN ('CME') ...

J'aurai plutôt mis

Code :

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

... X.COMAR = 'CME' ...

sachant que tu ne compares qu'une seule valeur mais je ne pense pas que ça ait une grave incidence.

**farenheiit** · 16/07/2009, 14h31

ça ne change pa

**farenheiit** · 16/07/2009, 14h32

ça ne change pas le plan d'execution mais merci quand même

**florent.blin** · 16/07/2009, 14h40

Que penses tu de ce code ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
UPDATE X 
SET X.CTRAI=1 
FROM WAREHO X 
INNER JOIN WARALL Y ON Y.NUWAR=X.NUWAR AND Y.CTRAI=1
WHERE X.COMAR='CME' OR X.COMAR IS NULL
 
UPDATE X 
SET X.CTRAI=1 
FROM WAREHO X 
INNER JOIN WARPOR Y ON Y.NUWAR=X.NUWAR
WHERE X.COMAR='CME' OR X.COMAR IS NULL

**farenheiit** · 16/07/2009, 14h50

c'est du SQL ça????
ça ne marche pas => ORA-00933: la commande SQL ne se termine pas correctement

**Snipah** · 16/07/2009, 15h54

Quels sont les index sur tes différentes tables?

**mnitu** · 16/07/2009, 16h07

Peut être avec

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
UPDATE WAREHO X 
   SET X.CTRAI=1 
 WHERE (X.COMAR IN ('CME') OR X.COMAR IS NULL) 
   And NUWAR In (Select NUWAR 
                   From WARALL Y 
                  WHERE Y.CTRAI=1
                 Union --[ALL] ? 
                 Select NUWAR
                   FROM WARPOR
                )

Mais ce quoi l'état des choses avant optimisation ?

**farenheiit** · 16/07/2009, 16h13

index unique sur WAREHO.NUWAR

index unique sur WARALL.NUWAR

index composite sur WARPOR (COINT, NUCPT et NUWAR)

**pacmann** · 16/07/2009, 16h39

Salut !

Code :

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

1
2
 
index composite sur WARPOR (COINT, NUCPT et NUWAR)

C'est sûrement ça le problème ?
L'index ne peut être utilisé pour ta requête... et donc il le full scan à la place.
Crée un index ou NUWAR est en première position.

Mnitu, à propos du "--[ALL] ?" : est-ce que ça peut changer quelque chose ?
[EDIT] un petit test complètement débile :

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
 
create table testuall as
select level as nb from dual
connect by level < 100
 
;
explain plan for
select * 
from testuall
where nb in (select nb from testuall 
union select nb from testuall)
 
;
 
select * from table(dbms_xplan.display)

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
 
----------------------------------------------------------------------                                                                                                                                   
| Id  | Operation              |  Name       | Rows  | Bytes | Cost  |                                                                                                                                   
----------------------------------------------------------------------                                                                                                                                   
|   0 | SELECT STATEMENT       |             |       |       |       |                                                                                                                                   
|   1 |  MERGE JOIN            |             |       |       |       |                                                                                                                                   
|   2 |   SORT JOIN            |             |       |       |       |                                                                                                                                   
|   3 |    TABLE ACCESS FULL   | TESTUALL    |       |       |       |                                                                                                                                   
|*  4 |   SORT JOIN            |             |       |       |       |                                                                                                                                   
|   5 |    VIEW                | VW_NSO_1    |       |       |       |                                                                                                                                   
|   6 |     SORT UNIQUE        |             |       |       |       |                                                                                                                                   
|   7 |      UNION-ALL         |             |       |       |       |                                                                                                                                   
|   8 |       TABLE ACCESS FULL| TESTUALL    |       |       |       |                                                                                                                                   
|   9 |       TABLE ACCESS FULL| TESTUALL    |       |       |       |                                                                                                                                   
----------------------------------------------------------------------

(Comme le IN est fortement lié au sort unique, il ne le fait pas deux fois... au cas où, j'ai testé avec /*+no_merge*/)

**farenheiit** · 16/07/2009, 17h12

Effectivement, en créant un index sur la colonne NUWAR de la table WARPOR j'obtiens un accès via cet index sur WARPOR (RANGE SCAN) mais mon COST total est toujours de 664.
Cela est du au FTS de WAREHO => à cause du prédicat COMAR IS NULL.
Y'a t'il un moyen de contourner ce FTS ? (à part mettre une valeure par défaut à la place de NULL lors des insert dans WAREHO)

**salim11** · 16/07/2009, 17h23

Envoyé par farenheiit

Effectivement, en créant un index sur la colonne NUWAR de la table WARPOR j'obtiens un accès via cet index sur WARPOR (RANGE SCAN) mais mon COST total est toujours de 664.
Cela est du au FTS de WAREHO => à cause du prédicat COMAR IS NULL.
Y'a t'il un moyen de contourner ce FTS ? (à part mettre une valeure par défaut à la place de NULL lors des insert dans WAREHO)

SALUT,

Essaie ce code

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
UPDATE wareho x
   SET x.ctrai = 1
 WHERE NVL (x.comar, 'CME') = 'CME'
   AND (   EXISTS (SELECT 1
                     FROM warall y
                    WHERE y.ctrai = 1 AND y.nuwar = x.nuwar)
        OR EXISTS (SELECT 1
                     FROM warpor y
                    WHERE x.nuwar = y.nuwar)
       )

**mnitu** · 16/07/2009, 17h30

Envoyé par pacmann

...
Mnitu, à propos du "--[ALL] ?" : est-ce que ça peut changer quelque chose ?
...

J'avais pensé plutôt aux contraintes fonctionnelles (inconnues) qu'à l'exécution de la requête.
Mais il semble que les chemins de l'optimiseur sont compliqués.

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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> 
SQL> explain plan for Select *
  2    From emp where deptno in (select deptno
  3                                from dept
  4                               where loc like '%'
  5                              union
  6                              select deptno
  7                                from dept
  8                                where loc = 'BOSTON'
  9                             )
 10  ;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    17 |   833 |    11 |
|*  1 |  HASH JOIN            |             |    17 |   833 |    11 |
|   2 |   VIEW                | VW_NSO_1    |     5 |    65 |     8 |
|   3 |    SORT UNIQUE        |             |     5 |    55 |     8 |
|   4 |     UNION-ALL         |             |       |       |       |
|*  5 |      TABLE ACCESS FULL| DEPT        |     4 |    44 |     2 |
|*  6 |      TABLE ACCESS FULL| DEPT        |     1 |    11 |     2 |
|   7 |   TABLE ACCESS FULL   | EMP         |    10 |   360 |     2 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMP"."DEPTNO"="VW_NSO_1"."$nso_col_1")
   5 - filter("DEPT"."LOC" LIKE '%')
   6 - filter("DEPT"."LOC"='BOSTON')
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note: cpu costing is off
 
22 rows selected
 
SQL> 
SQL> explain plan for Select *
  2    From emp where deptno in (select deptno
  3                                from dept
  4                               where loc like '%'
  5                              union all
  6                              select deptno
  7                                from dept
  8                                where loc = 'BOSTON'
  9                             )
 10  ;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                       |  Name       | Rows  | Bytes | Cost (%C
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |    17 |   663 |     4  (
|   1 |  NESTED LOOPS                   |             |    17 |   663 |     4  (
|   2 |   TABLE ACCESS FULL             | EMP         |    10 |   360 |     2
|   3 |   VIEW                          | VW_NSO_1    |     2 |     6 |
|   4 |    SORT UNIQUE                  |             |     5 |       |
|   5 |     UNION-ALL PARTITION         |             |       |       |
|*  6 |      TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    11 |     2  (
|*  7 |       INDEX UNIQUE SCAN         | PK_DEPT     |     1 |       |
|*  8 |      TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    11 |     2  (
|*  9 |       INDEX UNIQUE SCAN         | PK_DEPT     |     1 |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("DEPT"."LOC" LIKE '%')
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   8 - filter("DEPT"."LOC"='BOSTON')
   9 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
 
23 rows selected
 
SQL>

**farenheiit** · 16/07/2009, 17h46

Envoyé par salim11

SALUT,

Essaie ce code

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
UPDATE wareho x
   SET x.ctrai = 1
 WHERE NVL (x.comar, 'CME') = 'CME'
   AND (   EXISTS (SELECT 1
                     FROM warall y
                    WHERE y.ctrai = 1 AND y.nuwar = x.nuwar)
        OR EXISTS (SELECT 1
                     FROM warpor y
                    WHERE x.nuwar = y.nuwar)
       )

Non j'ai tjrs le FTS.
C'est logique car t'utilise une fonction sur le champ indexé
si je crée un index fonction:

Code :

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

1
2
CREATE INDEX OPS$BRU.WAREHO3 ON OPS$BRU.WAREHO
(NVL(COMAR,'CME'))

j'obtiens un accès via l'index sur la table WAREHO avec un COST à 1.
le pb c'est que la valeur de n'est pas écrit en dur dans l'applicatif.

comment créer un index fonction NVL qui soit générique ?

**pacmann** · 16/07/2009, 17h54

Code :

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

1
2
3
 
CREATE INDEX OPS$BRU.WAREHO3 ON OPS$BRU.WAREHO
(NVL(COMAR,'bidon'))

En suite, ta requête doit tester bidon et la valeur voulue.

C'est laid, oui !

J'ai lu sur le net une autre solution qui consiste a créer ton indexe sur la colonne ET sur une valeur bidon :

Code :

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

1
2
3
 
CREATE INDEX OPS$BRU.WAREHO3 ON OPS$BRU.WAREHO
(COMAR,'a')

ce qui fait que la valeur de ta clef n'est pas nulle...
Faut voir si ça marche.

[EDIT]Bon j'ai essayé de créer un exemple... et j'ai échoué lamentablement.
C'est peut être une question de version.

Bref, le site où j'ai lu ça (le reader comment) :
http://www.dba-oracle.com/oracle_tips_null_idx.htm

**farenheiit** · 16/07/2009, 18h08

Ca ne marche pas avec la 2ème solution

concernant la 1ère solution après avoir crée l'index tu fais quoi?
ça ?

Code :

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

1
2
3
4
5
6
7
8
9
10
UPDATE wareho x
   SET x.ctrai = 1
 WHERE (NVL(x.comar, 'bidon') = 'bidon' or x.comar = 'CME')
   AND (   EXISTS (SELECT 1
                     FROM warall y
                    WHERE y.ctrai = 1 AND y.nuwar = x.nuwar)
        OR EXISTS (SELECT 1
                     FROM warpor y
                    WHERE x.nuwar = y.nuwar)
       );

**salim11** · 16/07/2009, 19h51

/* Formatted on 2009/07/16 13:42 (Formatter Plus v4.8.8) */
SELECT COUNT (1)
FROM emp_test
WHERE empno IS NULL;

CREATE INDEX tind ON emp_test(empno, 1);

SELECT COUNT (1)
FROM emp_test
Comme ça.

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
 
SQL> SELECT COUNT (1)
  2    FROM emp_test
  3   WHERE empno IS NULL;
 
  COUNT(1)
----------
         0
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP_TEST' (TABLE) (Cost=3 Card=1
           Bytes=4)
 
 
 
 
SQL> 
SQL> CREATE INDEX tind ON emp_test(empno, 1);
 
Index créé.
 
SQL> 
SQL> SELECT COUNT (1)
  2    FROM emp_test
  3   WHERE empno IS NULL;
 
  COUNT(1)
----------
         0
 
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=0 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'TIND' (INDEX) (Cost=0 Card=1 Byte
          s=4)

WHERE empno IS NULL;

**farenheiit** · 17/07/2009, 09h57

Effectivement ça marche
mais ça veut dire que mes valeurs null dans l'index sont stocké sous la valeur bidon ?

**pacmann** · 17/07/2009, 10h07

Pour la première solution, oui : la valeur est le retour de la fonction, donc bidon.

Par contre, pour la deuxième solution, le truc c'est que c'est l'agrégat des valeurs qui constitue la clef. Donc celle-ci n'est jamais nulle, vu que "1" en fait partie.

**mnitu** · 17/07/2009, 10h19

Excepté la valeur testée pour identifier les nuls les deux solutions sont la même chose : index basé sur une fonction.

Requête à optimiser

SQL Oracle

Discussions similaires

Partager

Partager