SQL*Loader Direct et Index

A moins de travailler avec des tables vides et d’assurer que les modifications ne sont pas journalisées via l’attribut NOLOGGING ou l’option UNRECOVERABLE, combiner index et chargement SQL*Loader en mode direct est en général une assez mauvaise idée… C’est, au moins, ce que suggère la documentation Oracle à plusieurs endroits à travers des petites phrases comme celles-ci :

  • [ici] « Avoiding index maintenance is quite reasonable when the number of rows to be loaded is large compared to the size of the table. But if relatively few rows are added to a large table, then the time required to resort the indexes may be excessive. In such cases, it is usually better to use the conventional path load method […] »
  • [ici] « A record that violates a UNIQUE constraint is not rejected (the record is not available in memory when the constraint violation is detected) »
  • [ici] « SQL*Loader leaves indexes in an Index Unusable state when […]« 

Cet article présente plusieurs cas de mise en œuvre d’index avec SQL*Loader en mode direct qui donnent des résultats relativement mitigés… Souvenez-vous en avant d’utiliser ce mode.

Erreurs de chargements

Le risque le plus important -quoique-, lié au fait d’utiliser des index avec des chargement en mode direct, est de laisser les index inutilisables (UNUSABLE) :

  • Dans le meilleur des cas, les temps de réponse se dégradent significativement. 
  • Dans le pire, lorsqu’il s’agit d’un index unique, la contrainte ne peut plus être vérifiée et les chargements consécutifs échouent.

Exemple 1 : crash d’un processus

La liste des cas d’erreurs qui rendent un index inutilisable pendant un chargement en mode direct est disponible dans Oracle® Database Utilities 11g Release 2 (11.2) – Indexes Left in an Unusable State. Nous allons créer un exemple qui montre à quel point il est facile d’obtenir un index invalide :

Dans ce premier exemple, nous allons créer une table X avec un index sur une de ses colonnes :

sqlplus demo/demo

create table X(
id number,
col2 number) logging;

create index X_IX on X(col2) logging;

Nous allons ensuite créer un fichier de contrôle et un fichier de données :

cat /home/oracle/x.ctl
load data
infile '/home/oracle/x.dat'
append
into table X
fields terminated by "," optionally enclosed by '"'
( id, col2 )

for i in `seq 10000`; do
echo "$i,1" >>/home/oracle/x.dat
done

Pour rendre l’index invalide, il suffit par exemple de lancer dans un terminal la commande ci-dessous :

for i in `seq 20`; do
sqlldr userid=demo/demo
   control=/home/oracle/x.ctl
   log=/home/oracle/x.log
   direct=true
done

Alors que dans un autre, vous lancerez celle-ci :

ps -ef |grep sq[l]ldr |awk '{print "kill -9",$2}'|sh

Le résultat est un index invalide comme le montre la requête ci-dessous :

select status 
from user_indexes
where index_name='X_IX';

STATUS
--------
UNUSABLE

Exemple 2: Insertion de doublons

Dans le cas où un index est unique, le rendre invalide est encore plus simple en mode direct, il suffit d’y insérer une valeur en double ; pour cet exemple, nous allons modifier le schéma précédent :

sqlplus demo/demo 

truncate table x;
drop index x_ix;

create unique index x_uq on x(id);

Exécutez ensuite 2x la commande ci-dessous :

sqlldr userid=demo/demo 
   control=/home/oracle/x.ctl
   log=/home/oracle/x.log
   direct=true

Le fichier log de la 2nd exécution montre :

The following index(es) on table X were processed:
index DEMO.X_UQ was made unusable due to:
ORA-00001: unique constraint (DEMO.X_UQ) violated

Pourtant les données sont, malgré tout chargées dans la table :

Table X:
10000 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Si vous exécutez encore le chargement, ça devient n’importe quoi puisque une partie des données est chargée mais le fait que l’index est invalide interdit le reste du chargement :

ORA-26026: unique index DEMO.X_UQ initially in unusable state
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Load completed - logical record count 5000.

Et évidemment, impossible de recréer l’index simplement ; il vous faudra passer par un grand nettoyage :

sqlplus demo/demo

alter index x_uq rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Ressources nécessaires au chargement

En outre, même quand les opérations sont en mode NOLOGGING, maintenir un index depuis le loader peut coûter plus cher que de l’alimenter en mode conventionnel surtout si le volume des données modifiées représente une petite partie de la taille de la table. Pour vous donner une idée, recréer la table de l’exemple précédent :

sqlplus demo/demo
drop table x purge;

create table X(
id number,
col2 number) nologging;

create index X_IX on X(col2) nologging;

Exécutez un millier de chargements préalables pour que la taille de l’index devienne important. Vous pourrez alors comparer les temps de chargement en mode direct et en mode conventionnel. Vous découvrirez, sans parler des contraintes sur le verrouillage par exemple, que les meilleurs temps de réponse ne sont pas forcément pour le scénario direct ; voici 2 extraits assez collectés sur mon serveur Linux 32bit avec une base 11.2.0.3 :

  • en mode direct
time sqlldr userid=demo/demo 
   control=/home/oracle/x.ctl
   log=/home/oracle/x.log
   direct=true

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Apr 29 11:24:39 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 10000.

real    0m0.304s
user    0m0.024s
sys    0m0.028s
  • en mode conventionnel avec quelques optimisations sur les tailles de buffers et tableaux
time sqlldr userid=demo/demo 
  control=/home/oracle/x.ctl
   log=/home/oracle/x.log
direct=false rows=10000 bindsize=10000000

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Apr 29 11:24:32 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

specified value for readsize(1048576) less than bindsize(10000000)
Commit point reached - logical record count 10000

real    0m0.136s
user    0m0.052s
sys    0m0.016s

Et ce n’est rien si vous comparez à une base de données pour laquelle la journalisation est activée. Evidemment, si vous supprimer les index…

Note:
Pour certains cas d’utilisation, vous pouvez utiliser l’option SINGLEROW pour alimenter une table en mode direct tout en maintenant l’index comme pour un chargement conventionnel.

Conclusion

Le mode DIRECT de SQL*Loader est une optimisation possible pour réduire les temps de chargement. Avant de l’activer, pensez-y bien :

  • Avez-vous vraiment besoin d’accélérer les temps de chargement ? de combien ?
  • Quelle est l’activité transactionnelle sur la table ?
  • Pouvez-vous réellement réaliser des opérations en mode NOLOGGING sur la table ?
  • La table est-elle partitionnée ?  Quel est le pourcentage de données que vous chargez à nouveau dans la table ?
  • Quels sont les index sur la table ? Pouvez-vous les désactiver le temps du chargement et les reconstruire ensuite ? Quel impact si vos index deviennent invalides ?

Et dans tous les cas, ne jouez pas à pile ou face, faites des tests et valider l’intérêt du mode direct. Dans le doute, restez en mode conventionnel…