Histogramy wykorzystujemy dość powszechnie w celu mniej lub bardziej precyzyjnego szacowania selektywności predykatów użytych w zapytaniach (oczywiście dla kolumn o nierównomiernym rozkładzie statystycznym). W pewnych przypadkach możemy jednak paść ofiarą uproszczenia zastosowanego przez Oracle podczas generowania histogramów, czego efektem będą złe/bardzo złe szacunki selektywności. Uproszczenie to dotyczy kolumn typu tekstowego i polega na uwzględnianiu w histogramie wyłącznie pierwszych 64 znaków (lub 32 znaków w wersji Oracle Database 11g) tekstu kolumny. W rezultacie, różne wartości posiadające jednakowy prefiks 64-znakowy będą potraktowane jako ta sama wartość zarówno w histogramie, jak i podczas szacowania selektywności.
Przyjrzyjmy się następującemu przykładowi. Tabela H_TEST zawiera 100 rekordów i dwie kolumny – A i B. Wartości obu kolumn są niepowtarzalne w obrębie tabeli, składają się jednak z 64-znakowej części stałej, której towarzyszy niepowtarzalna liczba całkowita – w kolumnie A umieszczona jest ona na początku tekstu, w kolumnie B – na końcu (zatem pierwsze 64 znaki kolumny B są jednakowe w każdym rekordzie). Generujemy dla obu kolumn histogramy częstotliwościowe, a następnie obserwujemy szacowaną selektywność predykatów (kolumnę ROWS planu wykonania), których celem jest wybranie jednego rekordu z tabeli (prawidłowa selektywność = 1%). Pierwszy predykat posługuje się kolumną A, drugi – kolumną B.
SQL> select * from H_TEST;
A B
---------- ----------
1XXX...XXX XXX...XXX1
2XXX...XXX XXX...XXX2
...
100 rows selected.
SQL> exec dbms_stats.gather_table_stats('','H_TEST', method_opt=>'FOR ALL COLUMNS SIZE 100');
SQL> select a from h_test where a='1XXX...XXX';
...
Execution Plan
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 67 | 2 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL|H_TEST| 1 | 67 | 2 (0) | 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
...
1 rows processed
SQL> select b from h_test where b='XXX...XXX1';
...
Execution Plan
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 6700 | 2 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL|H_TEST| 100 | 6700 | 2 (0) | 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
...
1 rows processed
Jak widać, optymalizator zapytań prawidłowo szacuje selektywność predykatu opartego na kolumnie A – 1%, natomiast całkowicie błędnie zachowuje się w przypadku predykatu opartego na kolumnie B, spodziewając się, że predykat zostanie spełniony przez 100% rekordów (gdyż 100% rekordów ma jednakowy 64-znakowy prefiks…).
W jaki sposób możemy unikać konsekwencji takiego zachowania się serwera? Niepotrzebnie nie prefiksować kluczy długimi stałymi łańcuchami znakowymi, jeśli klucz musi być długi, to niech część stała znajduje się na jego końcu, dekomponować długie, złożone wartości tekstowe (np. adresy URL) na wiele kolumn tabeli, stosować indeksy funkcyjne oparte o funkcję SUBSTR(), itp.