Posts mit dem Label ADD_MDATA_SECTION werden angezeigt. Alle Posts anzeigen
Posts mit dem Label ADD_MDATA_SECTION werden angezeigt. Alle Posts anzeigen

Freitag, 15. Mai 2009

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...

Dienstag, 12. August 2008

Metadaten-Suche mit MDATA Section

Eine Mixed Query kombiniert eine relationale Abfrage mit einer Volltextrecherche und kann zusätzlich noch sortierte Ausgaben erfordern. Ein typisches Beispiel ist eine kombinierte Text- und eine Datumsabfrage im WHERE-Filter, oder einfach nur eine Textabfrage in Kombination mit einer weiteren relationalen Abfrage. Dies kann u.U. zu Performance-Einbussen führen, besonders wenn weder der Textanteil noch der strukturelle Anteil sehr selektiv sind.
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. Bei MDATA wird der Metadaten-Anteil separat als strukturierter Anteil gespeichert und ist für den Textindex unsichtbar. Allerdings gibt es Unterschiede zur Field Section Suche wie z.B. MDATA kann transaktionell verändert werden, ohne den Rest des Index zu beeinträchtigen.
Folgendes Beispiel veranschaulicht die Nutzung. Nehmen wir als Ausgangstabelle die PRODUCTS Tabelle aus dem Schema SH und erzeugen folgende Tabellenstruktur mit XMLTYPE Spalte:

CREATE TABLE mdata_doc (prod_id NUMBER, xml_lob XMLTYPE);

INSERT INTO mdata_doc select prod_id,
xmlelement("Infos",
xmlelement( "Monat",extract(MONTH from prod_eff_from + seq.nextval)),
xmlelement("Prod_name", PROD_NAME),
xmlelement("Prod_kategorie", PROD_CATEGORY),
xmlelement("Status", PROD_STATUS),
xmlelement("Prod_list_preis", PROD_LIST_PRICE))
FROM products;

Das Ergebnis sieht dann folgendermassen aus:
SQL> SELECT * FROM mdata_doc WHERE rownum=1;
PROD_ID
----------
XML_LOB
-------------------------------------------------------------------------------- 
45
<infos>
<monat>1<monat>
<prod_name>O/S Documentation Set - Kanji</prod_name>
<prod_kategorie>Software/Other</prod_kategorie> <status>STATUS<status> <prod_list_preis>44.99<prod_list_preis> <infos>

Danach werden die Section, die Section Gruppe und die Fields erzeugt.

EXECUTE ctx_ddl.drop_section_group('my_seg');
EXECUTE ctx_ddl.create_section_group(group_name=>'my_seg',group_type=>'xml_section_group');

BEGIN
ctx_ddl.add_field_section(group_name=>'my_seg',section_name=>'Infos', tag=>'Infos');
ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'Prod_name', tag=>'Prod_name', visible=>TRUE);
ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'Prod_kategorie', tag=>'Prod_kategorie', visible=>TRUE);
ctx_ddl.add_field_section(group_name=>'my_seg', section_name=>'Prod_list_preis', tag=>'Prod_list_preis', visible=>TRUE);
END;
/

Nun fügen wir die MDATA ´Metadaten Sections "Status" und "Monat" mit ADD_MDATA_SECTION hinzu.

EXECUTE ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'Status', tag=>'Status');
EXECUTE ctx_ddl.add_mdata_section(group_name=>'my_seg', section_name=>'Monat', tag=>'Monat');

Nun legen wir den Text-Index an: dabei sind 4 Spalten "normal" Text indiziert und die Felder Status und Monat sind MDATA Section Group indiziert.

CREATE INDEX txt_index ON mdata_doc (xml_lob)
INDEXTYPE IS ctxsys.context
PARAMETERS ('SECTION GROUP my_seg');

SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'TXT_INDEX';

Nach dem Indizieren suchen wir nach einem Produkt in der Kategorie "photo" im Monat "2". Dies kann nun vollständig über den Textindex gelöst werden. Die MDATA Suche ist dabei allerdings nur auf Gleichheit möglich.

SELECT count(*) FROM mdata_doc
WHERE contains (xml_lob, 'photo within Prod_kategorie and (mdata(monat,2) 
and mdata(status,STATUS)) ') > 0;

Nun können wir folgenden PL/SQL Block nutzen, um die Metadaten zu ändern. In unserem Fall wird der Wert "STATUS" der Section "Status" in "verfuegbar" geändert. Dabei verwenden wir die Prozeduren CTX_DDL.REMOVE_MDATA und CTX_DDL.ADD_MDATA.

BEGIN
FOR c1 IF (SELECT rowid FROM mdata_doc 
WHERE contains (xml_lob, 'mdata(Status,STATUS)') > 0)
LOOP
ctx_ddl.remove_mdata(idx_name=>'TXT_INDEX', section_name=>'Status', mdata_value=>'STATUS', mdata_rowid=>c1.rowid);
ctx_ddl.add_mdata(idx_name=>'TXT_INDEX', section_name=>'Status', mdata_value=>'verfuegbar', mdata_rowid=>c1.rowid);
END LOOP;
END;
/
COMMIT;

Da wir die Metatdaten geändert haben, muss die Abfrage nun folgendermassen lauten, um das gleiche Ergebnis wie vorhin zu liefern:

SELECT count(*)
FROM mdata_doc
WHERE contains (xml_lob, 'photo within Prod_kategorie and mdata(monat,2) 
and mdata(Status,verfuegbar)') > 0

Weitere Neuerungen und Features zum Thema Mixed Queries und Oracle Text gibt es in der aktuelle Oracle Datenbank Version 11g. Mehr dazu in einer der nächsten Postings...

Beliebte Postings