Optimisation de requete

Version imprimable

25/09/2006, 17h06
zekmek

Optimisation de requete

Bonjour,

J'ai la requete suivante qui met plus de 2 minutes a s'executer. Je m'y prend sans doutes tres mal... quelqu'un sait il comment je peux l'optimiser ?

SELECT
count(distinct(extractvalue (value(images),'//@numfich')))
FROM ae7qtme1 me1,
TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE me1.ID=methode.ID
and upper
(
extractvalue (value(images),'//@numfich')
)
NOT IN
(
SELECT
upper(mg1.NAME)
FROM ae7qtmg1 mg1,
ae7qtme1 me1,
ae7qtlkc lkc,
TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
where me1.id=lkc.FK_METHOD_ID
and lkc.FK_IMAGE_ID=mg1.ID
and me1.ID=methode.ID
and upper(extractvalue (value(images),'//@numfich')) = upper(mg1.NAME)
)

Merci d'utiliser :tagcode:

Sinon il faut éviter les utilisaons de NOT IN

c'est vite fait mais essaye

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
         count(distinct(extractvalue (value(images),'//@numfich')))
FROM     ae7qtme1 me1,
         TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE    me1.ID=methode.ID
and 
NOT EXISTS
(
SELECT
upper(mg1.NAME)
FROM ae7qtmg1 mg1,
ae7qtme1 me1,
ae7qtlkc lkc,
TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
where me1.id=lkc.FK_METHOD_ID
and lkc.FK_IMAGE_ID=mg1.ID
and me1.ID=methode.ID
and upper(extractvalue (value(images),'//@numfich')) = upper(mg1.NAME)
)

Peux tu me dire d'ou vient methode.ID qui n'est présent dans aucun FROM

25/09/2006, 17h51
zekmek

Merci sygale :-) et désolé, la prochaine je mettrais la balise CODE ...

Citation:

Envoyé par sygale

Peux tu me dire d'ou vient methode.ID qui n'est présent dans aucun FROM

En fait c'est une requete imbriquée dans une autre... c'est pourquoi j'ai methode.ID. Je ne voulais pas mettre l'intégralité de ma requete car elle est énorme.

En effet, je ne savais pas mais le NOT IN me pompe tout le temps d'execution. Par contre j'ai du mal a le remplacer ... sinon je peux faire 2 requetes mais j'aurais aimé eviter.

Pour info, j'arrive à éxécuter sans erreur la requete que tu m'as donné avec le NOT EXISTS mais je n'obtiens pas le bon résultat.

voila plus d'infos ...

Code:

1
2
3
4
-- Renvoi = 5
SELECT count(distinct(upper(extractvalue (value(images),'//@numfich'))))
FROM ae7qtme1 me1,TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE me1.ID=29746

Code:

1
2
3
4
-- Renvoi = 1323_FORMAT_D , C4AP1NCD , C4AP1NDD , C4AP1NED , C4AP1NFD
SELECT distinct(upper(extractvalue (value(images),'//@numfich')))
FROM ae7qtme1 me1,TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE me1.ID=29746

Code:

1
2
3
4
5
6
7
8
9
10
11
-- Renvoi = C4AP1NCD , C4AP1NDD , C4AP1NED , C4AP1NFD
SELECT
   distinct(upper(mg1.NAME))
   FROM ae7qtmg1 mg1,
   ae7qtme1 me1,
   ae7qtlkc lkc,
   TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
   where me1.id=lkc.FK_METHOD_ID
   and lkc.FK_IMAGE_ID=mg1.ID
   and me1.ID=29746
   and upper(extractvalue (value(images),'//@numfich')) = upper(mg1.NAME)

