Czy przy definiowaniu indeksów są jakieś korzyści w pewnym uporządkowaniu kolumn?

13

Na przykład, jeśli mam dwa indeksy:

CREATE INDEX IDX_1 ON MY_TABLE_1
 (ITEM, DATE, LOCATION)
 COMPUTE STATISTICS;

CREATE INDEX IDX_2 ON MY_TABLE_1
 (DATE, LOCATION, ITEM)
 COMPUTE STATISTICS;

Czy to by stało się IDX_2zbędne? Jeśli nie, jak określić kolejność deklarowania kolumn?

Czy powinienem dostosowywać indeksy do regularnych zapytań?

oracle index

— Lewis Norton
źródło

12

Tak, korzyść pojawia się, gdy chcesz wykonać zapytanie dotyczące części indeksu. Jeśli najpierw umieścisz predykaty używane w części, indeksu można użyć do zapytań, które dotyczą tych predykatów, ale nie wszystkich kolumn w indeksie.

Ponadto, chyba że masz inne wymagania, może pomóc na pierwszym miejscu postawić najbardziej selektywne predykaty, ponieważ może to skrócić operacje wyszukiwania indeksu szybciej.

W twoim przypadku IDX_2niekoniecznie jest zbędny w zależności od charakteru zapytań na stole. Jednak może nie być konieczne dołączenie wszystkich kolumn. Jeśli, na przykład, wiele zapytań przez Ciebie zrobić locationi datewtedy IDX_2może być przydatne, by rozwiązać te pytania, jak IDX_1nie jest w odpowiedniej kolejności, aby być przydatne do tego. Może się jednak okazać, że itemjest to zbędne IDX_2.

Od wersji 9i Oracle wprowadziło operatora „przeskocz skanowanie” , w którym zapytania o kolumny indeksu końcowego można wydajniej wyszukiwać, co może zmniejszyć zapotrzebowanie na dodatkowe indeksy tego rodzaju.

W bardziej konkretnym przypadku, jeśli zapytanie odbywa się za itempomocą locationi datenie potrzebujesz żadnych innych kolumn, wówczas zapytanie można całkowicie rozwiązać za pomocą indeksu bez konieczności czytania czegokolwiek z tabeli. Możesz także budować indeksy obejmujące dołączone nieindeksowane kolumny. Jeśli wszystkie potrzebne kolumny można rozwiązać z indeksu obejmującego, zapytanie w ogóle nie musi dotykać głównej tabeli.

Wreszcie, w odpowiedzi na twoje ostatnie pytanie: Jeśli masz zestaw regularnie używanych zapytań, które zużywają dużo zasobów i można je dostroić za pomocą indeksu, z pewnością warto to rozważyć. Jednak utrzymanie indeksów wiąże się z narzutem na wstawki, więc trzeba będzie zmniejszyć wydajność zapytań w stosunku do narzutu, jaki indeksy nakładają na operacje wstawiania lub aktualizacji.

— ConcernedOfTunbridgeWells
źródło

3

@ConcernedOfTunbridgeWells: Alternatywnym podejściem jest użycie kompresji klucza indeksu i prowadzenie mniej selektywnych (mniej wyraźnych wartości) kolumn. Pomaga to w uzyskaniu mniejszego indeksu, a jednocześnie pozwala na prawidłowe działanie przeskakiwania.

— Adam Musch

2

Inną rzeczą, którą należy wziąć pod uwagę, są kolumny z dużą liczbą wartości zerowych.

Jeśli w kolumnach tych podano po indeksie dowolne kolumny, wartości zerowe muszą zostać zindeksowane. W przeciwnym razie, jak zwykle, wartości zerowe nie są indeksowane (oczywiście przy założeniu, że używasz indeksu b-drzewa).

Tak więc, jeśli masz kolumny z dużą liczbą wartości zerowych, umieszczenie ich na końcu indeksu może zaoszczędzić znaczną ilość miejsca na dysku.

— Michał Tenenberg
źródło