Wybierając kolejność kolumn indeksu, nadrzędnym problemem jest:
Czy w moich zapytaniach są predykaty (równości) względem tej kolumny?
Jeśli kolumna nigdy nie pojawia się w klauzuli where, nie warto indeksować (1)
OK, więc masz tabelę i zapytania dotyczące każdej kolumny. Czasami więcej niż jeden.
Jak decydujesz, co indeksować?
Spójrzmy na przykład. Oto tabela z trzema kolumnami. Jedna zawiera 10 wartości, kolejna 1000, ostatnia 10.000:
create table t(
few_vals varchar2(10),
many_vals varchar2(10),
lots_vals varchar2(10)
);
insert into t
with rws as (
select lpad(mod(rownum, 10), 10, '0'),
lpad(mod(rownum, 1000), 10, '0'),
lpad(rownum, 10, '0')
from dual connect by level <= 10000
)
select * from rws;
commit;
select count(distinct few_vals),
count(distinct many_vals) ,
count(distinct lots_vals)
from t;
COUNT(DISTINCTFEW_VALS) COUNT(DISTINCTMANY_VALS) COUNT(DISTINCTLOTS_VALS)
10 1,000 10,000
Są to liczby wypełnione zerami. Pomoże to zwrócić uwagę na kompresję później.
Masz trzy typowe zapytania:
select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where few_vals = '0000000001';
select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where lots_vals = '0000000001';
select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where lots_vals = '0000000001'
and few_vals = '0000000001';
Co indeksujesz?
Indeks tylko kilku_wartości jest tylko nieznacznie lepszy niż pełny skan tabeli:
select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where few_vals = '0000000001';
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 61 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 61 |
| 2 | VIEW | VW_DAG_0 | 1 | 1000 | 1000 |00:00:00.01 | 61 |
| 3 | HASH GROUP BY | | 1 | 1000 | 1000 |00:00:00.01 | 61 |
| 4 | TABLE ACCESS FULL| T | 1 | 1000 | 1000 |00:00:00.01 | 61 |
-------------------------------------------------------------------------------------------
select /*+ index (t (few_vals)) */
count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where few_vals = '0000000001';
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 58 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 58 |
| 2 | VIEW | VW_DAG_0 | 1 | 1000 | 1000 |00:00:00.01 | 58 |
| 3 | HASH GROUP BY | | 1 | 1000 | 1000 |00:00:00.01 | 58 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1000 | 1000 |00:00:00.01 | 58 |
| 5 | INDEX RANGE SCAN | FEW | 1 | 1000 | 1000 |00:00:00.01 | 5 |
-------------------------------------------------------------------------------------------------------------
Jest więc mało prawdopodobne, aby warto było samodzielnie indeksować. Zapytania na Lot_vals zwracają kilka wierszy (w tym przypadku tylko 1). Jest to zdecydowanie warte indeksowania.
Ale co z zapytaniami do obu kolumn?
Jeśli indeksujesz:
( few_vals, lots_vals )
LUB
( lots_vals, few_vals )
Podchwytliwe pytanie!
Odpowiedź nie jest żadna.
Jasne, że kilka_wartości jest długim ciągiem. Dzięki temu możesz uzyskać dobrą kompresję. I możesz (może) uzyskać skanowanie pomijania indeksu dla zapytań przy użyciu (kilka_wartości, wiele_wartości), które mają predykaty tylko na wartości_wielkości. Ale mnie tu nie ma, chociaż działa znacznie lepiej niż pełny skan:
create index few_lots on t(few_vals, lots_vals);
select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where lots_vals = '0000000001';
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 61 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 61 |
| 2 | VIEW | VW_DAG_0 | 1 | 1 | 1 |00:00:00.01 | 61 |
| 3 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 61 |
| 4 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 61 |
-------------------------------------------------------------------------------------------
select /*+ index_ss (t few_lots) */count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where lots_vals = '0000000001';
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 13 | 11 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 13 | 11 |
| 2 | VIEW | VW_DAG_0 | 1 | 1 | 1 |00:00:00.01 | 13 | 11 |
| 3 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 13 | 11 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 1 |00:00:00.01 | 13 | 11 |
| 5 | INDEX SKIP SCAN | FEW_LOTS | 1 | 40 | 1 |00:00:00.01 | 12 | 11 |
----------------------------------------------------------------------------------------------------------------------
Czy lubisz hazard? (2)
Nadal potrzebujesz indeksu z Lot_Vals jako wiodącą kolumną. I przynajmniej w tym przypadku indeks złożony (kilka, partii) wykonuje tyle samo pracy, co jeden na jednym (partiach)
select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where lots_vals = '0000000001'
and few_vals = '0000000001';
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |
| 2 | VIEW | VW_DAG_0 | 1 | 1 | 1 |00:00:00.01 | 3 |
| 3 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 3 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 1 |00:00:00.01 | 3 |
| 5 | INDEX RANGE SCAN | FEW_LOTS | 1 | 1 | 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------
create index lots on t(lots_vals);
select /*+ index (t (lots_vals)) */count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where lots_vals = '0000000001'
and few_vals = '0000000001';
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
| 2 | VIEW | VW_DAG_0 | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
| 3 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
| 5 | INDEX RANGE SCAN | LOTS | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
----------------------------------------------------------------------------------------------------------------------
Zdarzają się przypadki, w których indeks złożony oszczędza 1-2 IO. Ale czy warto mieć dwa indeksy dla tego oszczędzania?
I jest inny problem z indeksem złożonym. Porównaj współczynnik klastrowania dla trzech indeksów, w tym LOTS_VALS:
create index lots on t(lots_vals);
create index lots_few on t(lots_vals, few_vals);
create index few_lots on t(few_vals, lots_vals);
select index_name, leaf_blocks, distinct_keys, clustering_factor
from user_indexes
where table_name = 'T';
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
FEW_LOTS 47 10,000 530
LOTS_FEW 47 10,000 53
LOTS 31 10,000 53
FEW 31 10 530
Zauważ, że współczynnik grupowania dla kilku_botów jest 10-krotnie wyższy niż dla wielu i wielu_lotów! I to jest na stole demo z doskonałym klastrowaniem na początek. W rzeczywistych bazach danych efekt będzie prawdopodobnie gorszy.
Co w tym takiego złego?
Czynnik grupowania jest jednym z kluczowych czynników decydujących o tym, jak „atrakcyjny” jest indeks. Im wyższy, tym mniej prawdopodobne jest, że optymalizator go wybierze. Zwłaszcza jeśli lot_vals nie jest tak naprawdę unikalny, ale zwykle ma kilka wierszy na wartość. Jeśli masz pecha, może to wystarczyć, aby optymalizator uznał, że pełne skanowanie jest tańsze ...
OK, więc indeksy złożone z parametrami kilka_wartości i wartości_lotów mają tylko zalety przewagi przypadku.
Co z zapytaniami filtrującymi kilka_wartości i wiele_wartości?
Indeksy z pojedynczymi kolumnami dają tylko niewielkie korzyści. Ale w połączeniu zwracają niewiele wartości. Tak więc indeks złożony jest dobrym pomysłem. Ale w którą stronę?
Jeśli umieścisz kilka pierwszych, skompresowanie wiodącej kolumny spowoduje jej zmniejszenie
create index few_many on t(many_vals, few_vals);
create index many_few on t(few_vals, many_vals);
select index_name, leaf_blocks, distinct_keys, clustering_factor
from user_indexes
where index_name in ('FEW_MANY', 'MANY_FEW');
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
FEW_MANY 47 1,000 10,000
MANY_FEW 47 1,000 10,000
alter index few_many rebuild compress 1;
alter index many_few rebuild compress 1;
select index_name, leaf_blocks, distinct_keys, clustering_factor
from user_indexes
where index_name in ('FEW_MANY', 'MANY_FEW');
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
MANY_FEW 31 1,000 10,000
FEW_MANY 34 1,000 10,000
Przy mniejszej liczbie różnych wartości w kolumnie wiodącej kompresuje się lepiej. Więc jest niewiele mniej pracy na odczytanie tego indeksu. Ale tylko nieznacznie. Oba są już dobrym kawałkiem mniejszym niż oryginał (zmniejszenie rozmiaru o 25%).
I możesz pójść dalej i skompresować cały indeks!
alter index few_many rebuild compress 2;
alter index many_few rebuild compress 2;
select index_name, leaf_blocks, distinct_keys, clustering_factor
from user_indexes
where index_name in ('FEW_MANY', 'MANY_FEW');
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
FEW_MANY 20 1,000 10,000
MANY_FEW 20 1,000 10,000
Teraz oba indeksy powróciły do tego samego rozmiaru. Zauważ, że wykorzystuje to fakt, że istnieje relacja między nielicznymi a wieloma. Ponownie jest mało prawdopodobne, że zobaczysz tego rodzaju korzyści w prawdziwym świecie.
Do tej pory mówiliśmy tylko o kontrolach równości. Często z indeksami złożonymi będziesz mieć nierówność względem jednej z kolumn. np. zapytania takie jak „otrzymaj zamówienia / przesyłki / faktury dla klienta w ciągu ostatnich N dni”.
Jeśli masz tego rodzaju zapytania, potrzebujesz równości z pierwszą kolumną indeksu:
select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where few_vals < '0000000002'
and many_vals = '0000000001';
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 12 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 12 |
| 2 | VIEW | VW_DAG_0 | 1 | 10 | 10 |00:00:00.01 | 12 |
| 3 | HASH GROUP BY | | 1 | 10 | 10 |00:00:00.01 | 12 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 10 | 10 |00:00:00.01 | 12 |
| 5 | INDEX RANGE SCAN | FEW_MANY | 1 | 10 | 10 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------
select count (distinct few_vals || ':' || many_vals || ':' || lots_vals )
from t
where few_vals = '0000000001'
and many_vals < '0000000002';
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST -PREDICATE'));
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 12 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 12 | 1 |
| 2 | VIEW | VW_DAG_0 | 1 | 2 | 10 |00:00:00.01 | 12 | 1 |
| 3 | HASH GROUP BY | | 1 | 2 | 10 |00:00:00.01 | 12 | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 2 | 10 |00:00:00.01 | 12 | 1 |
| 5 | INDEX RANGE SCAN | MANY_FEW | 1 | 1 | 10 |00:00:00.01 | 2 | 1 |
----------------------------------------------------------------------------------------------------------------------
Zauważ, że używają przeciwnego indeksu.
TL; DR
- Kolumny z warunkami równości powinny być pierwsze w indeksie.
- Jeśli w zapytaniu masz wiele kolumn o równości, umieszczenie na początku tej z najmniejszą liczbą różnych wartości zapewni najlepszą przewagę kompresji
- Chociaż skany indeksów są możliwe, musisz mieć pewność, że pozostanie to opłacalną opcją w dającej się przewidzieć przyszłości
- Indeksy złożone, w tym prawie unikalne kolumny, dają minimalne korzyści. Upewnij się, że naprawdę musisz zapisać 1-2 IO
1: W niektórych przypadkach warto uwzględnić kolumnę w indeksie, jeśli oznacza to, że wszystkie kolumny w zapytaniu znajdują się w indeksie. Umożliwia to skanowanie tylko indeksu, więc nie musisz uzyskiwać dostępu do tabeli.
2: Jeśli masz licencję na diagnostykę i strojenie, możesz zmusić plan do pominięcia skanowania za pomocą SQL Plan Management
ADDEDNDA
PS - cytowane tam dokumenty pochodzą z 9i. To naprawdę stare. Zostałbym przy czymś nowszym