Maintenant quand j'imbrique toutes les requetes, j'aurais aimé trouvé 1 qui serait l'enregistrement : 1323_FORMAT_D. Or je trouve 0.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Renvoi = 0
SELECT count(distinct(upper(extractvalue (value(images),'//@numfich'))))
FROM ae7qtme1 me1,TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
WHERE me1.ID=29746
and NOT EXISTS
(
   SELECT
   distinct(upper(mg1.NAME))
   FROM ae7qtmg1 mg1,
   ae7qtme1 me1,
   ae7qtlkc lkc,
   TABLE ( XMLSEQUENCE ( extract(me1.docxml, '//Image') ) ) images
   where me1.id=lkc.FK_METHOD_ID
   and lkc.FK_IMAGE_ID=mg1.ID
   and me1.ID=29746
   and upper(extractvalue (value(images),'//@numfich')) = upper(mg1.NAME)
)

25/09/2006, 18h19
remi4444
Est-ce que ta dernière requête est toujours longue ?

Ce qu'il faut faire avant tout, passer les stats sur ton schéma:
Code:

1 2 3 4 5 begin dbms_stats.gather_schema_stats('TON_USER'); end; /
Si la lenteur persiste, il faut te poser la question suivante (et y répondre... ;)): "Quelle est le (ou les) critère(s) qui fait que ma requête ne me renvoie que quelques lignes et non pas des millions ?".
25/09/2006, 18h52
remi4444

Remarques subsidiaires:

- Utiliser le meme alias (me1) dans la requête et la sous-requête, c'est pas génial, ça veux dire qu'il n'y a pas de jointure entre les 2... (remplacée par la répétition de "me1.ID=29746" ). La jointure à faire serait plutot du style "sous_alias_me1.ID = me1.ID"

- ce n'est pas la peine d'utiliser un "distinct" dans la sous requête. Les clauses de filtrage exists/not exists sont justement optimisées pour s'arréter au 1ier enregistrement trouvé.

- si les tables ae7qtmg1 et ae7qtlkc sont plutot grosses, il faut s'assurer que leurs colonnes respectives mg1.ID et FK_METHOD_ID sont bien indexées.

Question:
Est-ce que les XML sont volumineux ?

Que donne ceci ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT COUNT(DISTINCT(UPPER(extractvalue (VALUE(images),'//@numfich'))))
FROM AE7QTME1 me1,
    TABLE ( XMLSEQUENCE ( EXTRACT(me1.docxml, '//Image') ) ) images
WHERE me1.ID=29746
AND NOT EXISTS
              (
                 SELECT 1
                 FROM AE7QTMG1 mg1,
                      AE7QTLKC lkc
                 WHERE lkc.fk_method_id = me1.ID
                 AND mg1.ID = lkc.fk_image_id
                 AND UPPER(extractvalue (VALUE(images),'//@numfich')) = UPPER(mg1.NAME)
              )

26/09/2006, 09h35
zekmek

héhé, cette dernière requete marche super bien, elle renvoie le bon resultat mais par contre elle met autant de temps que la requete d'origine c'est a dire 73 000 ms ...
26/09/2006, 10h25
McM

Faut des infos sur les tables, fichiers et l'explain plan.

Voici les infos sur les 3 tables.
J'ai un index sur la colonne NAME dans AE7QTMG1.

Par contre je n'arrive pas a creer l'index suivant:

Code:

CREATE INDEX AE7QTME6 ON AE7QTME1(extractValue(DOCXML,'//Image//@numfich'));

il me met:

Citation:

longueur maximum de clé atteinte

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
 
