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