Désactiver Index context d'une partition

**attila771** · 07/07/2011, 11h01

Bonjour,

Je dois faire un insert ( 2.5M de lignes) dans une table partitionné. Dans mon insert je précise le nom de la partition.

Ma question est simple, pour que mon insert soit plus rapide, peut-on désactiver les indexes context de cette partition ( et seulement cette partition), faire mon insert puis remettre l'index ?

Merci D'avance

**al1_24** · 07/07/2011, 12h23

Tu as une autre solution :
Faire une une copie de ta partition sur une table temporaire de même structure (y compris les contraintes et index !)
Insérer tes données dans la table temporaire.
Désactiver les contraintes sur la table cible.
Echanger ta partition et la table temporaire (y compris les index).
Réactiver les contraintes sur la table cible.

**attila771** · 07/07/2011, 12h26

Merci bien pour l'aide.

je viens de trouver ça

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Unusable Local Indexes
 
ALTER TABLE  [schema.]table MODIFY PARTITION partition UNUSABLE LOCAL INDEXES
 
Marks the index partition corresponding to the given table partition UNUSABLE. You might mark an index partition unusable before you rebuild the index partition as described in Rebuild Unusable Local Indexes.
 
If the index partition is not marked unusable, the rebuild command returns without actually rebuilding the local index partition.
 
Rebuild Unusable Local Indexes
 
ALTER TABLE  [schema.]table MODIFY PARTITION partition REBUILD UNUSABLE LOCAL
INDEXES

je vais tester et regarder les perf. Si ce n'est pas bon je prendrais ta solution qui est assez simple aussi.

Merci

**Mohamed.Houri** · 07/07/2011, 12h28

Envoyé par attila771

Bonjour,

Je dois faire un insert ( 2.5M de lignes) dans une table partitionné. Dans mon insert je précise le nom de la partition.

Ma question est simple, pour que mon insert soit plus rapide, peut-on désactiver les indexes context de cette partition ( et seulement cette partition), faire mon insert puis remettre l'index ?

Merci D'avance

Si j'ai bien compris votre question, alors oui vous pouvez mettre les indexes locaux (partitionés) dans un statut unusable et les rebuilder par la suite

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
 
mhouri.world > create table TAB1 ( Value number(10))
  2    partition by range(value)
  3    (
  4    partition p1 values less than (10),
  5    partition p2 values less than (20),
  6    partition p3 values less than (30),
  7    partition p4 values less than (40),
  8    partition p5 values less than (50)
  9    );
 
Table created.
 
mhouri.world > insert into tab1 select rownum from dual connect by rownum < 50;
 
49 rows created.
 
mhouri.world > commit;
 
Commit complete.
 
mhouri.world > create index tab1_indx1 on tab1(value) local;
 
Index created.
 
mhouri.world > alter index tab1_indx1 modify partition p1 unusable;
 
Index altered.
 
mhouri.world >select index_name, partition_name,status
  2  from user_ind_partitions
  3  where index_name = 'TAB1_INDX1';
 
INDEX_NAME                     PARTITION_NAME                 STATUS            
------------------------------ ------------------------------ --------          
TAB1_INDX1                     P1                             UNUSABLE          
TAB1_INDX1                     P2                             USABLE            
TAB1_INDX1                     P3                             USABLE            
TAB1_INDX1                     P4                             USABLE            
TAB1_INDX1                     P5                             USABLE            
 
mhouri.world > alter index tab1_indx1 rebuild partition p1
  2  ;
 
Index altered.
 
mhouri.world > select index_name, partition_name,status
  2  from user_ind_partitions
  3  where index_name = 'TAB1_INDX1';
 
INDEX_NAME                     PARTITION_NAME                 STATUS            
------------------------------ ------------------------------ --------          
TAB1_INDX1                     P1                             USABLE            
TAB1_INDX1                     P2                             USABLE            
TAB1_INDX1                     P3                             USABLE            
TAB1_INDX1                     P4                             USABLE            
TAB1_INDX1                     P5                             USABLE

Mohamed Houri

**attila771** · 07/07/2011, 13h31

Désole de réouvrir le sujet.

J'ai un petit problème quand je désactive les index et je lance mon insert

Code :

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

