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