Posts mit dem Label Composite Domain Index werden angezeigt. Alle Posts anzeigen
Posts mit dem Label Composite Domain Index werden angezeigt. Alle Posts anzeigen

Dienstag, 26. Oktober 2010

Neues Oracle Text Feature XML QUERY RESULT SET

Im letzten Blog wurde schon das neue Text Feature NAME SEARCH, das mit Veröffentlichung des Patchsets 11.2.0.2 zur Verfügung steht, erläutert. In folgendem Tipp soll ein weiteres neues Feature - das sogenannte RESULT SET INTERFACE - an einem Beispiel illustriert werden.
Die Idee ist, vielfältige komplexe Abfragen für Ergebnismengen im Textumfeld, auf einen einzigen SQL Call zu reduzieren und damit Ressourcen zu sparen und die Performance zu erhöhen.
Als Beispieltabelle soll folgende Defintion verwendert werden:

 
DROP TABLE dokumente PURGE;
CREATE TABLE dokumente (id number, autor varchar2(30), datum date, titel varchar2(200), dokument clob);

INSERT INTO dokumente VALUES (1,'Doderer',sysdate,'Buch1','Doderer: Buch1: Oracle Buch');
INSERT INTO dokumente VALUES (2, 'Swobodnik' ,sysdate+1,'Buch2', 'Swobodnik: Buch2');
INSERT INTO dokumente VALUES (3, 'Tucholsky' ,sysdate+2,'Buch3', 'Tucholsky: Buch3');
INSERT INTO dokumente VALUES (4, 'Varga' ,sysdate+3,'Buch4', 'Varga: Buch4');
INSERT INTO dokumente VALUES (5, 'Maurer' ,sysdate+4,'Buch5', 'Maurer: Buch5');
INSERT INTO dokumente VALUES (6, 'Steinfest' ,sysdate+5,'Buch6', 'Steinfest: Buch6: Oracle Kategorie');
INSERT INTO dokumente VALUES (7, 'Rowling' ,sysdate+6,'Buch7', 'Rowling: Buch7');
INSERT INTO dokumente VALUES (8, 'Poe' ,sysdate+7,'Buch8', 'Poe: Buch8');
INSERT INTO dokumente VALUES (9, 'Beauvoir' ,sysdate+8,'Buch9', 'Beauvoir: Buch9');
INSERT INTO dokumente VALUES (10, 'Schwarzer' ,sysdate+9,'Buch10', 'Schwarzer: Buch10');
INSERT INTO dokumente VALUES (11, 'Schmidt' ,sysdate+10,'Buch11', 'Schmidt: Buch11');
INSERT INTO dokumente VALUES (12, 'Sartre' ,sysdate+11,'Buch12', 'Sartre: Buch12');
INSERT INTO dokumente VALUES (13, 'Maurer' ,sysdate+12,'Buch13', 'Maurer: Buch13');
INSERT INTO dokumente VALUES (14, 'Maurer' ,sysdate+13,'Buch14', 'Maurer: Buch14: Oracle Buch');
INSERT INTO dokumente VALUES (15, 'Maurer' ,sysdate+10,'Buch15', 'Maurer: Buch15: Oracle Buch');
INSERT INTO dokumente VALUES (16, 'Maurer' ,sysdate+10,'Buch16', 'Maurer: Buch16: Oracle ');
INSERT INTO dokumente VALUES (17, 'Maurer' ,sysdate+10,'Buch17', 'Maurer: Buch17');
INSERT INTO dokumente VALUES (18, 'Maurer' ,sysdate+10,'Buch18', 'Maurer: Buch18');
INSERT INTO dokumente VALUES (19, 'Maurer' ,sysdate+10,'Buch19', 'Maurer: Buch19');
INSERT INTO dokumente VALUES (20, 'Maurer' ,sysdate+10,'Buch20', 'Maurer: Buch20');
COMMIT;
Um effiziente Abfragen auf die Spalten AUTOR, DATUM und TITEL durchzuführen, wird die folgende Indexdefinition mit Angabe der FILTER BY und ORDER BY Klausel ausgeführt. Dieser Index - auch Composite Domain Index genannt - ist neu in Oracle 11g und bietet besonders gute Perfromance bei Mixed Query- Abfragen (siehe auch den Blogeintrag zum Thema).
 
