Indexer une table

[AbouZaid]

Bonjour à tous,

J'ai une requête update sur une table et elle rame quand je l'exécute, donc je souhaiterais faire intervenir les index.

Code :

1
2
3
4
5
6
7
 
UPDATE Table1 tab1
SET col1 = (SELECT DISTINCT tab2.col1 FROM table2 tab2 WHERE tab1.col3 = tab2.col3 AND tab1.col4 = tab2.col4)
SET col2 = (SELECT DISTINCT tab2.col2 FROM table2 tab2 WHERE tab1.col3 = tab2.col3 AND tab1.col4 = tab2.col4)
WHERE tab1.STATUS = 'NEW'
AND nvl(tab1.col6,0) = 1
AND EXISTS (SELECT 'x' FROM table2 tab2 WHERE tab1.col3 = tab2.col3 AND tab1.col4 = tab2.col4)

Vous auriez une idée sur la disposition d'index?

Je souligne que la table1 comporte plus de 2 millions de lignes contre 2000 pour la table 2.

[mnitu]

Déjà faire

Code :

1
2
3
...
SET (col1, col2) = (SELECT col1, col2
                            FROM ...

Utilisez Merge si c'est possible.

[AbouZaid]

Ok je vais le faire.

Et merge me sort cette erreur :

Code :

ORA-30926 : impossible d'obtenir un ensemble de lignes stables dans les tables source

[Yanika_bzh]

Vous n'avez aucun index de défini ?

[AbouZaid]

si j'ai mis un index sur : Status et sur nvl(tab1.col6,0).

Voici ma requête avec le merge :

Code :

1
2
3
4
5
6
7
8
merge INTO tab1
            USING tab2
            ON (ltrim(rtrim(substr(tab1.LIGNE_ME,153,4))) = tab2.TRANRDJE 
                AND  ltrim(rtrim(substr(tab1.LIGNE_ME,157,4))) =  tab2.OPERRDJE)
             when matched then UPDATE 
             SET   (ORIGINE,JOURNAL) = (SELECT DISTINCT CODORIG,CODJOU    FROM tab2 WHERE ltrim(rtrim(substr(tab1.LIGNE_ME,153,4))) = tab2.TRANRDJE AND  ltrim(rtrim(substr(tab1.LIGNE_ME,157,4))) = tab2.OPERRDJE)
WHERE tab1.STATUS = 'NEW'
AND nvl(tab1.col6,0) = 1

[Yanika_bzh]

Je pense qu'un index sur tab1.col3 , tab2.col3 ou tab1.col4 , tab2.col4 (en fonction de la colonne la plus discriminante) peut etre envisagé.
J'ai des doute quand a l'index sur tab1.col6

[mnitu]

Envoyé par AbouZaid

...
Voici ma requête avec le merge :
...

Merging into a Table: Example

[AbouZaid]

Une petite question:

Est ce que le 'MERGE' apporte plus de performance à la requête?

Et comme disposer les index dans une telle requête?

Merci bcp.

[mnitu]

A l’origine vous avez une requête de type

Code :

1
2
3
4
5
6
7
8
 
UPDATE t1
  SET (col1, col2) = (SELECT col1, col2 FROM t2 WHERE t1.cle = t2.cle)
WHERE t1.filtre = valeur
  AND EXISTS (SELECT NULL
                FROM t2
               WHERE t1.cle = t2.cle
             )

Cette requête utilise deux sous-requêtes corrélées pour mettre à jour les valeurs et vérifier que l’enregistrement courant devrait faire partie de l’ensemble des enregistrements à modifier. Si votre condition filtre est très sélective ça va. Sinon vous allez avoir un problème de performance.
Ce problème peut être résolu avec Merge. Normalement les colonnes de jointures sont la clé de la table t1 et normalement un index existe sur ces colonnes.
Dans votre cas les valeurs New et le Nvl indiquent plutôt une faible sélectivité et les indexer ne semble pas à servir à grand chose.

[pacmann]

Salut !

Envoyé par AbouZaid

Code :

AND nvl(tab1.col6,0) = 1

tab1.col6 = 1 suffit, si avec cette condition col6 est null, il ne le prendra pas (pas besoin de lui affecter 0 pour s'apercevoir que 0 est différent de 1)
La question restant... est ce que col6 est très sélective ?

[AbouZaid]

non la colonne n'est pas très sélective...

[pacmann]

Dans ce cas, je te renvoie à la remarque de Yanika : il faut envisager de "partir" de la table tab2 qui a moins de lignes, afin d'éviter le scan des 50 millions de lignes.

Par contre, comme ton lien sur les colonnes de la table1 utilise des fonctions, il faut envisager de créer un index sur les fonctions correspondantes :
- trim(rtrim(substr(tab1.LIGNE_ME,153,4)))
- ltrim(rtrim(substr(tab1.LIGNE_ME,157,4)))

Avec la même question : ces valeurs sont elles sélectives ?

A surveiller également si ces colonnes sont nullables...

[AbouZaid]

Comment je peux partir de la première table alors qu'elle est la principale?

[pacmann]

En fait, pour savoir ce qu'il faut mettre à jour, on commence par faire la jointure... donc on peut sans aucun doute commencer par la petite table.

Un exemple : je crée une grosse table qu'il faut mettre à jour, avec les valeurs d'une petite table :

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
 
SQL> CREATE TABLE testmergeuh AS
  2  SELECT level id, 0 val
  3  FROM DUAL
  4  CONNECT BY level <= 1000000;
 
TABLE crÚÚe.
 
EcoulÚ : 00 :00 :02.17
SQL> CREATE INDEX testmergeuhii ON testmergeuh(id) COMPUTE STATISTICS;
 
INDEX crÚÚ.
 
EcoulÚ : 00 :00 :02.10
SQL> exec dbms_stats.gather_table_stats(NULL, 'TESTMERGEUH')
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
EcoulÚ : 00 :00 :06.42
SQL> CREATE TABLE testtab2 AS
  2  SELECT level heidi, level val
  3  FROM DUAL
  4  CONNECT BY level <= 10;
 
TABLE crÚÚe.
 
EcoulÚ : 00 :00 :00.13
SQL> exec dbms_stats.gather_table_stats(NULL, 'TESTTAB2')
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
EcoulÚ : 00 :00 :00.32

Et l'explain du MERGE :

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
 
SQL> EXPLAIN PLAN FOR
  2  MERGE INTO testmergeuh a
  3  USING testtab2 b
  4  ON (a.id = b.heidi)
  5  WHEN MATCHED THEN
  6  UPDATE SET a.val = b.val;
 
ExplicitÚ.
 
EcoulÚ : 00 :00 :00.02
SQL> SELECT * FROM TABLE(dbms_xplan.display)
  2  /
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1765098833
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT               |               |     1 |    15 |    23   (0)| 00:00:01 |
|   1 |  MERGE                        | TESTMERGEUH   |       |       |            |          |
|   2 |   VIEW                        |               |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| TESTMERGEUH   |     1 |     6 |     2   (0)| 00:00:01 |
|   4 |     NESTED LOOPS              |               |     1 |    12 |    23   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL        | TESTTAB2      |    10 |    60 |     3   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN         | TESTMERGEUHII |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   6 - access("A"."ID"="B"."HEIDI")
 
18 ligne(s) sÚlectionnÚe(s).

(Le full scan est sur la petite table de 10 lignes, puis on accède à la grosse via l'index, puis on met à jour les lignes qui matchent)

[AbouZaid]

Merci bcp pour ces informations.

Je vais analyser ma requête et revenir vers vous.