So können Queries die aus
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.....