1
2
3
4
5
6
7
ALTER TABLE  MA_TABLE MODIFY PARTITION PARTITION350  UNUSABLE LOCAL INDEXES;
 
 
INSERT INTO MA_TABLE (ID,col1,col2,col3,col4,col5) VALUES (200000221283,200000221283,'product',' Philippines','en',341);
 
 
ALTER TABLE  MA_TABLE MODIFY PARTITION PARTITION350 REBUILD UNUSABLE LOCAL INDEXES;

J'ai cette erreur lors de l'insert

Code :

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

1
2
3
4
5
6
7
8
9
Error report:
SQL Error: ORA-29954: domain index partition is marked LOADING/FAILED/UNUSABLE
29954. 00000 -  "domain index partition is marked LOADING/FAILED/UNUSABLE"
*Cause:    An attempt has been made to access a domain index that is
           being built or is marked failed by an unsuccessful DDL
           or is marked unusable by a DDL operation.
*Action:   Wait if the specified index partition is marked LOADING
           Rebuild the specified index partition if it is marked FAILED
           or UNUSABLE.

**Waldar** · 07/07/2011, 13h38

Quand vous dites que vous faites un insert, vous faites l'insert d'une seule ligne ?

**attila771** · 07/07/2011, 13h40

Pour faire mon test oui mais le vrai insert est une simple jointure qui fait 2.5M d'insert et qui prend beaucoup de temps

Code :

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

