prog:oracle_text_multiple_column_user_datastore
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende ÜberarbeitungNächste ÜberarbeitungBeide Seiten der Revision | ||
prog:oracle_text_multiple_column_user_datastore [2017/04/22 13:22] – gpipperr | prog:oracle_text_multiple_column_user_datastore [2023/10/23 15:20] – [Mit Oracle Text mehrere Spalten einer oder mehrerer Tabellen indizieren] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | ===== Mit Oracle Text mehrere Spalten einer oder mehrerer Tabellen indizieren ===== | ||
+ | ** Schon ewig, min seit 8i , getestet mit Oracle 12c R1 in 2017, Review für Oracle 19c in 10.2023** | ||
+ | |||
+ | |||
+ | Mehr über Oracle Text, siehe auch hier => [[dba: | ||
+ | |||
+ | |||
+ | **Ziel**: Einen Anwender soll in einer Suchmaske über mehrere Datenbank Felder in der Anwendung hinweg suchen können. | ||
+ | |||
+ | Je nach dem wo die Daten in der DB verteilt liegen, können die folgenden Store Eigenschaften verwendet werden | ||
+ | |||
+ | |||
+ | Mit der " | ||
+ | |||
+ | * **MULTI_COLUMN_DATASTORE** => Alle Spalten liegen in gleichen Tabelle | ||
+ | * **DETAIL_DATASTORE** | ||
+ | * **USER_DATASTORE** Text liegt in der Datenbank in verschiedenen Tabellen und Spalten und soll besonders vor der Indizierung bearbeitet werden | ||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Text Spalten alle in der selben Tabelle ==== | ||
+ | |||
+ | |||
+ | Am einfachsten ist es, wenn alle Textspalten in der selben Tabelle liegen. | ||
+ | |||
+ | |||
+ | Tabelle: | ||
+ | |||
+ | <code sql> | ||
+ | drop table kunden; | ||
+ | |||
+ | create table kunden ( | ||
+ | kd_id number(11) not null | ||
+ | , kd_name varchar2(256) | ||
+ | , anschrift | ||
+ | , bemerkung varchar2(256) | ||
+ | , CONSTRAINT kunden_pk PRIMARY KEY (kd_id) | ||
+ | ); | ||
+ | |||
+ | insert into kunden values (1,' | ||
+ | insert into kunden values (2,' | ||
+ | insert into kunden values (3,' | ||
+ | insert into kunden values (4,' | ||
+ | insert into kunden values (5,' | ||
+ | commit; | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | " | ||
+ | |||
+ | <code sql> | ||
+ | exec ctx_ddl.drop_preference | ||
+ | exec ctx_ddl.create_preference( ' | ||
+ | exec ctx_ddl.set_attribute | ||
+ | exec ctx_ddl.set_attribute | ||
+ | |||
+ | exec ctx_ddl.drop_section_group( | ||
+ | exec ctx_ddl.create_section_group( | ||
+ | |||
+ | </ | ||
+ | |||
+ | Index anlegen | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | drop index idx_kunden_ctx; | ||
+ | |||
+ | create index idx_kunden_ctx on kunden( bemerkung ) | ||
+ | indextype is ctxsys.context | ||
+ | filter by kd_id | ||
+ | parameters( ' | ||
+ | | ||
+ | / | ||
+ | |||
+ | --Check for any filtering errors | ||
+ | select * from ctx_user_index_errors; | ||
+ | </ | ||
+ | |||
+ | |||
+ | Suchen: | ||
+ | |||
+ | <code sql> | ||
+ | select kd_id from kunden where contains(bemerkung,' | ||
+ | |||
+ | kd_id | ||
+ | ------ | ||
+ | 1 | ||
+ | 4 | ||
+ | </ | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | select * from kunden where contains(bemerkung,' | ||
+ | |||
+ | kd_id | ||
+ | ------ | ||
+ | 4 | ||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | ==== Über mehrere Tabellen hinweg die Daten zusammenfassen==== | ||
+ | |||
+ | |||
+ | Die Schwierigkeit dabei sind: | ||
+ | |||
+ | * Pro Tabelle ein anderer PK - welcher PK soll von allen diesen Tabellen indiziert werden? | ||
+ | * Jede Tabelle kann sich getrennt von einer anderen Tabelle ändern - d.h. bei welcher Änderung auf welcher Tabelle soll der Index aktualisiert werden? | ||
+ | |||
+ | |||
+ | ===Lösungsvorschlag 1 - Tabelle für die PK's und Tabellenamen und Verwendung eines USER_DATASTORES=== | ||
+ | |||
+ | Suche findet über eine zentrale Tabelle statt, jede Änderung wird in dieser Tabelle protokolliert über Trigger auf den Source Tabellen. | ||
+ | |||
+ | Struktur der Master Tabelle: | ||
+ | |||
+ | * SEARCH_ID - PK | ||
+ | * PK_ID - PK der referenzierten Tabelle | ||
+ | * PK_TABLENAME - Name der referenzierten Tabelle | ||
+ | * CHANGE_DATE | ||
+ | |||
+ | |||
+ | Der Index wird auf die " | ||
+ | |||
+ | ==DB Modell== | ||
+ | |||
+ | Wir haben drei Tabellen mit den Rohdaten: | ||
+ | |||
+ | <code sql> | ||
+ | drop table t1; | ||
+ | |||
+ | create table t1 ( | ||
+ | t1_id number(11) not null | ||
+ | , t1_kostenstelle number(11) | ||
+ | , t1_bemerkung varchar2(256) | ||
+ | , CONSTRAINT t1_pk PRIMARY KEY (t1_id) | ||
+ | ); | ||
+ | insert into t1 values ( 1, | ||
+ | insert into t1 values ( 2, | ||
+ | insert into t1 values ( 3, | ||
+ | |||
+ | |||
+ | drop table t2; | ||
+ | |||
+ | create table t2 ( | ||
+ | t2_id number(11) not null | ||
+ | , t2_kostenstelle number(11) | ||
+ | , t2_bemerkung clob | ||
+ | , CONSTRAINT t2_pk PRIMARY KEY (t2_id) | ||
+ | ); | ||
+ | insert into t2 values ( 1, | ||
+ | insert into t2 values ( 2, | ||
+ | insert into t2 values ( 3, | ||
+ | |||
+ | drop table t3; | ||
+ | |||
+ | create table t3 ( | ||
+ | t3_id number(11) not null | ||
+ | , t3_kostenstelle number(11) | ||
+ | , t3_bemerkung blob | ||
+ | , CONSTRAINT t3_pk PRIMARY KEY (t3_id) | ||
+ | ); | ||
+ | |||
+ | -- insert Data document with a tool .-) | ||
+ | |||
+ | commit; | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | Master Tabelle: | ||
+ | |||
+ | <code sql> | ||
+ | drop table search_master; | ||
+ | |||
+ | create table search_master ( | ||
+ | | ||
+ | , PK_ID number(11) | ||
+ | , PK_TABLENAME varchar2(32) | ||
+ | , CHANGE_DATE varchar2(64) | ||
+ | , CONSTRAINT search_master_pk PRIMARY KEY (search_id) | ||
+ | ); | ||
+ | |||
+ | insert into search_master ( search_id, | ||
+ | select null,t1_id, ' | ||
+ | ; | ||
+ | |||
+ | insert into search_master ( search_id, | ||
+ | select null,t2_id, ' | ||
+ | ; | ||
+ | |||
+ | insert into search_master ( search_id, | ||
+ | select null,t3_id, ' | ||
+ | ; | ||
+ | |||
+ | commit; | ||
+ | </ | ||
+ | |||
+ | |||
+ | Protokoll Trigger: | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | create or replace trigger t1_i_trg | ||
+ | after insert | ||
+ | on t1 | ||
+ | for each row | ||
+ | begin | ||
+ | insert into search_master ( search_id, | ||
+ | end; | ||
+ | / | ||
+ | create or replace trigger t1_u_trg | ||
+ | after update | ||
+ | on t1 | ||
+ | for each row | ||
+ | begin | ||
+ | update search_master set CHANGE_DATE=to_char(sysdate) where PK_TABLENAME=' | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | create or replace trigger t2_i_trg | ||
+ | after insert | ||
+ | on t2 | ||
+ | for each row | ||
+ | begin | ||
+ | insert into search_master ( search_id, | ||
+ | end; | ||
+ | / | ||
+ | create or replace trigger t2_u_trg | ||
+ | after update | ||
+ | on t2 | ||
+ | for each row | ||
+ | begin | ||
+ | update search_master set CHANGE_DATE=to_char(sysdate) where PK_TABLENAME=' | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | create or replace trigger t3_i_trg | ||
+ | after insert | ||
+ | on t3 | ||
+ | for each row | ||
+ | begin | ||
+ | insert into search_master ( search_id, | ||
+ | end; | ||
+ | / | ||
+ | create or replace trigger t3_u_trg | ||
+ | after update | ||
+ | on t3 | ||
+ | for each row | ||
+ | begin | ||
+ | update search_master set CHANGE_DATE=to_char(sysdate) where PK_TABLENAME=' | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | </ | ||
+ | |||
+ | == PL/SQL Routine für das Daten sammeln == | ||
+ | |||
+ | |||
+ | PL/SQL Routine erstellen: | ||
+ | |||
+ | <code sql> | ||
+ | CREATE OR REPLACE procedure GPI.collect_ctx_data ( | ||
+ | p_rowid_in | ||
+ | p_lob_out | ||
+ | |||
+ | v_pk_id | ||
+ | v_pk_tablename | ||
+ | | ||
+ | v_file_doc | ||
+ | v_clob_doc | ||
+ | v_doc_text | ||
+ | | ||
+ | v_amount | ||
+ | v_position INTEGER := 1; | ||
+ | v_buffer raw(32767); | ||
+ | |||
+ | begin | ||
+ | -- rowid wird als input übergeben | ||
+ | -- Tabellenamen auslesen | ||
+ | select PK_ID | ||
+ | , PK_TABLENAME | ||
+ | into v_pk_id, v_pk_tablename | ||
+ | from search_master | ||
+ | where rowid = p_rowid_in; | ||
+ | |||
+ | -- je nach Tabelle die Daten auslesen | ||
+ | case v_pk_tablename | ||
+ | when ' | ||
+ | -- Varchar2 ist auch ganz einfach | ||
+ | | ||
+ | into p_lob_out | ||
+ | from t1 where t1_id=v_pk_id; | ||
+ | when ' | ||
+ | -- Clob kann direkt gelesen werden | ||
+ | | ||
+ | select '< | ||
+ | into p_lob_out | ||
+ | from t2 where t2_id=v_pk_id; | ||
+ | | ||
+ | when ' | ||
+ | -- Blob direkt on the fly filter | ||
+ | | ||
+ | select '< | ||
+ | , t3_bemerkung | ||
+ | into p_lob_out, v_file_doc | ||
+ | from t3 where t3_id=v_pk_id; | ||
+ | |||
+ | -- create a temporary CLOB to hold the document text | ||
+ | dbms_lob.createtemporary(v_clob_doc, | ||
+ | |||
+ | -- read the bfile to the blob | ||
+ | -- not nesseary if you use blob as datastore | ||
+ | /* | ||
+ | dbms_lob.open(v_file_doc, | ||
+ | loop | ||
+ | BEGIN | ||
+ | dbms_lob.read(v_file_doc, | ||
+ | exception | ||
+ | WHEN no_data_found THEN | ||
+ | exit; | ||
+ | END; | ||
+ | dbms_lob.writeappend(v_clob_doc, | ||
+ | v_position := v_position + v_amount; | ||
+ | END loop; | ||
+ | dbms_lob.close(v_file_doc); | ||
+ | */ | ||
+ | -- | ||
+ | -- call ctx_doc.policy_filter to filter the BLOB to CLOB data | ||
+ | -- | ||
+ | ctx_doc.policy_filter(' | ||
+ | |||
+ | -- add the extract text to the output | ||
+ | p_lob_out: | ||
+ | |||
+ | --free the lob | ||
+ | dbms_lob.freetemporary(v_clob_doc); | ||
+ | | ||
+ | end case; | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | siehe auch [[prog: | ||
+ | |||
+ | ==Oracle Text Index == | ||
+ | |||
+ | Eigenschaften setzen: | ||
+ | |||
+ | <code sql> | ||
+ | EXEC ctx_ddl.drop_preference( | ||
+ | |||
+ | EXEC ctx_ddl.create_preference( ' | ||
+ | |||
+ | EXEC ctx_ddl.set_attribute( | ||
+ | |||
+ | |||
+ | exec ctx_ddl.drop_section_group( | ||
+ | exec ctx_ddl.create_section_group( | ||
+ | |||
+ | / | ||
+ | </ | ||
+ | |||
+ | <code sql> | ||
+ | -- Policy for binary dokuments | ||
+ | BEGIN | ||
+ | -- create the policy for this example | ||
+ | ctx_ddl.create_preference(preference_name => ' | ||
+ | , object_name | ||
+ | |||
+ | ctx_ddl.set_attribute(preference_name | ||
+ | , attribute_name | ||
+ | , attribute_value | ||
+ | |||
+ | ctx_ddl.create_policy(policy_name | ||
+ | , FILTER | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | Index anlegen: | ||
+ | |||
+ | <code sql> | ||
+ | drop index idx_search_master_ctx; | ||
+ | |||
+ | create index idx_search_master_ctx on search_master( CHANGE_DATE) | ||
+ | indextype is ctxsys.context | ||
+ | parameters( ' | ||
+ | | ||
+ | / | ||
+ | |||
+ | select * from ctx_user_index_errors; | ||
+ | </ | ||
+ | |||
+ | |||
+ | Suchen und Update / Insert verhalten testen | ||
+ | <code sql> | ||
+ | |||
+ | select * from search_master where contains (change_date,' | ||
+ | -- 1 Record | ||
+ | |||
+ | -- test data | ||
+ | INSERT INTO t3 VALUES ( 4, | ||
+ | INSERT INTO t2 VALUES ( 4, | ||
+ | INSERT INTO t1 VALUES ( 4, | ||
+ | |||
+ | commit; | ||
+ | |||
+ | select * from search_master where contains (change_date,' | ||
+ | --nix | ||
+ | |||
+ | -- sync the index | ||
+ | exec ctx_ddl.sync_index( ' | ||
+ | |||
+ | select * from search_master where contains (change_date,' | ||
+ | --3 Records | ||
+ | |||
+ | |||
+ | -- Test Update Verhalten | ||
+ | |||
+ | update t1 set t1_bemerkung = ' | ||
+ | commit; | ||
+ | select * from search_master where contains (change_date,' | ||
+ | |||
+ | -- sync the index | ||
+ | exec ctx_ddl.sync_index( ' | ||
+ | |||
+ | select * from search_master where contains (change_date,' | ||
+ | --1 Records | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | |||
+ | ====Quellen==== | ||
+ | |||
+ | Oracle Support: | ||
+ | * How to Create a Text Index on Multiple Columns Belonging to Different Tables using USER_DATASTORE (Doc ID 395129.1) | ||
+ | |||
+ | Blogs: | ||
+ | |||
+ | * https:// |
prog/oracle_text_multiple_column_user_datastore.txt · Zuletzt geändert: 2023/10/23 15:21 von gpipperr