Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:oracle_text_multiple_column_user_datastore

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 ⇒ 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:

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;

„multi_column_datastore“ Preference anlegen:

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' )

Index anlegen

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;

Suchen:

SELECT kd_id FROM kunden WHERE contains(bemerkung,'Ziegel') > 0;
 
kd_id
------
1
4
SELECT * FROM kunden WHERE contains(bemerkung,'Ziegel within kd_name') > 0;
 
kd_id
------
4


Ü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:

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;

Master Tabelle:

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;

Protokoll Trigger:

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;
/
PL/SQL Routine für das Daten sammeln

PL/SQL Routine erstellen:

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;
/

siehe auch Oracle Text für die Verarbeitung von Binären Dokumenten in PL/SQL verwenden

Oracle Text Index

Eigenschaften setzen:

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' )
 
/
-- 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;
/

Index anlegen:

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;

Suchen und Update / Insert verhalten testen

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

Quellen

Oracle Support:

  • How to Create a Text Index on Multiple Columns Belonging to Different Tables using USER_DATASTORE (Doc ID 395129.1)

Blogs:

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
"Autor: Gunther Pipperr"
prog/oracle_text_multiple_column_user_datastore.txt · Zuletzt geändert: 2023/10/23 15:21 von gpipperr