DROP INDEX dokumente_IDX; 
CREATE INDEX dokumente_idx ON dokumente(dokument) INDEXTYPE IS ctxsys.contextFILTER BY autor, datum, titel;
SELECT err_text FROM ctx_user_index_errors WHERE err_index_name = 'DOKUMENTE_IDX';

Um die verschiedensten Informationen über die gespeicherten Dokumente zu erhalten, könnten sich nun folgende Fragestellungen ergeben: 1)Wie groß ist die Anzahl der Bücher, die das Wort "Oracle" enthalten?

SELECT count(*) FROM dokumente WHERE contains(dokument, 'oracle',1)>0; 
COUNT(*)
----------         
5
Eine effiziente Alternative bietet dabei die Funktion COUNT_HITS (siehe auch den Blogeintrag zum Thema) wie in folgendem Beispiel zu sehen ist:
 
set serveroutput on
declare  v_number number;
begin  v_number := ctx_query.count_hits (
      index_name => 'DOKUMENTE_IDX',
      text_query => 'oracle',   
           exact => true  );  
dbms_output.put_line('Anzahl Treffer: '||v_number);
end;
/
2) Wie groß ist diese Anzahl der Bücher gruppiert nach Datum?

SELECT datum, count(*) FROM dokumente 
where contains(dokument, 'oracle',1)>0 GROUP BY datum;
DATUM       COUNT(*)
--------- ----------
26-OCT-10          1
31-OCT-10          1
08-NOV-10          1
05-NOV-10          2
3) Wie groß ist diese Anzahl der Bücher gruppiert nach Autor?

SELECT autor, count(*) FROM dokumente 
WHERE contains(dokument, 'oracle',1)>0 GROUP BY autor;
AUTOR                            COUNT(*)
------------------------------ ----------
Doderer                                 1
Maurer                                  3
Steinfest                               1
4) Welche Bücher aus der Topliste enthalten das Wort "Oracle"?
 
SELECT * FROM  
(SELECT /*+ first_rows */ rowid, titel, autor, datum   
 FROM dokumente WHERE contains(dokument, 'oracle',1)>0   
 ORDER BY datum desc, score(1) desc)
WHERE rownum <= 3;
ROWID              TITEL      AUTOR      DATUM
------------------ ---------- ---------- ---------
AAAXJXAAFAAAfAUAAN Buch14     Maurer     08-NOV-10
AAAXJXAAFAAAfAUAAO Buch15     Maurer     05-NOV-10
AAAXJXAAFAAAfAUAAP Buch16     Maurer     05-NOV-10
Das Ganze lässt sich in 11.2.0.2 alternativ mit dem neuen Feature XML QUERY RESULT SET Interface lösen. Die Idee dabei ist, die mehrmalige Ausführung von SQL Statements wie in unserem Beispiel durchgeführt zu vermeiden und die Ergebnisse mit einem einzigen SQL Aufruf zu erhalten. Somit könnte eine schnellere und effizientere Ausgaben erfolgen. Wie der Name des Features schon andeutet, ist zur Beschreibung der Abfrage eine bestimmte XML-Eingabe - der XML SET Descriptor - notwendig. Auch das Ergebnis, die Hitliste, ist im XML Format. Verwendet wird zusätzlich die SDATA Sektionen, die automatisch mit Erzeugung des Composite Domain Index zur Verfügung stehen. So wird die Information über Gruppierungen in der Attributliste GROUP SDATA angegeben und END_HIT_NUMBER gibt die Größe der Topliste aus. Eine umfangreiche und vollständige Beschreibung findet sich im Handbuch. In unserem Beispiel sieht die Abfrage dann folgendermassen aus:
  
declare   
   ergebnis_clob clob;
