MDATA Section und MULTI_COLUMN_DATASTORE

Ab Oracle Database 10g gibt es ein neues „Section“ Feature - die neue MDATA Section, um Dokument-Metadaten separat zu handhaben. Die MDATA Section ist vergleichbar mit einer Zone- oder Field- Section, d.h. das Dokument muss eine interne Struktur („Section“) wie HTML oder XML besitzen. Ein Beispiel dazu findet sich in folgendem Blog. Der MULTI_COLUMN_DATASTORE (siehe dazu auch Blog) führt die Spalten einer Tabellen zu einem Dokument zusammen und trennt die Informationen durch XML Tags.
Bringt man nun diese beiden Techniken zusammen, so lassen sich Spalten im MULTI_COLUMN_DATASTORE zusammenführen und danach als MDATA Metadaten Sections kennzeichen und nutzen.

Für das folgenden Beispiel verwenden wir die Tabelle PRODUCTS im Schema SH. Zu beachten ist, um das Paket CTX_DDL nutzen zu können, benötigt man die Rolle CTXAPP (oder ein explizites EXECUTE-Privileg).
Zuerst legen wir den MULTI_COLUMN_DATASTORE mit folgenden Statements an. Die Spalten "PROD_NAME", "PROD_STATUS", "PROD_LIST_PRICE" und "PROD_DESC" werden für den MULTI_COLUMN_DATASTORE konfiguriert:

connect sh/sh
execute ctx_ddl.drop_preference (preference_name =>'MY_MULTI_PREF');
begin 
  ctx_ddl.create_preference(preference_name =>'MY_MULTI_PREF',
                            object_name => 'multi_column_datastore' );
  ctx_ddl.set_attribute(preference_name => 'MY_MULTI_PREF',
           attribute_name  => 'COLUMNS',
           attribute_value => 'PROD_NAME, PROD_STATUS, PROD_LIST_PRICE, PROD_DESC');
end;
/
sho err

Im nächsten Schritte werden die MDATA Sections "PROD_NAME", "PROD_STATUS", "PROD_LIST_PRICE" mit ADD_MDATA_SECTION konfiguriert. Die Section "PROD_DESC" wird als eine FIELD-Section angelegt. Zusätzlich zu den existierenden Sections erzeugen wir eine neue Section "flag", die neue Informationen, die nicht in der Tabelle enthalten sind, aufnehmen kann. Eine typische Verwendung wäre zum Beispiel eine zusätzliche Kennzeichnung der Daten durch spezielle Zugriffsrechte.

execute ctx_ddl.drop_section_group('my_seg');

execute ctx_ddl.create_section_group(group_name=>'my_seg',   
                                     group_type=>'basic_section_group');
execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'PROD_NAME', 
                                  tag=>'prod_name');
execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'PROD_STATUS', 
                                  tag=>'prod_status');
execute ctx_ddl.add_mdata_section(group_name=>'my_seg',  
                                  section_name=>'PROD_LIST_PRICE', 
                                  tag=>'prod_list_price');
execute ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'PROD_DESC', 
                                  tag=>'prod_desc', visible=>true);
execute ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'flag', 
                                  tag=>'flag');

sho err

Nun kann der Index mit dem DATASTORE my_multi_pref und der SECTION GROUP my_seg erzeugt werden. Die Synchronisierung soll dabei automatisch nach jedem COMMIT erfolgen.

DROP INDEX mdata_index;

CREATE INDEX mdata_index ON products(prod_desc)
indextype IS ctxsys.context 
parameters ('DATASTORE my_multi_pref SECTION GROUP my_seg sync (on commit)');

SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'MDATA_INDEX';
no rows selected

Folgende Abfrageart mit dem MDATA-Operator ist nun möglich.

SELECT prod_id, prod_list_price, prod_desc FROM products 
WHERE contains (prod_desc, 'Card AND MDATA(prod_list_price, 69.99)') > 0;

   PROD_ID PROD_LIST_PRICE
---------- ---------------
PROD_DESC
--------------------------------------------------------------------------------
       138           69.99
256MB Memory Card

