Full Index Scan jest w Oracle pełnym odczytem indexu czyli pobraniem wszystkich danych zgodnie z kolejnością klucza. Index Full Scan może wyeliminować operację sortowanie ponieważ dane są już uporządkowane, dotyczy to sortowania ASC jak i DESC.

Aby lepiej zrozumieć działanie indexu warto wcześniej zapoznać się z kursem Oracle SQL: Index w Oracle

Ten wpis jest częścią Kurs Oracle SQL

Optymalizator bazy danych Oracle wykorzysta FULL SCAN INDEXu gdy:

  • Warunki w WHERE odwołują się do kolumn w indeksie. Te kolumny nie muszą zawierać kolumny wiodącej.
  • Zapytanie zawiera ORDER BY (ASC jak i DESC) w indeksowanych kolumnach, które nie są kolumnami nullowymi (praktycznie warunek obowiązkowy)
  • Nie podano warunku WHERE ale:
    • Wszystkie kolumny w zapytaniu znajdują się w indeksie.
    • Co najmniej jedna indeksowana kolumna nie jest kolumną nullową

Aby zrozumieć skąd biorą się powyższe wymagania do wykorzystania FULL INDEX SCAN przejdźmy algorytm jego działania:

  • Baza danych odczytuje blok wejściowy „root block”
  • Następnie baza danych przechodzi w dół do pierwszego bloku gałęzi „branch block” w lewo lub w prawo w zależności od kierunku sortowania
  • Dalej baza schodzi w dól po kolejnych skrajnych blokach gałęzi aż osiągnie pierwszy blok danych „leaf block”
  • Następnie baza danych skanuje blok liścia
  • Po przeszukaniu bloku liścia następuje przejściu na kolejny blok liścia aż do momentu zakończenia indexu lub spełnieniu ostatniego warunku z klauzuli WHERE.

Powyższy algorytm przedstawiłem na poniższym schemacie

Index-Full-Scan
Oracle INDEX FULL SCAN

Gdy spojrzymy na powyższy schemat można odnieść wrażenie, że Full Index Scan działa jak Range Index Scan ale po całym indexie. I w sposobie działania oba skanowania są bardzo podobne. Najprawdopodobniej optymalizator wybierze Range Scan gdy pobierać będziemy do ok.20% rekordów z indexu. Gdy pobierać będziemy większą ilość danych z indexu zapewne optymalizator Oracla wybierze Fast Full Scan a raczej w niektórych sytuacjach wybierze Full Scan. Żeby łatwiej zrozumieć dlaczego Full Scan jest rzadko wybieranym skanowaniem prześledźmy przypadki testowe.

Dane testowe

Przygotowanie danych rozpoczniemy od stworzenia tabeli:

CREATE TABLE TEST_TABLE (
    ID NUMBER GENERATED ALWAYS AS IDENTITY
  , FOO VARCHAR2(255)
  , BAR NUMBER
  , FIS NUMBER
  , CONSTRAINT ID_PK PRIMARY KEY (ID)
);

Z danymi:


BEGIN
  FOR I IN 0 .. 1000000
  LOOP
    INSERT INTO TEST_TABLE (
        FOO
      , BAR
      , FIS
    ) VALUES (
        'ACME: ' || MOD(I, 10)
      ,  MOD(I, 10000)
      ,  DECODE(MOD(I, 1000), 1, I, NULL)
    );
  END LOOP;
END;
/

COMMIT;

Statystykami:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','TEST_TABLE');

oraz zwykłym indexem na kolumnie FIS:

CREATE INDEX TEST_TABLE_FIS_IDX ON TEST_TABLE(FIS);

Na uwagę zasługuje kolumna FIS która w większości jest kolumną NULLową. Dzięki temu zabiegowi index będzie stosunkowo niewielki ponieważ w indeksie przechowywane są jedynie wartości nie nullowe.

Index Full Scan

Posiadając już wiedzę o tym jak działa full scan oraz posiadając środowisko testowe możemy przystąpić do testów.

Na pierwszy rzut zobaczmy co się stanie gdy pobierzemy wszystkie dane jakie są w tabeli bez względu na warunek WHERE:

SELECT
  FIS
FROM TEST_TABLE;

oraz plan:

FULL SCAN TEST_TABLE
FULL SCAN TEST_TABLE

Jak widzimy przy próbie pobrania wszystkich danych optymalizator Oracle wybrał FULL SCAN ale na tabeli a nie indexie. Jest to zrozumiałe ponieważ w indeksie posiadamy dane bez nulli a w warunku nie ograniczyliśmy się do wartości nie nullowych. Zatem zobaczmy co się stanie gdy dodamy ograniczenie na wartość FIS IS NOT NULL:

SELECT
  FIS
FROM TEST_TABLE
WHERE 1=1
  AND FIS IS NOT NULL;

oraz plan:

FAST FULL SCAN
FAST FULL SCAN