begin   
   dbms_lob.createtemporary(ergebnis_clob, true, dbms_lob.session);
   ctx_query.result_set('dokumente_idx', 'Oracle', '
       <ctx_result_set_descriptor> <count/>
         <hitlist start_hit_num="1" end_hit_num="3" order="datum desc, score desc">
           <score/>
           <rowid/>
           <sdata name="titel"/>
           <sdata name="autor"/>
           <sdata name="datum"/>
         </hitlist>
        <group sdata="datum">
          <count/>
        </group>
        <group sdata="autor">
          <count/>
        </group>
     </ctx_result_set_descriptor>
                                                    ',ergebnis_clob); 
dbms_output.put_line('Ergebnis: '|| ergebnis_clob);
dbms_lob.freetemporary(ergebnis_clob);
 exception   
  when others 
  then    
    dbms_lob.freetemporary(ergebnis_clob);    
    raise;
end;
/
Auch das Ergebnis wird im XML Format dargestellt. In unserem Fall erhalten wir folgende Ausgabe. (Zur besseren Lesbarkeit wurden Leerzeichen eingefügt.)

Ergebnis:

<ctx_result_set>
 <hitlist>
  <hit><score>5</score><rowid>AAAXJXAAFAAAfAUAAN</rowid>
    <sdata name="TITEL">Buch14</sdata><sdata name="AUTOR">Maurer</sdata>
    <sdata name="DATUM">2010-11-08 11:56:06</sdata>
  </hit>
  <hit><score></score><rowid>AAAXJXAAFAAAfAUAAO</rowid>
   <sdata name="TITEL">Buch15</sdata><sdata name="AUTOR">Maurer</sdata>
   <sdata name="DATUM">2010-11-05 11:56:06</sdata>
  </hit>
  <hit><score>5</score><rowid>AAAXJXAAFAAAfAUAAP</rowid>
   <sdata name="TITEL">Buch16</sdata><sdata name="AUTOR">Maurer</sdata>
   <sdata name="DATUM">2010-11-05 11:56:06</sdata>
 </hit>
 </hitlist>
<count>5</count>
<groups sdata="DATUM">
  <group value="2010-10-26 11:56:06"><count>1</count></group>
  <group value="2010-10-31 11:56:06"><count>1</count></group>
  <group value="2010-11-05 11:56:06"><count>2</count></group>
  <group value="2010-11-08 11:56:06"><count>1</count></group>
</groups>
<groups sdata="AUTOR">
  <group value="Doderer"><count>1</count></group>
  <group value="Maurer"><count>3</count></group>
  <group value="Steinfest"><count>1</count></group>
</groups>
</ctx_result_set>

PL/SQL procedure successfully completed.
Um eine besser lesbare Ausgabeform zu erhalten, bietet sich beispielsweise die Nutzung der XML-Funktionen an. Dazu mehr in einem unserer nächsten Ausgaben...

Montag, 17. Mai 2010

"Sicheres" Suchen: Oracle TEXT und die "Virtual Private Database"

Eine Volltextsuche geht bekanntlich immer über die ganze Tabelle. Manchmal bestehen allerdings Anforderungen an "sicheres Suchen", es soll also nicht jeder alles sehen können. Hierzu bietet sich im "normalen" Datenbankumfeld die Virtual Private Database an. Diese bewirkt, kurzgefasst, dass unterschiedliche Nutzer ein SELECT * FROM TABELLE absetzen, jeder sieht jedoch was anderes (das Feature gehört zur Enterprise Edition der Datenbank). Das wollen wir nun im Zusammenspiel mit Oracle TEXT einsetzen. Zuerst erzeugen wir die Tabelle und fügen ein paar Spalten ein:
create table dokumente(
  id                 number(10),
  sicherheitsmerkmal varchar2(50),
  dokument           clob
)
/

insert into dokumente values (1, 'LOW', 'Oracle TEXT dient zur Volltextrecherche');
insert into dokumente values (2, 'LOW', 'Oracle TEXT beherrscht linguistische Suche');
insert into dokumente values (3, 'MEDIUM', 'Der Gewinn wird im nächsten Jahr steigen');
insert into dokumente values (4, 'HIGH', 'Die Spionageabteilung sitzt in Berlin');
Das Besondere ist die Spalte SICHERHEITSMERKMAL - sie kann die Werte MEDIUM, HIGH oder LOW annehmen. Es fehlt nun nur noch eine Tabelle, mit welcher wir die Datenbanknutzer auf die Sicherheitsklassen abbilden ...
create table dokumente_nutzer(
  userid             varchar2(30),
  sicherheitsmerkmal varchar2(100)
)
/

insert into dokumente_nutzer values ('SCOTT', 'HIGH');
insert into dokumente_nutzer values ('SCOTT', 'MEDIUM');
insert into dokumente_nutzer values ('SCOTT', 'LOW');
insert into dokumente_nutzer values ('REVISION', 'MEDIUM');
insert into dokumente_nutzer values ('REVISION', 'LOW');
insert into dokumente_nutzer values ('ALLE', 'LOW');
Dann werden (als SYS) die Nutzer angelegt und, da die Privilegiensteuerung komplett über die Virtual Private Database gesteuert wird, SELECT-Privilegien an der Tabelle an PUBLIC vergeben.
create user revision identified by revision
/

create user alle identified by alle
/

grant create session to revision
/

grant create session to alle
/

grant select on scott.dokumente to public
/

Nun geht es ans Einrichten der Virtual Private Database. Zunächst wird die sog. Policy Function benötigt. Die Policy Function generiert eine WHERE-Bedingung, die dann von der Datenbank automatisch in das SELECT-Kommando integriert wird. Durch diese Vorgehensweise (die Sicherheitsregel "hängt" direkt an der Tabelle) kann die Sicherheitsregel nicht umgangen werden! Unsere PL/SQL-Funktion ist recht einfach ...
create or replace function my_policy(
 p_schema varchar2, p_object varchar2
) return varchar2 is
  v_where varchar2(32767) := 'SICHERHEITSMERKMAL IN (';
  v_users boolean := false;
begin
  for i in (
    select sicherheitsmerkmal from dokumente_nutzer
    where userid = user
  ) loop
    v_users := true;
    v_where := v_where || '''' || i.sicherheitsmerkmal || ''', ';
  end loop;
  if v_users then 
    v_where := substr(v_where, 1, length(v_where) - 2) || ')'; 
  else
    v_where := '1=0';
  end if;
  return v_where;
end;  
Als letztes machen wir die Virtual Private Database "scharf" ... Um dieses Kommando laufen zu lassen, wird das EXECUTE-Privileg auf DBMS_RLS benötigt - oder der DBA macht es gleich selbst ...
begin
 DBMS_RLS.ADD_POLICY(
    object_schema => 'SCOTT',
    object_name => 'DOKUMENTE',
    policy_name => 'SCOTT_DOKUMENT_POLICY',
    function_schema => 'SCOTT',
    policy_function => 'MY_POLICY'
  );
end;
/
Und nun kommt der erste Test (noch ohne Volltextindex). Probiert nun mal, die Tabelle mit verschiedenen Nutzerkonten zu selektieren ... Zuerst als SCOTT:
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERKMAL                                 DOKUMENT
---------- -------------------------------------------------- --------------------------------------------------
         1 LOW                                                Oracle TEXT dient zur Volltextrecherche
         2 LOW                                                Oracle TEXT beherrscht linguistische Suche
         3 MEDIUM                                             Der Gewinn wird im nächsten Jahr steigen
         4 HIGH                                               Die Spionageabteilung sitzt in Berlin

4 Zeilen ausgewählt.
... dann als User ALLE:
SQL> select * from scott.dokumente;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.
... schließlich als irgendein User, der in der Tabelle DOKUMENTE_NUTZER nicht vorkommt ...
SQL> select * from scott.dokumente;

Es wurden keine Zeilen ausgewählt
Man sieht, dass zwar alle die Tabelle selektieren können, jeder sieht allerdings was anderes. Und das kann man natürlich auch mit dem Volltextindex kombinieren. Den müssen wir natürlich zuerst mal anlegen. Und hier wollen wir auch gleich das in 11g neue Feature des Composite Domain Index nutzen (siehe das eigene Blog Posting dazu. Nötig ist das, da in diesem Fall jede Abfrage eine Mixed Query sein wird - die Virtual Private Database hängt ja eine relationale Abfrage an jede Textabfrage an ...
create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Nun können wir testen: Wir arbeiten als User ALLE und probieren mal einige Vollextabfragen ...
SQL> select * from scott.dokumente where contains (dokument,'Oracle') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         1 LOW             Oracle TEXT dient zur Volltextrecherche
         2 LOW             Oracle TEXT beherrscht linguistische Suche

2 Zeilen ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

Es wurden keine Zeilen ausgewählt
Die Abfrage mit dem Gewinn probieren wir danach mal als User REVISION - der darf mehr sehen, aber nicht alles ...
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         3 MEDIUM          Der Gewinn wird im nächsten Jahr steigen

1 Zeile ausgewählt.

SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

Es wurden keine Zeilen ausgewählt.
Die Spionageabteilung darf aber nur der SCOTT sehen ...
SQL> select * from scott.dokumente where contains (dokument,'Spionageabteilung') > 0;

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
         4 HIGH            Die Spionageabteilung sitzt in Berlin

1 Zeile ausgewählt.
Und das Schöne ist, dass diese Sicherheitsregeln unabhängig vom Textindex arbeiten; sie ziehen sowohl bei "normalen" als auch bei Volltextrecherchen. Zum Abschluß werfen wir einen Blick auf die Performance - normalerweise sind solche Mixed Queries ja problematisch ... insbesondere, wenn das Volltextkriterium nur gemeinsam mit dem relationalen Kriterium selektiv ist. Auch dazu bauen wir einen kleinen Testcase und fügen einige Sätze in die Tabelle DOKUMENTE ein.
drop index vt_dokumente
/

begin
  for i in 1..100000 loop
    insert into dokumente values (i+10, 'MEDIUM', 'Der Gewinn ist dieses Jahr stabil');
    insert into dokumente values (i+200010, 'LOW', 'Oracle TEXT ist Teil der Datenbank');
   end loop;
end;
/

commit
/

insert into dokumente values (500000, 'LOW', 'Gewinn ist: Mehr Einnahmen als Ausgaben');
    

create index vt_dokumente on dokumente(dokument)
indextype is ctxsys.context
filter by sicherheitsmerkmal
parameters ('transactional')
/
Diese Situation bringt das Mixed Query-Problem auf den Punkt. Die Volltextrecherche nach Gewinn allein ist nun extrem unselektiv (100.000 mal in MEDIUM, 1 mal in LOW). Die Sicherheitsklasse LOW ist aber auch unselektiv (kommt auch 100.000 mal vor). Die Kombination als Volltextsuche und relationalen Kriterium allein ist sehr selektiv - denn Gewinn kommt in der Sicherheitsklasse LOW nur einmal vor. Schauen wir uns die Abfrage nun an - wir arbeiten mit dem User ALLE (dieser hat nur Zugriff auf LOW), es darf also nur die Zeile der Klasse LOW zurückkommen ...
SQL>select * from scott.dokumente where contains (dokument,'Gewinn') > 0

        ID SICHERHEITSMERK DOKUMENT
---------- --------------- --------------------------------------------------
    300000 LOW             Gewinn ist: Mehr Einnahmen als Ausgaben

1 Zeile wurde ausgewählt.

Abgelaufen: 00:00:00.17
Sieht auf den ersten Blick ganz gut aus - und der Ausführungsplan ...?
SQL> set autotrace traceonly
SQL> select * from scott.dokumente where contains (dokument,'Gewinn') > 0;

1 Zeile wurde ausgewählt.

Ausführungsplan
----------------------------------------------------------
Plan hash value: 3982102634

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 42738 |  6135K|  9161   (4)| 00:01:50 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DOKUMENTE    | 42738 |  6135K|  9161   (4)| 00:01:50 |
|*  2 |   DOMAIN INDEX              | VT_DOKUMENTE |       |       |  7710   (1)| 00:01:33 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("DOKUMENT",'Gewinn')>0)
       filter("SICHERHEITSMERKMAL"='LOW')


Statistiken
----------------------------------------------------------
         35  recursive calls
          0  db block gets
        551  consistent gets
          0  physical reads
          0  redo size
        928  bytes sent via SQL*Net to client
        648  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
Im Ausführungsplan erkennt man, dass die komplette Abfrage aus dem Volltextindex heraus bedient wurde. Genau für solche Fälle wurde dieses Feature in 11g auch geschaffen ... Zum Abschluß noch ein paar Links zu weiterführender Dokumentation:

Mittwoch, 19. August 2009

Abfrage-Optimierung mit Composite Domain Index

Wie in vorangegangen Blogs gezeigt wurde, kann die sogenannte Mixed Query Problematik - eine Kombination aus relationalem und Volltextrecherche-Anteil - mit neuen Section Features in Oracle Database 10g bzw. 11g angegangen und teilweise gelöst werden. Der MData Section und MULTI_COLUMN_DATASTORE-Blog verwendete dabei zur Lösung, die in 10g eingeführte MDATA-Sections und der SData Section-Blog die neue SDATA-Section. Darüber hinaus ist in 11g eine neue Form des Context Index, der sogenannte „Composite Domain Index“ (kurz CDI) neu eingeführt worden, um speziell bei der Optimierung von Mixed Queries eine einfache direkte Lösung zu bieten. Ein Composite Domain Index ist dabei ein zusammengesetzter Index, der sich nicht auf die Textinformation beschränkt, sondern auch strukturelle Informationen mitführt und mit einem einzigen Aufruf an die Textengine ausgeführt wird.
So können Queries die aus
  • Textanteilen und strukturierten Anteilen in the SQL WHERE Klausel
  • Textanteilen und strukturierten Anteilen in der ORDER BY Klausel
  • eine Kombination aus beidem

  • einfach optimiert werden. Dieses Feature kann dabei unabhängig von den Section Features genutzt werden. Keine Sections oder gar Änderung am Abfragecode ist notwendig. Die Technologie verwendet allerdings aus Optimierungsgründen im Hintergrund genau wie bei SDATA-Sections ein zusätzliches Indexsegment, eine IOT-Tabelle mit Namen DR$SDATA_INDEX$S. Das Anlegen des CDI erfolgt dann mit folgender einfacher erweiterter Syntax.
    
    CREATE INDEX comp_ind ON customers(cust_first_name)
    INDEXTYPE IS ctxsys.context
    FILTER BY cust_id
    ORDER BY cust_year_of_birth
    
    Oracle Text wird nun die Daten aus CUST_ID und CUST_YEAR_OF_BIRTH im Textindex speichern; dabei besteht keine Notwendigkeit, die Queries anzupassen. Der Optimizer wird feststellen, dass die Abfrage durch den Textindex allein verifiziert werden kann. Die Erweiterung mit ORDER BY führt sogar dazu, dass die abgerufenen Zeilen danach sortiert ausgeliefert werden können. Im Unterschied zu B*Tree Indizes können allerdings nur die Informationen gefiltert werden, die auch schon synchronisiert worden sind.
    An einem Beispiel wollen wir die Optimierung aufzeigen. Im ersten Fall verwenden wir folgenden einfachen CONTEXT Index
    
    CREATE INDEX text_ind ON customers(cust_first_name) 
    INDEXTYPE IS ctxsys.context;
    
    Sehen wir uns nun die Ausführungszeit folgender Query an. Die Abfrage ist wie häufig bei Webanwendungen anzutreffen optimiert im Hinblick auf sortierten Zugriffe der ersten Zeilen.
    
    SELECT /*+ first_rows(10) */ cust_id
    FROM (select cust_id, cust_first_name,cust_year_of_birth from customers
    WHERE contains (cust_first_name, 'A% or D% or N% or B%',1)>0  AND cust_id>100000 
    ORDER BY cust_year_of_birth, score(1))
    WHERE rownum<10
    
       CUST_ID
    ----------
        102011
        103921
        100199
        100930
        104242
        103080
        103187
        103412
        103684
    
    9 rows selected.
    
    Elapsed: 00:00:00.12
    
    Der folgende Ausführungsplan ist etwas länglich, zeigt allerdings schon ohne genaue Analyse dass Textindex TEXT_IND und B*Index CUSTOMERS_PK für die Ausführung notwendig sind.
    
    SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic'))
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    EXPLAINED SQL STATEMENT:
    ------------------------
    SELECT /*+ first_rows(10) */ cust_id FROM (select cust_id,
    cust_first_name,cust_year_of_birth from customers WHERE contains
    (cust_first_name, 'A% or D% or N% or B%',1)>0  AND cust_id>100000 order
    by cust_year_of_birth, score(1)) where rownum<10
    Plan hash value: 1704212880
    ------------------------------------------------------------
    | Id  | Operation                           | Name         |
    ------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |              |
    |   1 |  COUNT STOPKEY                      |              |
    |   2 |   VIEW                              |              |
    |   3 |    SORT ORDER BY STOPKEY            |              |
    |   4 |     TABLE ACCESS BY INDEX ROWID     | CUSTOMERS    |
    |   5 |      BITMAP CONVERSION TO ROWIDS    |              |
    |   6 |       BITMAP AND                    |              |
    |   7 |        BITMAP CONVERSION FROM ROWIDS|              |
    |   8 |         SORT ORDER BY               |              |
    |   9 |          DOMAIN INDEX               | TEXT_IND     |
    |  10 |        BITMAP CONVERSION FROM ROWIDS|              |
    |  11 |         SORT ORDER BY               |              |
    |  12 |          INDEX RANGE SCAN           | CUSTOMERS_PK |
    ------------------------------------------------------------
    
    Zum Vergleich verwenden wir statt des einfachen Context Index nun den neuen CDI mit der oben angegebenen Syntax und führen die Abfrage noch einmal durch:
    
    SELECT /*+ first_rows(10) */ cust_id
    FROM (select cust_id, cust_first_name,cust_year_of_birth from customers
    WHERE contains (cust_first_name, 'A% or D% or N% or B%',1)>0  AND cust_id>100000 
    ORDER BY cust_year_of_birth, score(1))
    WHERE rownum<10
    
       CUST_ID
    ----------
        102011
        103921
        100199
        100930
        104242
        103080
        103187
        103412
        103684
    
    9 rows selected.
    
    Elapsed: 00:00:00.02
    
    Die Abfragezeit beträgt nur noch ein Sechstel der Zeit. Nun schauen wir uns noch den zugehörigen Ausführungsplan an:
     
    SELECT * FROM table (dbms_xplan.display_cursor());
    ...
    Plan hash value: 3210723938
    -------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time    |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |           |       |       |     5 (100)|         |
    |*  1 |  COUNT STOPKEY                 |           |       |       |            |         |
    |   2 |   VIEW                         |           |     1 |    13 |     5  (20)| 00:00:01|
    |*  3 |    SORT ORDER BY STOPKEY       |           |     1 |    28 |     5  (20)| 00:00:01|
    |   4 |     TABLE ACCESS BY INDEX ROWID| CUSTOMERS |     1 |    28 |     4   (0)| 00:00:01|
    |*  5 |      DOMAIN INDEX              | COMP_IND  |       |       |     4   (0)| 00:00:01|
    -------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    -------------------------------------------------------------------------------------------
       1 - filter(ROWNUM<10)
       3 - filter(ROWNUM<10)
       5 - access("CTXSYS"."CONTAINS"("CUST_FIRST_NAME",'A% or D% or N% or B%',1)>0)
    
    Da weniger oder keine DOCID->ROWID Transformationen für Sätze, die nicht in der finalen Ergebnisliste sind, notwendig sind, erhalten wir einen effizienteren Zugriff. Generell zeigt sich, dass grosse Ergebnismengen im Textindex in Verbindung mit den stark nachfilternden Indizes, am Besten im CDI abgebildet werden sollten.
    Mehr zur Tipps und Tricks in einem der nächsten Blogs.....

    Dienstag, 7. Juli 2009

    Mixed Queries in 11g

    MDATA Sections sind in 10g eingeführt worden, um gemischte Abfragen (auch mixed queries genannt) - also Abfragen mit Text- und relationalen Anteilen, besser handhaben zu können. Generell können damit kurze Textfelder(sogenannte Metadaten), die als Ganzes im Textindex indiziert wurden, einfach abgefragt werden. Mehr Informationen zur MDATA-Nutzung finden Sie Metadatensuche mit MDATA Blog und im MData Section und MULTI_COLUMN_DATASTORE Blog. Abfragen auf die Metadaten wie prod_list_price und flag sehen dann beispielsweise folgendermassen aus:
    
    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;
    
    
    Ein wichtiger Unterschied zu gewohntem Sectionverhalten ist, dass MDATA Bereiche transaktionell verändert werden können, ohne den Rest des Index zu beeinträchtigen bzw. zu re-indizieren. Nachteile dieser Technologie ist die Tatsache, dass nur auf Gleichheit abgfragt werden kann und zusätzlich die MDATA Werte als einziges Token behandelt und minimal normalisiert werden können (Whitespace-Entfernung etc). Daher ist in 11g eine weitere Form der Section Suche eingeführt worden - die SDATA Section (SDATA steht dabei für Structured Data). Die Indizierung der SDATA Section erlaubt Operationen wie Range Scans, Nutzung von Funktionen, Projektionen usw. So können neue Kombinationen aus Text und strukturierte Anteilen abgefragt werden. Um die Unterschiede aufzuzeigen, nehmen wir das Beispiel aus MData Section und MULTI_COLUMN_DATASTORE und verwenden dabei die neue SDATA Section. Wir belassen den MULTI_COLUMN_DATASTORE my_multi_pref, und erzeugen eine SDATA Section mit Namen prod_list_price.
    
    connect sh/sh
    execute ctx_ddl.drop_section_group('my_seg');
    begin
    ctx_ddl.create_section_group(group_name=>'my_seg',group_type=>'basic_section_group');
    ctx_ddl.add_sdata_section('my_seg','PROD_LIST_PRICE','prod_list_price', 'NUMBER');
    end;
    /
    DROP INDEX mdata_index;
    DROP INDEX sdata_index;
    CREATE INDEX sdata_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 = 'SDATA_INDEX';
    no rows selected
    
    
    Folgende Abfrageart mit dem SDATA-Operator ist nun möglich.
    
    SELECT prod_id, prod_list_price, prod_desc FROM products
    WHERE contains (prod_desc, 'Card AND SDATA(prod_list_price >= 69.99)') > 0;
    
       PROD_ID PROD_LIST_PRICE
    ---------- ---------------
    PROD_DESC
    --------------------------------------------------------------------------------
            25          112.99
    SIMM- 8MB PCMCIAII card
    
            26          149.99
    SIMM- 16MB PCMCIAII card
    
           138           69.99
    256MB Memory Card
    
    
    Untersucht man genauer die neuangelegten Objekte, wird man feststellen, dass ein zusätzliches Indexsegment, eine IOT-Tabelle mit Namen DR$SDATA_INDEX$S, erzeugt wurde.

    Nun stellt sich die Frage, ob das Ganze nicht einfacher zu bewerkstelligen ist, ohne zusätzlich Sections zu verwenden. Die Antwort dazu gibt die neue Composite Domain Index Technologie. Mit nur einem CREATE INDEX-Kommando ohne zusätzliche SDATA Sections kann dies erreicht werden. Folgendes Kommando zeigt die Implementierung in unserem Fall. Die FILTER BY Klausel ermöglicht dabei die Teilabfrage auf die Spalte PROD_LIST_PRICE vollständig im Text-Index durchzuführen.
    
    DROP INDEX sdata_index;
    
    CREATE INDEX comp_index ON products(prod_desc)
    INDEXTYPE IS ctxsys.context
    FILTER BY prod_list_price;
    
    
    Ein kurzer Blick auf die erzeugten Objekte, gibt den Hinweis darauf, dass die SDATA Technologie offensichtlich als Grundlage dient, da wir nun eine zusätzliche IOT-Tabelle DR$COMP_INDEX$S besitzen. Die Abfragen können nun im gewohnten Stil ohne Verwendung von speziellen Operatoren verwendet werden.
    
    SELECT prod_id, prod_list_price, prod_desc 
    FROM products
    WHERE contains (prod_desc, 'Card')>0  AND prod_list_price <= 69.99;
    
       PROD_ID PROD_LIST_PRICE
    ---------- ---------------
    PROD_DESC
    --------------------------------------------------------------------------------
           136           32.99
    64MB Memory Card
    
           137           52.99
    128MB Memory Card
    
           138           69.99
    256MB Memory Card
    
     
    Mehr zur Composite Domain Index Technik in einem der nächsten Blogs.....

    Beliebte Postings