Nous allons explorer Oracle Text et son son administration en tant que DBA.
La première partie concerne l’installation et l’implémentation.
Oracle est une base de données relationnelles, les données sont donc gérées en deux dimensions sous forme de table.
Certains types de données (géographique, audio, vidéo et texte) sont difficiles voire impossibles à normaliser.
Historiquement des solutions non relationelles existaient avec Adabas ou Objectivity DB par exemple.
Oracle a amélioré sa gestion des données non relationnelles avec :
– Oracle Spatial : pour les données géographiques
– Oracle Intermedia : pour les données audio-vidéos
– Oracle Text : pour les données textes
Ces fonctionnalités reposent essentiellement sur des colonnes de type LOB pour stocker les données et un ensemble de procédures pour les gérer.
Oracle Text est utilisable dans toutes les distibutions d’Oracle (SE, SE One, PE, EE) et ignorer son existence est une erreur.
Elle permet d’effectuer des recherches et de générer des rapports sur une variété de format (html, xml, pdf, word..).
Son interface est ouverte et peut être utilisée par des logiciels tiers.
Sa compréhension est indispensable à tout DBA Oracle.
1) Vérification de l’installation d’ Oracle Text
– Vérifier la présence du schéma ctxsys (mot de passe par défaut ctxsys).
Attention le compte peut être verrouillé (et doit le rester en production)
SELECT * FROM ctxsys.ctx_version;
-Son installation est faite en standard.
Néanmoins si l’option n’a pas été installée vous pouvez utiliser la démarche suivante :
conn SYS/syspasswd AS SYSDBA @?/ctx/admin/catctx.sql mypasswd SYSAUX TEMP NOLOCK
–pour le langage de préférence par défaut drdefxx.sql (English(us),French(f),..)
conn CTXSYS/mypasswd @?/ctx/admin/defaults/drdefus.sql
– Par défaut le shéma se trouve sur le tablespace SYSAUX
select space_used_kbytes from v$sysaux_occupants where occupant_name='TEXT';
Il est possible de modifier le tablespace, par exemple vers le tablespace TBS_CONTEXT :
execute ctxsys.dri_move_ctxsys(tbs_name=>'TBS_CONTEXT');
2) Création d’un index Oracle Text simple
create tablespace TBS_TEXT datafile '+DATA' size 10M autoextend on maxsize 200M
extent management local uniform size 64K;
CREATE USER user_text IDENTIFIED BY text DEFAULT TABLESPACE TBS_TEXT
TEMPORARY TABLESPACE temp PROFILE default QUOTA UNLIMITED ON TBS_TEXT;
— droits nécessaires
GRANT EXECUTE ON ctxsys.ctx_ddl TO user_text; GRANT CTXAPP TO user_text; GRANT CREATE PROCEDURE TO user_text; GRANT CREATE SESSION TO user_text; GRANT CREATE TABLE TO user_text; GRANT SELECT ANY DICTIONARY TO user_text; GRANT CREATE JOB to user_text; GRANT CREATE TRIGGER to user_text;
— jeu de test
CONNECT user_text/text
create table test_tab (clob_col clob);
create index test_idx on test_tab (clob_col) indextype is ctxsys.context;
commit;
insert into test_tab values ('c a b d');
insert into test_tab values ('ab');
commit;— requête de test de filtre
select * from test_tab where CONTAINS(clob_col,'ab') > 0;
3) Structure interne
Un index Oracle Text utilise l’environnement “Extensibility Framework” appelé parfois cartouche.
L’objectif est de donner aux dévéloppeurs la possibilité de créer leurs propres types d’index.
Un index doit ramener un ensemble de ROWID, il est nécessaire de gérer la mise à jour de l’index et toutes les opérations associées.
La création d’un index Oracle Text repose sur des objets Oracle et sur des routines de mises à jour contenues dans le noyau.
Regardons ce que la création d’un index de domaine simple a crée :
Au niveau table :
select table_name,IOT_TYPE, tablespace_name from USER_TABLES ; TABLE_NAME IOT_TYPE TABLESPACE_NAME ------------------------------ ------------ ------------------------------ DR$TEST_IDX$K IOT DR$TEST_IDX$N IOT DR$TEST_IDX$R TBS_TEXT DR$TEST_IDX$I TBS_TEXT TEST_TAB TBS_TEXT
– 4 tables ont eté crées, elles se nomment DR<index_text_name><K,N,R,I>
– 2 tables sont des IOTs
Au niveau des index :
select index_name,table_name, tablespace_name,INDEX_TYPE from user_indexes
where INDEX_TYPE in ('DOMAIN','NORMAL','IOT - TOP')
INDEX_NAME TABLE_NAME TABLESPACE_NAME INDEX_TYPE
------------------------------ ------------------------------ ------------------------------ ----------
TEST_IDX TEST_TAB DOMAIN
SYS_IOT_TOP_72679 DR$TEST_IDX$N TBS_TEXT IOT - TOP
SYS_IOT_TOP_72674 DR$TEST_IDX$K TBS_TEXT IOT - TOP
DR$TEST_IDX$X DR$TEST_IDX$I TBS_TEXT NORMALNous avons la structure des 2 IOTS <SYS_IOT_TOP_>.. et un index DR$TEST_IDX$X sur la table DR$TEST_IDX$I
Au niveau des colonnes LOB :
SELECT table_name "Table", column_name "Column", segment_name "Segment" , index_name "Index" FROM user_lobs WHERE table_name like 'DR$%'; Table Column Segment Index ------------------------------ ------------ ------------------------------ --------------------- DR$TEST_IDX$I TOKEN_INFO SYS_LOB0000072671C00006$ SYS_IL0000072671C00006$ DR$TEST_IDX$R DATA SYS_LOB0000072676C00002$ SYS_IL0000072676C00002$
Nous avons 2 colonnes de types LOB sur TOKEN_INFO et DATA sur les tables DR$TEST_IDX$I et DR$TEST_IDX$R
Conclusion :
Pour le DBA, la création d’un index Oracle Text entraine la création d’un ensemble d’objets :
– D<index_name>$I : Token table
– table des mots ‘Token’
– liste des mots avec position dans le document
– contient une colonne LOB token_info (représentation binaire des documents les contenant)
– chaque document est représenté par la valeur DOCID
– DR<index_name>$X :
– index B-tree sur la table $I, toutes les colonnes sauf la colonne lob
– DR<index_name>$N : Negative Row Table
– contient les DOCID supprimés
– supprimé par le processus d’optimisation
– DR<index_name>$K : Table de mapping ROWID -> DOCID
– Table IOT , recherche de DOCID à partir d’un rowid
– DR<index_name>$R : Table de mapping DOCID -> ROWID
– Table IOT , recherche de rowid à partir d’un DOCID
– DR<index_name>$P : Optionnel ,IOT pour recherche rapide sur liste de mots (wordlist preference)
– DR<index_name>$R : Optionnel,si utilisation de paramètres FILTER BY ou ORDER BY
La deuxième partie concerne le comportement d’un index Oracle TEXT avec du DML
Continuez votre lecture sur le blog :
- Oracle Text pour DBA Oracle : Partie 2 (Capdata team) [Oracle]
- Oracle 19c : Les partitions hybrides (Emmanuel RAMI) [Oracle]
- Oracle 23c : se protéger des attaques par injections SQL grâce à SQL Firewall (Emmanuel RAMI) [Oracle]
- Le chiffrement Oracle : Transparent Data Encryption sur Oracle 19c (Emmanuel RAMI) [Oracle]
- Oracle et SQL Server: Les Statistiques (Benjamin VESAN) [OracleSQL Server]