Gunther Oracle APEX und Datenbank Wiki

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Zuletzt angesehen:
prog:oracle_text_multiple_column_user_datastore

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
prog:oracle_text_multiple_column_user_datastore [2017/04/22 13:22] – gpipperrprog:oracle_text_multiple_column_user_datastore [2023/10/23 15:21] (aktuell) – [Über mehrere Tabellen hinweg die Daten zusammenfassen] 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:oracle_text|Oracle Text - Volltext Suche über Text Dokumente]]
 +
 +
 +**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 "DATASTORE" Eigenschaft des Index lässt sich definieren wo die Daten liegen, wichtig ist es einen eindeutigen Primary Key für den späteren Zugriff auf die Daten zur Verfügung zu haben.
 +
 +  * **MULTI_COLUMN_DATASTORE** => Alle Spalten liegen in gleichen Tabelle 
 +  * **DETAIL_DATASTORE**  => Text liegt in der Datenbank in verschiedenen Tabellen, Master wird indiziert, Text in den Detail Tabellen
 +  * **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 - MULTI_COLUMN_DATASTORE ==== 
 +
 +
 +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  varchar2(256)
 + , bemerkung varchar2(256)
 + , CONSTRAINT kunden_pk PRIMARY KEY (kd_id)
 +);
 +
 +insert into kunden values (1,'Huber Bau Gmbh','Am Steingraben 37214 Witzenhausen','Erdarbeiten und Abbruch, Ziegel');
 +insert into kunden values (2,'Elektro Kohler','Ziegelweg Eschwege','Elektriker, Installationen und Netzwerk');
 +insert into kunden values (3,'Stahl Starke','Werra Str. 7 Hubenrode','Treppenbau');
 +insert into kunden values (4,'Müller Ziegel',' 37214 Witzenhausen','Rohbau');
 +insert into kunden values (5,'Maier Abbruch','Am Steingraben 37214 Witzenhausen','Abbruch und Container');
 +commit;
 +
 +</code>
 +
 +
 +"multi_column_datastore" Preference anlegen:
 +
 +<code sql>
 +exec ctx_ddl.drop_preference  ( 'GPI_MULTI_COL_STORE' )
 +exec ctx_ddl.create_preference( 'GPI_MULTI_COL_STORE', 'multi_column_datastore' )
 +exec ctx_ddl.set_attribute    ( 'GPI_MULTI_COL_STORE', 'columns', 'kd_name, anschrift, bemerkung' )
 +exec ctx_ddl.set_attribute    ( 'GPI_MULTI_COL_STORE', 'filter',  'Y     ,Y      ,Y' )
 +
 +exec ctx_ddl.drop_section_group(    'GPI_SECTION_GRP' )
 +exec ctx_ddl.create_section_group(  'GPI_SECTION_GRP' , 'auto_section_group' )
 +
 +</code>
 +
 +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( 'datastore     GPI_MULTI_COL_STORE
 +             section group GPI_SECTION_GRP' )
 +/
 +
 +--Check for any filtering errors
 +select * from ctx_user_index_errors;
 +</code>
 +
 +
 +Suchen:
 +
 +<code sql>
 +select kd_id from kunden where contains(bemerkung,'Ziegel') > 0;
 +
 +kd_id
 +------
 +1
 +4
 +</code>
 +
 +
 +<code sql>
 +select * from kunden where contains(bemerkung,'Ziegel within kd_name') > 0;
 +
 +kd_id
 +------
 +4
 +</code>
 +
 +
 +----
 +
 +----
 +
 +
 +==== Über mehrere Tabellen hinweg die Daten zusammenfassen  mit dem USER_DATASTORE==== 
 +
 +
 +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  - letzte Änderung - über diese Spalte wird der Oracle Text Index Update getriggert
 +
 +
 +Der Index wird auf die "CHANGE_DATE" Spalte zwar angelegt, verwendet aber nicht die Werte in der Spalte, sonder mit dem USER_DATASTORE wird per PL/SQL Funktion der passende Text aus der jeweiligen Tabelle extrahiert. Ändert sich aber der Wert der Spalte "CHANGE_DATE" wird automatisch diese Spalte für den nächsten Index Maintaince Prozess markiert und damit baldmöglichst von Oracle TEXT neu eingelesen.
 +
 +==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,2342324,'Bohrhammer Makita');
 +insert into t1 values ( 2,3334325,'Bohrhammer Bosch');
 +insert into t1 values ( 3,4345326,'Bohrhammer Hilit');
 +
 +
 +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,2342324,'Lehmputz');
 +insert into t2 values ( 2,3334325,'Kalkputz');
 +insert into t2 values ( 3,4345326,'Sand');
 +
 +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;
 +
 +</code> 
 +
 +
 +Master Tabelle:
 +
 +<code sql>
 +drop table search_master;
 +
 +create table search_master (   
 +   search_id number(11) GENERATED BY DEFAULT ON NULL AS IDENTITY
 + , 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,PK_ID,PK_TABLENAME,CHANGE_DATE)
 +select null,t1_id, 'T1' , sysdate from t1
 +;
 +
 +insert into search_master ( search_id,PK_ID,PK_TABLENAME,CHANGE_DATE)
 +select null,t2_id, 'T2' , sysdate from t2
 +;
 +
 +insert into search_master ( search_id,PK_ID,PK_TABLENAME,CHANGE_DATE)
 +select null,t3_id, 'T3' , sysdate from t3
 +;
 +
 +commit;
 +</code>
 +
 +
 +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,PK_ID,PK_TABLENAME,CHANGE_DATE) values ( null,:new.t1_id, 'T1', to_char(sysdate) );
 +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='T1' and PK_ID=:new.t1_id;
 +end;
 +/
 +
 +create or replace trigger t2_i_trg
 +after insert 
 +  on t2
 +  for each row
 +begin
 +  insert into search_master ( search_id,PK_ID,PK_TABLENAME,CHANGE_DATE) values ( null,:new.t2_id, 'T2', to_char(sysdate) );
 +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='T2' and PK_ID=:new.t2_id;
 +end;
 +/
 +
 +create or replace trigger t3_i_trg
 +after insert 
 +  on t3
 +  for each row
 +begin
 +  insert into search_master ( search_id,PK_ID,PK_TABLENAME,CHANGE_DATE) values ( null,:new.t3_id, 'T3', to_char(sysdate) );
 +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='T3' and PK_ID=:new.t3_id;
 +end;
 +/
 +
 +</code>
 +
 +== 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  in              rowid,
 +    p_lob_out   in out NOCOPY   clob ) is
 +
 +  v_pk_id        number;
 +  v_pk_tablename  varchar2(30);
 +  
 +  v_file_doc    blob;
 +  v_clob_doc    clob;
 +  v_doc_text   clob;
 +  
 +  v_amount   INTEGER := 32767;
 +  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 'T1' then
 +      -- Varchar2 ist auch ganz einfach
 +       select '<T1_KST>' || t1_kostenstelle || '</T1_KST><T1_TEXT>' || t1_bemerkung || '</T1_TEXT>'
 +         into p_lob_out
 +       from t1 where t1_id=v_pk_id;
 +    when 'T2' then
 +      -- Clob kann direkt gelesen werden
 +      
 +      select '<T2_KST>' || t2_kostenstelle || '</T2_KST><T2_TEXT>' || t2_bemerkung || '</T2_TEXT>'
 +         into p_lob_out
 +       from t2 where t2_id=v_pk_id;    
 +          
 +    when 'T3' then
 +        -- Blob direkt on the fly filter
 +    
 +          select '<T3_KST>' || t3_kostenstelle || '</T3_KST>'
 +              , 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, TRUE, dbms_lob.session);
 + 
 +          -- read the bfile to the blob
 +          -- not nesseary if you use blob as datastore
 +          /*
 +          dbms_lob.open(v_file_doc, dbms_lob.lob_readonly);
 +          loop
 +            BEGIN
 +              dbms_lob.read(v_file_doc, v_amount, v_position, v_buffer);
 +            exception
 +            WHEN no_data_found THEN
 +              exit;
 +            END;
 +            dbms_lob.writeappend(v_clob_doc, v_amount, v_buffer);
 +            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('GPI_FAST_POLICY', v_file_doc,v_doc_text , FALSE);
 +         
 +          -- add the extract text to the output
 +          p_lob_out:=p_lob_out||'<T3_TEXT>' || v_doc_text || '</T3_TEXT>';
 +                   
 +          --free the lob 
 +          dbms_lob.freetemporary(v_clob_doc);
 +          
 +  end case;
 +end;
 +/
 +</code>
 +
 +siehe auch [[prog:oracle_text_in_plsql|Oracle Text für die Verarbeitung von Binären Dokumenten in PL/SQL verwenden]]
 +
 +==Oracle Text Index ==
 +
 +Eigenschaften setzen:
 +
 +<code sql>
 +EXEC ctx_ddl.drop_preference(   'GPI_USER_DATASTORE' )
 + 
 +EXEC ctx_ddl.create_preference( 'GPI_USER_DATASTORE', 'user_datastore' )
 + 
 +EXEC ctx_ddl.set_attribute(     'GPI_USER_DATASTORE', 'procedure', 'collect_ctx_data' )
 +
 +
 +exec ctx_ddl.drop_section_group(    'GPI_SECTION_GRP' )
 +exec ctx_ddl.create_section_group(  'GPI_SECTION_GRP', 'auto_section_group' )
 +
 +/
 +</code>
 +
 +<code sql> 
 +-- Policy for binary dokuments
 +BEGIN
 +  -- create the policy for this example
 +  ctx_ddl.create_preference(preference_name => 'fast_filter'
 +                          , object_name  => 'AUTO_FILTER');
 + 
 +  ctx_ddl.set_attribute(preference_name  => 'fast_filter'
 +                      , attribute_name   => 'OUTPUT_FORMATTING'
 +                      , attribute_value  => 'FALSE');
 + 
 +  ctx_ddl.create_policy(policy_name      => 'GPI_FAST_POLICY' 
 +                      , FILTER           => 'fast_filter');
 +END;
 +/
 +</code>
 +
 +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( 'datastore     GPI_USER_DATASTORE 
 +             section group GPI_SECTION_GRP' )
 +/
 +
 +select * from ctx_user_index_errors;
 +</code>
 +
 +
 +Suchen und Update / Insert verhalten testen
 +<code sql>
 +
 +select * from search_master where contains (change_date,'Ziegel') > 0
 +-- 1 Record
 +
 +-- test data
 +INSERT INTO t3 VALUES ( 4,4345326,'Split');
 +INSERT INTO t2 VALUES ( 4,4345326,'Split');
 +INSERT INTO t1 VALUES ( 4,4345326,'Split');
 +
 +commit;
 +
 +select * from search_master where contains (change_date,'Split') > 0
 +--nix
 +
 +-- sync the index 
 +exec ctx_ddl.sync_index( 'idx_search_master_ctx' );
 +
 +select * from search_master where contains (change_date,'Split') > 0;
 +--3 Records
 +
 +
 +-- Test Update Verhalten
 +
 +update t1 set t1_bemerkung = 'Schlagbohrmaschine' where t1_id=1;
 +commit;
 +select * from search_master where contains (change_date,'Schlagbohrmaschine') > 0;
 +
 +-- sync the index 
 +exec ctx_ddl.sync_index( 'idx_search_master_ctx' );
 +
 +select * from search_master where contains (change_date,'Schlagbohrmaschine') > 0;
 +--1 Records
 +
 +</code>
 +
 +
 +----
 +
 +
 +
 +====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://blogs.oracle.com/searchtech/entry/indexing_data_from_multiple_tables