Benutzer-Werkzeuge

Webseiten-Werkzeuge


prod:oracle_text_ctxcat_index

Einsatz des CTXCAT Indexes unter Oracle 19c für die Suche nach Kundenmerkmalen

Mehr zu Oracle Text siehe ⇒ Oracle Text - Volltext Suche über Text Dokumente

Aufgabe:

Für eine APEX App wird ein Filter Kriterium benötigt, um Kunden aus bestimmten Instituten auszuwählen. Die Kunden können mehreren Instituten zugeordnet werden und werden in der APP über eine eigene Meta Kunden Nummer geführt.

Da sich diese Zuordnung (Meta Kunde zu Institut) aber nicht trivial bzw. performant ermitteln lässt, wird täglich eine MV erzeugt die unteranderem eine Spalte mit diesem Filter Kriterium als Liste aller Institute enthält.

Das Format sieht so aus: <INST_ID1>:<INST_ID1>:<INST_ID3> wie '1256:0456:7890'

Die Liste wird mit dem einer ANA Funktion LISTAGG (siehe dazu Ein Listen von Werten in SQL erstellenerzeugt.

Lösung ohne Oracle Text

Im ersten Schritt wurde ganz trivial mit „INSTR“ „instr(INST_LIST,:DEFAULT_INSTITUT) >= 1“ auf das Institut für diese Spalte selektiert, allerdings wird das bei mehreren Millionen Kunden etwas zu langsam. Auch kann so die Spalte nicht indiziert werden!

Lösung mit dem CTXCAT Indexes

Genau für solche Aufgabenstellungen ist der CTXCAT Index ideal, der Index ist deutlich einfacher aufgebaut und ist auch transactional eingebunden, d.h. kann ohne weiteren Job für die Pflege eingesetzt werden.

In unseren Fall gibt es im „Katalog“ der mögliche Wert ja nur die Menge aller Institute im maximal zwei stelligen Bereich, d.h. die Anzahl der unterschiedliche Tockens ist sehr übersichtlich.

Nachteil:

Der Catsearch Operator kann in SQL Statement nicht so einfach mit „OR“ verknüpft werden!

Ein „catsearch(INST_LIST, :DEFAULT_INSTITUT,null) > 0 ) or nvl(:DEFAULT_INSTITUT,'1')='1' or :DEFAULT_INSTITUT='%'“ führt zu einem :

ORA-20000: Oracle Text error:
DRG-10849: catsearch unterstützt funktionale Aufrufe nicht

In meine Fall über die Möglichkeit per PL/SQL dynamsich SQL für eine Abfrage in APEX zu erzeugen das Statement je nach Bedarf mit den passenden Where Bedingungen erzeugt und damit ließ sich das OR vermeiden.


Grundprinzip CTXCAT Index

Doku 19c ⇒ https://docs.oracle.com/en/database/oracle/oracle-database/19/ccapp/creating-oracle-text-indexes.html#GUID-210F00C9-8866-466F-909F-71C672CF469B

  • Ein CTXCAT Index ist transactional, d.h. synchron zu mit dem Commit auf der Tabelle auch aktuell
  • Es können Sub Indexe angelegt werden die weiter Spalten der Tabelle mit berücksichtigen. Damit können „Mixed Queries“ abgesetzt werden, Diese Mixed Query Abfrage, kann dann Spalten zusammen mit dem Volltext Index abfragen.
  • Sollte nur für Spalten mit wenig „Worten/Tockens“ eingesetzt werden «100
  • Unterstützt auch Linguistische Feature
  • Es wird nur eine Domain Index Tabelle DR$xxx$I angelegt

Der Index eignet sich gut für genau meine Einsatzzweck, eine Liste mit nur wenigen Worten (auch wenig in der Gesamtmenge von disjunkten Werten) soll indiziert werden.

Für große Text Mengen ist das aber nicht geeignet!

Einfaches Demo ohne SubIndex Feature

Tabelle mit Index und Daten anlegen:

CREATE TABLE customers (
   id NUMBER NOT NULL
 , name varchar2(254)
 , inst_list  varchar2(4000)
 , segment NUMBER(1)
);
 
ALTER TABLE customers ADD ( CONSTRAINT  customers_pk PRIMARY KEY (id) );
 
CREATE INDEX idx_customers_inst_list   ON  customers(INST_LIST) indextype IS ctxsys.ctxcat;
 
-- Domain Index Tabelle wird erstellt:
SELECT COUNT(*) FROM DR$IDX_CUSTOMERS_INST_LIST$I;
 
--
0
 
INSERT INTO customers VALUES ( 1,'A1','1234:3456:789',1);
INSERT INTO customers VALUES ( 2,'B2','1234:987:001',1);
INSERT INTO customers VALUES ( 3,'C3','1234:3456:002',1);
INSERT INTO customers VALUES ( 4,'D4','003:3456:789',1);
 
commit;
 
SELECT COUNT(*) FROM DR$IDX_CUSTOMERS_INST_LIST$I;
--
12
-- 4 Datensätze mit je 3 tocken => 12 Einträge in der Domain Index Tabelle 

Abfragen mit:

SELECT * FROM customers WHERE catsearch(inst_list,'1234',NULL) > 0
 
---  ------     -------------  --
1	A1	1234:3456:789	1
2	B2	1234:987:001	1
3	C3	1234:3456:002	1

Auf das „null“ achten für die SubIndex Klausel, in dem Beispiel verwenden wir das ja nicht.

Alles wieder aufräumen:

DROP TABLE customers purge;

Was läßt sich einstellen?

Lexer Einstellungen setzen, da unsere Katalog nur aus Nummer besteht, ohne Groß/klein Schreibung, Trenner ist der „:“:

BEGIN
  ctx_ddl.create_preference('INST_LEXER','BASIC_LEXER');
  ctx_ddl.set_attribute('INST_LEXER','MIXED_CASE','NO');
  ctx_ddl.set_attribute('INST_LEXER','ENDJOINS',':');  
END;
/

Diese beim Anlegen hinterlegen:

CREATE INDEX idx_customers_inst_list   
          ON  customers(INST_LIST) 
indextype IS ctxsys.ctxcat parameters ('LEXER INST_LEXER');

Quellen

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
prod/oracle_text_ctxcat_index.txt · Zuletzt geändert: 2022/03/11 13:42 von gpipperr