Contrairement à des index relationnels, les index Oracle Text ne sont généralement pas maintenus en temps réel.
Il faut donc en tenir compte pour une activité transactionnelle.
Même s’il est possible de le faire, cela procure un travail considérable et un souci de fragmentation.
Lorsque l’on effectue des inserts/update, il est nécessaire de synchroniser la table $I.
Un article intéressant : http://www.oracle.com/technetwork/database/enterprise-edition/text-dml-processing-092316.html
1) Activité DML
a) Lors de l’INSERT
Lorsque qu’une ligne est ajoutée dans la table utilisateur, une ligne contenant INDEX_ID et ROWID est ajoutée dans la table CTXSYS.DR$PENDING.
SQL> insert into test_tab values ('ef'); SQL> select * from CTXSYS.DR$PENDING; PND_CID PND_PID PND_ROWID PND_TIMES P ---------- ---------- ------------------ --------- - 1080 0 AAARvbAAFAAAADMAAA 29-MAR-12 N -- valeur précédente 1080 0 AAARvbAAFAAAADMAAB 29-MAR-12 N -- valeur précédente 1080 0 AAARvbAAFAAAADMAAC 30-MAR-12 N -- nouvelle valeur
Rem : si une modification est faite sur la même ligne, la ligne est placée dans CTXSYS.DR$WAITING
SQL> update test_tab set CLOB_COL='fg' where ROWID='AAARvbAAFAAAADMAAC'; SQL> select * from CTXSYS.DR$WAITING ; WTG_CID WTG_ROWID WTG_PID ---------- ------------------ ---------- 1080 AAARvbAAFAAAADMAAC 0 SQL> commit;
En conclusion :
– aucune synchronisation de l’index Oracle n’est effectuée lors du COMMIT qui clôt l’INSERT
– même si la valeur de la colonne indexée est nulle, la ligne est ajoutée dans CTXSYS.DR$PENDING
– si la ligne est déjà en traitement dans CTXSYS.DR$PENDING, celle-ci est ajoutée dans CTXSYS.DR$WAITING
b) Lors d’un DELETE :
Lors du commit qui clôt le DELETE:
– la ligne correspondante avec le ROWID est immédiatement supprimée de DR$<index_name>$K :
– une recherche sur ROWID en utilisant DR$< index_name>$K ne trouve plus la ligne
– une ligne avec ROWID et INDEX_ID est insérée dans CTXSYS.DR$DELETE
– un SELECT dans la session courante ne voit plus la ligne avant le commit
– permet lors du « commit callback » de supprimer les lignes correspondantes de DR$< index_name>$R
– une ligne avec ROWID est insérée dans DR$<index_name>$N
– permet la suppression des DOCID dans la table DR$<index_name>$I lors d’une optimisation
En conclusion :
Un DELETE est immédiatement pris en compte :
– dans la session courante : on ne voit plus la ligne
– dans une autre session : on ne voit plus la ligne au commit
c) Lors d’un UPDATE
Il agit comme un DELETE suivi d’un INSERT.
En conclusion :
– seule la partie DELETE sera visible après le commit
– tout UPDATE sur la colonne entraine la non visibilité du document
d) Lors du COMMIT
Au commit , un « commit callback » est généré :
– pour chaque DOCID de DR$DELETE, une mise à jour de la colonne LOB de DR$< index_name>$R est effectuée pour le ROWID
– la ligne est ensuite effacée de DR$DELETE
2) Mise en place de la synchronisation Oracle TEXT
Lorsque l’on effectue des inserts/update, il est nécessaire de synchroniser la table $I.
Il existe plusieurs possibilités :
1) En manuel via ctx_ddl.sync_index
– utilisation d’une mémoire de 256M avec un parallélisme 2
exec ctx_ddl.sync_index('TEST_IDX','256M',NULL,2);
Rem : ne pas utiliser Alter Index … Rebuild Online Parameters (’sync’)
2) Sinon, il est possible à partir de la 10g de placer les paramètres SYNC EVERY ‘interval’ (via DBMS_SCHEDULER)
SQL> ALTER INDEX test_idx REBUILD ONLINE PARAMETERS (' REPLACE sync (every "sysdate+(1/24)" MEMORY 64M PARALLEL 2)') PARALLEL 2 ;
ou mieux via METADATA car évite le rebuild de l’index
SQL> ALTER INDEX test_idx REBUILDPARAMETERS (' REPLACE METADATA sync (every "sysdate+(1/24)" MEMORY 64M PARALLEL 2)') ; -- création d'un job via DBMS_SCHEDULER SQL> select JOB_ACTION from USER_SCHEDULER_JOBS where job_name='DR$TEST_IDX$J'; JOB_ACTION -------------------------------------------------------------------------------------------------------------------
ctxsys.drvdml.auto_sync_index('TEST_IDX', 67108864, NULL, 2, NULL, 0);
3) Une synchronisation peut avoir lieu au COMMIT
Peut être intéressant pour un commit de plusieurs milliers de lignes
Le COMMIT est envoyé une fois que la synchronisation est effectuée.
SQL>create index test_idx on test_tab (clob_col) indextype is ctxsys.context parameters ('sync (on commit)'); -- ou SQL>ALTER INDEX test_idx REBUILD ONLINE PARAMETERS ('REPLACE METADATA MEMORY 64M sync (on commit)') PARALLEL 2 ;
4) Sinon il est possible d’utiliser le paramètre TRANSACTIONAL
Ce paramètre permet de voir les INSERT/DELETE validées en utilisant les tables DR$PENDING DR$WAITING et en effectuant une indexation à la volée
SQL>ALTER INDEX test_idx REBUILD ONLINE PARAMETERS ('REPLACE METADATA transactional'); SQL> select * from test_tab where CONTAINS(clob_col,'ab') > 0; no rows selected SQL> insert into test_tab values ('ab'); SELECT pnd_index_name, pnd_rowid , TO_CHAR(pnd_timestamp, 'dd-mon-yyyyhh24:mi:ss') timestamp FROM ctx_user_pending; PND_INDEX_NAME PND_ROWID TIMESTAMP ------------------------------ ------------------ ------------------- TEST_IDX AAARvbAAFAAAADPAAA 03-apr-201216:40:11 SQL> select * from test_tab where CONTAINS(clob_col,'ab') > 0; CLOB_COL -------------------------------------------------------------------------------- ab
Il est possible pour des questions de performances de désactiver cette fonctionnalité dans la session par une variable PL/SQL :
SQL> exec ctx_query.disable_transactional_query := TRUE; SQL> select * from test_tab where CONTAINS(clob_col,'ab') > 0; no rows selected Pour la résactiver : SQL> exec ctx_query.disable_transactional_query := FALSE;
Sinon, il est possible de modifier l’index Oracle Text :
SQL> ALTER INDEX test_idx REBUILD ONLINE PARAMETERS ('REPLACE METADATA nontransactional');
2) Gérer la synchronisation
a) Pour optimiser la synchronisation, vous pouvez utiliser :
– le parallélisme en Entreprise Edition
– la clause memory avec une limite à 1 Go en 11gR2
– modification possible de la valeur maximale :
exec ctxsys.ctx_adm.set_parameter('MAX_INDEX_MEMORY','2147483647');
b) la clause transactional a un surcout en performance
– nécessité de lire la table dr$unindexed
c) le mot clé REBUILD METADATA évite la reconstruction de l’index
Dans la troisième partie, nous allons continuer la gestion d’un index Oracle TEXT.
Continuez votre lecture sur le blog :
- Oracle Text pour DBA Oracle : Partie 1 (Capdata team) [Oracle]
- Oracle et SQL Server: La Fragmentation (Benjamin VESAN) [OracleSQL Server]
- Alter table rebuild (Benjamin VESAN) [SQL Server]
- Création d’un Dataguard physique (Benjamin VESAN) [Oracle]
- Modes de récupération et journal de transactions, épisode 2 (David Baffaleuf) [SQL ServerVintage]