Die Tokentypes größer gleich 400 in der $I-Tabelle zeigen an, dass MDATA-Sectionen vorhanden sind, da MDATA Tokens mit Tokentype zwischen 400 und 499 gekennzeichnet sind. Gibt es zusätzlich negative Werte (kleiner gleich 400), die sogenannten "Delta-Zeilen", ist der Index nicht optimiert. Dies wird nun mit folgender Abfrage verifiziert:

SELECT token_type, count(*) 
FROM dr$mdata_index$i 
WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;
TOKEN_TYPE   COUNT(*)
---------- ----------
       400         71
       402         42
       401          1

Die 3 Zeilen zeigen, dass 3 verschiedene MDATA Tokentypes mit 400, 401 und 402 existieren. Diese stehen für die Sections "PROD_NAME", "PROD_STATUS" und "PROD_LIST_PRICE".
Nun werden manuell Werte mit der Prozedur ADD_MDATA in die "flag" Metadaten-Section eingefügt. Dazu erzeugen wir mit folgendem Skript die entsprechenden Aufrufe, um "flag"-Einträge mit Wert "J" bzw. "N" je nach Größe des PROD_LIST_PRICE zu generieren.

spool liste.lst
SELECT 
'execute ctx_ddl.add_mdata'||'(''MDATA_INDEX'''||','||'''flag'''||','||'''N'''||','||''''||rowid||''''||')'||';' 
FROM products WHERE prod_list_price<=70;
SELECT 'execute ctx_ddl.add_mdata'||'(''MDATA_INDEX'''||','||'''flag'''||','||'''J'''||','||''''||rowid||''''||')'||';'
FROM products WHERE prod_list_price>70;
COMMIT;
spool off

-- start des ablaufbaren Skripts mit
start liste.lst
execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAG');
execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAJ');
execute ctx_ddl.add_mdata('MDATA_INDEX','flag','N','AAAew2AAEAADLwUAAK');
...

ADD_MDATA ist transaktional und muss mit COMMIT oder ROLLBACK abgeschlossen werden. Danach ist die "flag" Section in Abfragen wie folgt nutzbar:

SELECT prod_id, prod_list_price, prod_desc 
FROM products WHERE contains (prod_desc, 
'Card AND MDATA(prod_list_price, 69.99) AND MDATA(flag,N)') > 0;
   PROD_ID PROD_LIST_PRICE
---------- ---------------
PROD_DESC
--------------------------------------------------------------------------------
       138           69.99
256MB Memory Card


SELECT prod_id, prod_list_price, prod_desc 
FROM products WHERE contains (prod_desc, 
'Card AND MDATA(prod_list_price, 69.99) AND MDATA(flag,J)') > 0;

no rows selected

Der Index ist allerdings noch nicht optimiert, wie die Überprüfung der $I Tabelle zeigt:

SELECT token_type, count(*)
FROM dr$mdata_index$i 
WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;

TOKEN_TYPE   COUNT(*)
---------- ----------
       400         71
       403          2
       402         42
      -403         70
       401          1

Tokenweise Optimierung ist mit folgender speziellen OPTIMIZE_INDEX Prozedur möglich:

execute ctx_ddl.optimize_index(idx_name=> 'MDATA_INDEX',
              optlevel=> ctx_ddl.optlevel_token_type, 
              token_type=> ctx_report.token_type('MDATA_INDEX', 'mdata flag')); 

Der Index liegt nun optimiert vor. Das Ergebnis in der Token-Tabelle sieht nun folgendermassen aus:

SELECT token_type, count(*)
FROM dr$mdata_index$i 
WHERE token_type<= -400 OR token_type>=400 GROUP BY token_type;

TOKEN_TYPE   COUNT(*)
---------- ----------
       400         71
       403          2
       402         42
       401          1

Zusätzlich zu den MDATA Sections gibt es in 11g SDATA Sections, die einige der Einschränkungen von MDATA aufheben. Darüberhinaus eröffnet der Composite Domain Index in 11g ganz neue Möglichkeiten, um die Problematik der Mixed Queries zu lösen. Mehr dazu in einer der nächsten Postings...