1
2
3
4
5
6
7
insert into MA_TABLE
SELECT ID_SEQUENCE.NEXTVAL,a.* from (
SELECT DISTINCT AA.METADATA_GROUP_ID,'blabla' AS NAME, B.RIGHT_TYPE AS VALUE,'en' AS LANGUAGE,'343' AS P#
FROM MA_TABLE1 B
INNER JOIN MA_TABLE2 AA ON AA.RESOURCE_ID=B.RESOURCE_ID
WHERE B.RIGHT_TYPE IS NOT NULL
AND NOT EXISTS ( SELECT 1 FROM AMN_METADATA AM WHERE AM.METADATA_GROUP_ID=AA.METADATA_GROUP_ID AND AM.NAME='blabla' AND AM.P#='343'))A

**Waldar** · 07/07/2011, 14h01

Me voila rassuré, j'ai eu un doute en lisant votre exemple justement.

Avez-vous essayé de faire un insert direct load avec le hint APPEND ?
Cette syntaxe permet de créer de minis-index sur les nouvelles données, ces index étant fusionnés à la fin dans les existants :
http://asktom.oracle.com/pls/asktom/...32800346479086

Envoyé par Tomas Kyte

if you direct path load, you'll maintain indexes - we build mini indexes on the newly loaded data and merge them into the 'real' indexes in bulk. A direct path load of large amounts of data will maintain indexes very efficiently.

Sinon je ne pense pas que ça ait un impact majeur sur votre requête, mais vous pouvez remonter les constantes d'un niveau.
Au final :

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
INSERT /*+ APPEND */ INTO MA_TABLE
With A as
(
SELECT DISTINCT
       AA.METADATA_GROUP_ID
     , B.RIGHT_TYPE AS VALUE
  FROM MA_TABLE1 B
       INNER JOIN MA_TABLE2 AA
         ON AA.RESOURCE_ID = B.RESOURCE_ID
 WHERE B.RIGHT_TYPE IS NOT NULL
   AND NOT EXISTS (SELECT 1
                     FROM AMN_METADATA AM
                    WHERE AM.METADATA_GROUP_ID = AA.METADATA_GROUP_ID
                      AND AM.NAME = 'blabla'
                      AND AM."P#" = '343')
)
SELECT ID_SEQUENCE.NEXTVAL
     , METADATA_GROUP_ID
     , 'blabla'
     , VALUE
     , 'en'
     , '343'
  FROM A;

**Mohamed.Houri** · 07/07/2011, 14h32

Avez-vous essayé de faire un insert direct load avec le hint APPEND ?
Cette syntaxe permet de créer de minis-index sur les nouvelles données, ces index étant fusionnés à la fin dans les existants :
http://asktom.oracle.com/pls/asktom/...32800346479086

Le direct path load est une bonne option sauf que le hint /*+ append */ est silencieusement ignoré par Oracle lorsque la table destinatrice de l'insert
(a) possède un trigger
ou
(b) une foreign key

De plus, le direct path load insert directement au dessus du High water mark. Il n'utilise aucun espace libre disponible. C'est pourquoi lorsque vous avez des deletes reguliers sur votre table le direct path n'est pas recommandé.

Bien à vous

Mohamed Houri

**attila771** · 07/07/2011, 14h33

Merci pour votre réponse mais hélas cela ne fonctionne pas

J'ai voulu faire la méthode de al1_24 mais il s'avère que j'ai 2 index global sur ma table donc au final je fais le mouv de partition mais je me retrouve a refaire quand même 2 index...

Je continue ma recherche !

**Mohamed.Houri** · 07/07/2011, 15h05

Envoyé par attila771

Merci pour votre réponse mais hélas cela ne fonctionne pas

J'ai voulu faire la méthode de al1_24 mais il s'avère que j'ai 2 index global sur ma table donc au final je fais le mouv de partition mais je me retrouve a refaire quand même 2 index...

Je continue ma recherche !

Je résume

(1) Vous avez une table partitionée dans laquelle vous voulez inséré 2,5M de rows
(2) vous connaissez la partition dans laquelle vont aller tous vos inserts
(3) vous avez des indexes locaux partitionés et d'autres globaux
(4) parmi les indexes locaux il y a peut être quelques uns qui assurent une unicité

Je vous conseille de procéder ainsi

(a) disabler tous les indexes locaux(sauf ceux qui assurent une unicité) mais uniquement pour la partition en question

Code :

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

1
2
3
 
ALTER INDEX tab1_indx1 MODIFY partition p1 unusable;
ALTER INDEX tab1_indx2 MODIFY partition p1 unusable;

(b) si le volume (2.5Millions) de records est très largement supérieur au volume existant alors vous pouvez disabler les indexes globaux aussi.

Code :

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

1
2
3
4
5
6
7
8
9
10
 
 
(c) alter session set skip_unusable_indexes=true;
 
(d) insert into table_a partition (p1) select .....
 
(e) commit;
 
(f) ALTER INDEX tab1_indx1 rebuild partition p1;
    ALTER INDEX tab1_indx2 rebuild partition p1;

Vous pouvez aussi utiliser le direct path load (/*+ append */) moyennant les remarques faites dans mon post de tout à l'heure

Bien cordialement

Mohamed Houri

**attila771** · 07/07/2011, 15h20

Oui c'est bien ça Mohamed, a un petit détail j'ai un index local CONTEXT

Je viens de trouver sur le net ce que vous m'indiquer

Code :

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

ALTER session SET skip_unusable_indexes=true;

Le problème c'est que cela ne marche pas avec l'index CONTEXT

En fait j'ai un problème sans en avoir un. J'ai une solution de refaire les index mais au vu du nombre de lignes dans ma table (au total plus de 2Milliard) cela va prendre un peu de temps, si je peux rester au niveau partition cela m'arrangerais

**Mohamed.Houri** · 07/07/2011, 15h43

Envoyé par attila771

Oui c'est bien ça Mohamed, a un petit détail j'ai un index local CONTEXT

Je viens de trouver sur le net ce que vous m'indiquer

Code :

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

ALTER session SET skip_unusable_indexes=true;

Le problème c'est que cela ne marche pas avec l'index CONTEXT

En fait j'ai un problème sans en avoir un. J'ai une solution de refaire les index mais au vu du nombre de lignes dans ma table (au total plus de 2Milliard) cela va prendre un peu de temps, si je peux rester au niveau partition cela m'arrangerais

Désolé,

Je n'ai jamais eu à faire à un index partitioné locallement du type CONTEXT.

Mais puisque vous avez 2 Milliard de records auquels vous voulez ajouter 2.5 millions de records uniquement, le "overhead" des rebuild des indexes va être énorme. Je pense sincèrement qu'il n'est pas conseillé dans ce cas de faire un disable des indexes globaux (tout au moins).

La seule possibilité c'est effectivement de savoir si un index local du type context peut-être localement disablé (aucune idée).

Mais n'oubliez pas, puisque vous avez la partition cible, de l'utiliser dans l'insert

Code :

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

 insert into table tab partition(p1)

Et dans l'insert n'oubliez pas aussi que vous avez la partie select.

Avez vous penser au direct path load?

Mohamed Houri

**attila771** · 07/07/2011, 16h01

Un index context peut être disablé au niveau local mais le problème c'est ce que l'on ne peut pas faire d'insert même si on utilise

Code :

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

ALTER session SET skip_unusable_indexes=true;

on a la même erreur que si on utilise pas cette commande

Code :

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

1
2
3
4
5
6
7
8
9
Error report:
SQL Error: ORA-29954: domain INDEX partition IS marked LOADING/FAILED/UNUSABLE
29954. 00000 -  "domain index partition is marked LOADING/FAILED/UNUSABLE"
*Cause:    An attempt has been made TO access a domain INDEX that IS
           being built OR IS marked failed BY an unsuccessful DDL
           OR IS marked unusable BY a DDL operation.
*Action:   Wait IF the specified INDEX partition IS marked LOADING
           Rebuild the specified INDEX partition IF it IS marked FAILED
           OR UNUSABLE.

Cette commande est donc valable uniquement pour les index "Normaux"

Pour le direct path load je ne connais pas très bien. J'ai utilisé le Append mais sans succes.

Vous pouvez m'en dire plus sur ce sujet ( je vais allez lire de la doc aussi)

Merci

**Mohamed.Houri** · 07/07/2011, 16h29

Envoyé par attila771

Un index context peut être disablé au niveau local mais le problème c'est ce que l'on ne peut pas faire d'insert même si on utilise

Code :

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

ALTER session SET skip_unusable_indexes=true;

on a la même erreur que si on utilise pas cette commande

Code :

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

1
2
3
4
5
6
7
8
9
Error report:
SQL Error: ORA-29954: domain INDEX partition IS marked LOADING/FAILED/UNUSABLE
29954. 00000 -  "domain index partition is marked LOADING/FAILED/UNUSABLE"
*Cause:    An attempt has been made TO access a domain INDEX that IS
           being built OR IS marked failed BY an unsuccessful DDL
           OR IS marked unusable BY a DDL operation.
*Action:   Wait IF the specified INDEX partition IS marked LOADING
           Rebuild the specified INDEX partition IF it IS marked FAILED
           OR UNUSABLE.

Cette commande est donc valable uniquement pour les index "Normaux"

Pour le direct path load je ne connais pas très bien. J'ai utilisé le Append mais sans succes.

Vous pouvez m'en dire plus sur ce sujet ( je vais allez lire de la doc aussi)

Merci

Avant d'envisager le direct path load, il faut d'abord repondre aux questions suivantes

(a) faites vous des deletes sur votre table
(b) existe t-il des triggers sur votre table
(c) existe t-il des Foreign keys sur votre table

Si la réponse est non aux questions précédentes alors vous pouvez utiliser quelque chose comme suit

Code :

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

1
2
3
4
 
 insert /*+ append */ into table t partition(p1)
 select ....
 where ...

Et laissez les indexes en place. Oracle va les maintenir d'une manière efficace (bulk collect).

Par contre si
(a) vous avez des triggers ou des FKs en place sur votre table alors le hint append va être ignoré.
(b) si vous avez des deletes sur votre table l'espace gagné par le delete ne sera pas utilisé lors du direct path car celui-ci insert directement au desssu du High Water mark

Pour votre information, il y a deux ans j'ai amélioré pour un client un insert sur une table partitionée via un select sur une base distance de 3 heures à moins de quelques minutes.

J'ai tracé le insert/select avec le 10046 event que j'ai fait profilé avec le profiler tvd$xtat. La majeur partie de mon temps d'insert était sur un wait event appelé dbfile sequential read (sur la partie select). J'ai résolu ce problème en mettant la table correspondante ainsi que l'index en question en parrallel 4.

Vous devriez tracer l'insert et voir qu'est ce qui consomme le plus de temps.

Bien à vous

Mohamed Houri
Bien à vous

Mohamed Houri

**attila771** · 07/07/2011, 17h08

(a) non surtout pas
(b) oui mais il est désactiver avant mon insert donc non
(c) oui j'ai des FK donc le Append ne va pas être pris en compte

Etant données que je dois donner des résultats de temps demain Je pense que m’orienté vers 2 solutions soit l'insert en masse sans bouger les index ( long mais presque aucune action juste attendre) ou refaire carrément les indexs, mon insert sera plus rapide mais le temps de recalcule devra être pris en compte

Merci de votre aide en tous cas.

Désactiver Index context d'une partition

SQL Oracle

Discussions similaires

Partager

Partager