Oracle Database – indeks B*-drzewo a operator LIKE

Czy indeks B*-drzewo może być użyty do wyszukiwania opartego na predykacie LIKE? To zależy. Przyjrzyjmy się planom wykonania dwóch zapytań do przykładowej tabeli DUZA_TEKSTOWA (16 tysięcy rekordów, niepowtarzalne wartości w kolumnie tekstowej A).

SQL> create index demo_idx1 on duza_tekstowa(a);
...
SQL> select * from duza_tekstowa where a like 'abcd%';
...
Plan wykonywania
-----------------------------------------------------------------
| Id | Operation        | Name      | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
| 0  | SELECT STATEMENT |           | 1    | 7     | 2 (0)      |
|* 1 | INDEX RANGE SCAN | DEMO_IDX1 | 1    | 7     | 2 (0)      |
-----------------------------------------------------------------
Statystyki
----------------------------------------------------------
127 recursive calls
0 db block gets
18 consistent gets
0 physical reads
...
SQL> select * from duza_tekstowa where a like '%abcd';
...
Plan wykonywania
---------------------------------------------------------------------
| Id | Operation        | Name          | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0  | SELECT STATEMENT |               | 819  | 5733  | 10 (0)     |
|* 1 | TABLE ACCESS FULL| DUZA_TEKSTOWA | 819  | 5733  | 10 (0)     |
---------------------------------------------------------------------
Statystyki
----------------------------------------------------------
1 recursive calls
0 db block gets
32 consistent gets
0 physical reads

W pierwszym przypadku (like ‘abcd%’) został użyty indeks B*-drzewo oparty na kolumnie A. To dość oczywiste, gdyż tego typu zapytanie prefiksowe to w istocie selekcja zakresowa – taka sama jak wykonywana operatorem BETWEEN. Selektywność podanego predykatu była wystarczająco dobra, aby opłacało się użyć indeksu.

W przypadku drugim (like ‘%abcd’) indeks nie został użyty, bo nie mógł być użyty. Uporządkowana leksykograficznie struktura liści indeksu B*-drzewo nijak nie wspomaga tzw. wyszukiwania postfiksowego. Podobnie byłoby w sytuacji wyszukiwania według fragmentu ciągu (np. ‘like ‘%abcd%’).

Czy zatem nie ma żadnego sposobu na wykorzystanie indeksu B*-drzewo do wyszukiwania postfiksowego (like ‘%abcd’)? Spróbujmy utworzyć indeks funkcyjny oparty na funkcji REVERSE, a zapytanie postfiksowe zamieńmy na prefiksowe (like reverse(‘abcd’)||‘%’). Działa?

SQL> create index demo_idx2 on duza_tekstowa(reverse(a));
...
SQL> select * from duza_tekstowa where reverse(a) like reverse('abcd')||'%';
...
Plan wykonywania
-------------------------------------------------------------------------------
| Id | Operation                  | Name          | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0  | SELECT STATEMENT           |               | 819  | 5733  | 9 (0)      |
| 1  | TABLE ACCESS BY INDEX ROWID| DUZA_TEKSTOWA | 819  | 5733  | 9 (0)      |
|* 2 | INDEX RANGE SCAN           | DEMO_IDX2     | 147  |       | 2 (0)      |
-------------------------------------------------------------------------------
Statystyki
----------------------------------------------------------
24 recursive calls
0 db block gets
4 consistent gets
1 physical reads

A co z wyszukiwaniem według fragmentu ciągu (np. ‘like ‘%abcd%’)? Tutaj żadne sztuczki z indeksem B*-drzewo nie pomogą. Trzeba sięgnąć po specjalizowane indeksy tekstowe i operatory tekstowe Oracle Text. Np. taki:

SQL> create index demo_idx3 on duza_tekstowa(a) indextype is ctxsys.context;
...
SQL> select * from duza_tekstowa where contains(a,'abcd')>0;
...
Plan wykonywania
-------------------------------------------------------------------------------
| Id | Operation                  | Name          | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0  | SELECT STATEMENT           |               | 8    | 56    | 5 (0)      |
| 1  | TABLE ACCESS BY INDEX ROWID| DUZA_TEKSTOWA | 8    | 56    | 5 (0)      |
|* 2 | DOMAIN INDEX               | DEMO_IDX3     |      |       | 4 (0)      |
-------------------------------------------------------------------------------

 

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *