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.....