Jak widzimy w tym przypadku optymalizator wybrał FAST FULL SCAN a nie FULL SCAN i tu można zadać pytanie dlaczego skoro dane które chcemy pobrać są w indexie, nie pobieramy danych nullowych nie mamy żadnych innych ograniczeń. Nim odpowiem na to pytanie zobaczmy jeszcze jeden przykład:

SELECT
  FIS
FROM TEST_TABLE
WHERE 1=1
  AND FIS IS NOT NULL
ORDER BY FIS;

oraz plan:

ORACLE FULL INDEX SCAN
FULL INDEX SCAN

Wreszcie udał się wywołać FULL INDEX SCAN jednak dopiero po dodaniu sortowania. Zauważmy, że sortowania nie widać w planie zapytania podobnie jak w przypadku range scan. Dzieje się tak ponieważ index jest posortowaną strukturą dzięki czemu baza nie musi wykonywać dodatkowej operacji sortowania danych.

W tym wpisie nie chcę opisywać dokładnie działania fast full scan ale należy wiedzieć, że FAST FULL SCAN przy skanowaniu całej tabeli pobiera dane znacznie szybciej niż FULL SCAN. Dlaczego (bardzo skrótowo): przy zastosowaniu fast full scan baza danych wykorzystuje mechanizm pobierania danych z wielu bloków za pomocą jeden operacji I/O. Baza Oracle pobiera wtedy dane tak jak są posortowane fizycznie a nie logicznie(wg. klucza). Dokładny opis działania tego mechanizmu również opiszę. Sytuacją kiedy zwykły full scan jest szybszy od fast full scan jest skanowanie z wykorzystaniem posortowanych wartości.

Podobnie jak w przypadku RANGE SCAN sortowanie odwrotne DESC też nie zwiększa kosztu:

SELECT
  FIS
FROM TEST_TABLE
WHERE 1=1
  AND FIS IS NOT NULL
ORDER BY FIS DESC;

oraz plan:

FULL INDEX SCAN DESC
FULL INDEX SCAN DESC

Na planie zapytania koszt 4 czyli taki jak w przypadku sortowania ASC. Podobnie jak w index range scan tak i w full index scan kierunek skanowania nie ma znaczenia dla kosztu.

FULL INDEX SCAN zostanie wykorzystany przez bazę zawsze gdy wymagane będzie sortowanie czyli przy operacjach np. GROUP BY

SELECT
  FIS
FROM TEST_TABLE
WHERE 1=1
  AND FIS IS NOT NULL
GROUP BY FIS;

oraz plan:

FFS przy GROUP BY
FFS przy GROUP BY

FULL INDEX SCAN (MIN/MAX)

Istnieje jeszcze specjalny typ FULL INDEX SCAN wykorzystywany w przypadku wyszukania MIN/MAX. Pokazuje go poniższy przykład:

SELECT
  MIN(FIS)
FROM TEST_TABLE
WHERE 1=1
  AND FIS IS NOT NULL;

oraz plan:

FULL SCNA (MIN/MAX)
FULL SCNA (MIN/MAX)

Przy wyszukaniu wartości MIN/MAX baza wykorzystuje specjalny typ FULL SCAN(MIN/MAX). Jako, że dane w indexie są posortowane to aby wyszukać wartość MIN lub MAX wystarczy znaleźć pierwszy lub ostatni wiersz z indexu. Co ważne baza może odwiedzić tylko jedną stronę indexu. Więc jeżeli w jednym zapytaniu użyjemy MIN oraz MAX baza nie wykorzysta tego typu skanowania:

SELECT
    MIN(FIS)
  , MAX(FIS)
FROM TEST_TABLE
WHERE 1=1
  AND FIS IS NOT NULL;

oraz plan:

Fast full scan dla min/max
Fast full scan dla min/max

I przy wyszukaniu MIN oraz MAX w jednym zapytaniu baza używa FAST FULL SCAN zmiast FULL SCAN(MIN/MAX)

Podsumowanie

FULL INDEX SCAN jest skanowaniem pełnego indexu w posortowanej kolejności względem klucza.

W działaniu podobny jest do range scan ale pobiera znacznie więcej danych.

Kiedy pobieramy dużą część danych z indexu bez względu na kolejność optymalizator wybierze raczej fast full scan.

W przypadku wyszukiwania wartości MIN/MAX baza wykorzystuje specjalny typ skanowania FULL SCAN (MIN/MAX) jednak tylko dla jednego wyszukania.

4 komentarze

    1. Nie znalazłem nigdzie w dokumentacji innej informacji jak ta, że wtedy gdy optymalizatorowi wyjdzie, że jedno skanowanie jest mniej kosztowne od drugiego. Jednak wartość ok. 20% a bardziej między 10-30% wynika z praktyki i testów moich oraz innych 🙂

      OracleDev

Dodaj komentarz

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