Requête à optimiser

Version imprimable

Voir 40 message(s) de cette discussion en une page

1 pièce(s) jointe(s)

Requête à optimiser

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:

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))

16/07/2009, 14h27
jsd03

Bonjour,

A la place de

Code:

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

J'aurai plutôt mis

Code:

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

sachant que tu ne compares qu'une seule valeur mais je ne pense pas que ça ait une grave incidence.
16/07/2009, 14h31
farenheiit

ça ne change pa
16/07/2009, 14h32
farenheiit

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

Que penses tu de ce code ?

Code:

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

16/07/2009, 14h50
farenheiit

c'est du SQL ça????
ça ne marche pas => ORA-00933: la commande SQL ne se termine pas correctement
16/07/2009, 15h54
Snipah

Quels sont les index sur tes différentes tables?

Peut être avec

Code:

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 ?

16/07/2009, 16h13
farenheiit

index unique sur WAREHO.NUWAR

index unique sur WARALL.NUWAR

index composite sur WARPOR (COINT, NUCPT et NUWAR)

Salut !

Code:

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:

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:

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*/)

16/07/2009, 17h12
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)

Citation:

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:

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) )

Citation:

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:

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>

Citation:
Envoyé par salim11
SALUT,

Essaie ce code
Code:

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:

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 ?

16/07/2009, 17h54
pacmann
Code:

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:

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

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:

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)
       );

/* 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:

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;

17/07/2009, 09h57
farenheiit

Effectivement ça marche
mais ça veut dire que mes valeurs null dans l'index sont stocké sous la valeur bidon ?
17/07/2009, 10h07
pacmann

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.
17/07/2009, 10h19
mnitu

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

Voir 40 message(s) de cette discussion en une page