CREATE TABLE AE7QTME1
(
    ID 					  NUMBER NOT NULL,
    PARENT_ID 			  NUMBER,
    VERSION               NUMBER DEFAULT '0' NOT NULL,
    METHOD_VERSION		  NUMBER DEFAULT '0' NOT NULL,
	WORKFLOW_ID			  NUMBER, 
    CODE				  VARCHAR2 (10),
    CODE_NATURE			  VARCHAR2 (1),
    LANGUE_ORIGINE 		  VARCHAR2(5) NOT NULL,
    CANCELED			  NUMBER,
    CREE				  NUMBER,
    ETAT				  VARCHAR2(10)		DEFAULT 'INIT' NOT NULL,
    THEO_BEGIN_DATE		  DATE,
    THEO_END_DATE		  DATE,
    REAL_BEGIN_DATE		  DATE,
    REAL_END_DATE		  DATE,    
    CHARGE    			  NUMBER 	    	NOT NULL,
    IMPORTANCE			  VARCHAR2(10)      DEFAULT '0' NOT NULL,
    ARCHIVAGE             NUMBER            DEFAULT '0' NOT NULL,
    MARK       			  VARCHAR2(100),
    LAST_BAT_DATE         TIMESTAMP(6),
    FIRST_DIFFUSION_DATE  TIMESTAMP(6),
    LAST_DIFFUSION_DATE	  TIMESTAMP(6),
    TYPE_REPRISE	      VARCHAR2 (20),
	TYPE_DIFFUSION        VARCHAR2 (20),
	ERROR_DIFFUSION       NUMBER,
    DOCXML 				  XMLTYPE ,
    FK_OPE_ID			  NUMBER NOT NULL
) TABLESPACE AE7QDDA3;
 
ALTER TABLE AE7QTME1
    ADD (CONSTRAINT AE7QCME1
PRIMARY KEY (ID) USING INDEX TABLESPACE AE7QDIX3);
 
ALTER TABLE AE7QTME1
   ADD CONSTRAINT AE7QIOS1 FOREIGN KEY (WORKFLOW_ID)
   REFERENCES AE7QTOS1 (ID)
;
 
ALTER TABLE AE7QTME1
    ADD CONSTRAINT AE7QIME1 FOREIGN KEY (FK_OPE_ID)
    REFERENCES AE7QTOP1 (ID)
;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
CREATE TABLE AE7QTLKC
(
	FK_METHOD_ID NUMBER,
	FK_IMAGE_ID NUMBER
) TABLESPACE AE7QDDA1;
-- clé primaire
ALTER TABLE AE7QTLKC
ADD (CONSTRAINT AE7QCLKC
PRIMARY KEY (FK_METHOD_ID, FK_IMAGE_ID)  USING INDEX TABLESPACE AE7QDIX1);
-- Clé étrangère vers la table des méthodes
ALTER TABLE AE7QTLKC
ADD CONSTRAINT AE7QILKT FOREIGN KEY (FK_METHOD_ID)
REFERENCES AE7QTME1 (ID)
;
-- Clé étrangère vers la table des images
ALTER TABLE AE7QTLKC
ADD CONSTRAINT AE7QILKU FOREIGN KEY (FK_IMAGE_ID)
REFERENCES AE7QTMG1 (ID)
;

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE TABLE AE7QTMG1
(
    ID NUMBER NOT NULL,
    VERSION   NUMBER   DEFAULT '0' NOT NULL,
    CREATION_DATE TIMESTAMP(6),
    NAME VARCHAR2 (10)
) TABLESPACE AE7QDDA1;
 
ALTER TABLE AE7QTMG1
    ADD (CONSTRAINT AE7QCMG1
PRIMARY KEY (ID) USING INDEX TABLESPACE AE7QDIX1);
 
ALTER TABLE AE7QTMG1
	ADD (CONSTRAINT AE7QCMG2 UNIQUE(NAME) USING INDEX TABLESPACE AE7QDIX1);

Je pense que dans ton cas c'est le fichier XML qui est gênant (Mais j'en ai jamais utilisé). Quelle est sa taille ?

Pour éviter de refaire des extracts : tu prend tout et tu enlèves ce que tu veux pas :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT COUNT (*) FROM( SELECT UPPER(extractvalue (VALUE(images),'//@numfich')) FROM AE7QTME1 me1, TABLE ( XMLSEQUENCE ( EXTRACT(me1.docxml, '//Image') ) ) images WHERE me1.ID=29746 MINUS SELECT UPPER(mg1.NAME) FROM AE7QTMG1 mg1, AE7QTLKC lkc WHERE lkc.fk_method_id = 29746 AND mg1.ID = lkc.fk_image_id )
PS : Le MINUS fait un distinct