Index Unique Scan jest skanowaniem indeksu zwracającym jeden lub zero kluczy z indeksem. Oznacza to, że klucz indeksu musi być unikalny. O ogólnej budowie indexu oraz jego klucza dowiesz się w tym kursie.

Ten wpis jest częścią Kurs Oracle SQL

Aby użyć skanowania Index Unique Scan wymagany jest nie tylko unikalny klucz w indeksie ale również odpowiednie zbudowanie indexu oraz zapytanie które zapewni zwrócenie tylko jednego rekordu.

Tworzenie unikalnego indexu zapewnia się poprzez klauzule UNIQUE

CRETAE UNIQUE INDEX

Natomiast samo zapytanie musi zawierać operator równości

WHERE foo = :P

Jeżeli powyższe warunki zostały spełnione optymalizator powinien skorzystać z Index Unique Scan. Skanowanie to przeszukuje klucz indeksu w poszukiwaniu wartości z warunku równości. Po znalezieniu klucza z wartości warunku przestaje dalej przeszukiwać index ponieważ nie istnieje więcej kluczy o tej wartości.

Przykład użycia INDEX UNIQUE SCAN

Przygotowanie danych

Pierwszym krokiem do pokazania i wykorzystania indeksu jest przygotowanie odpowiedniej tabeli z danymi na której będziemy testować indexy.

Do stworzenia przykładowej tabeli posłuży zapytanie:

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

Testowa tabela TEST_TABLE ma kolumny o różnych typach. Pierwsza kolumna ID jest również kluczem głównym ID_PK tej tabeli, FOO jest typu VARCHAR2 który będzie nieunikalną wartością oraz dodatkowo BAR typu NUMBER który wypełnimy liczbami unikalnymi. Nałożenie na kolumnę ID klucza głównego powoduje również stworzenie indeksu typu UNIQUE na tą kolumnę. Można to zobaczyć patrząc na indeksy nałożone na tą tabelę:

SELECT 
    IDX.INDEX_NAME
  , IDX.INDEX_TYPE
  , IDX.TABLE_NAME
  , IDX.UNIQUENESS 
  , IDX.STATUS
  , COL.COLUMN_NAME
FROM ALL_INDEXES IDX
JOIN ALL_IND_COLUMNS COL ON COL.INDEX_NAME = IDX.INDEX_NAME
WHERE 1 = 1
  AND IDX.TABLE_NAME = 'TEST_TABLE';

Po powyższym widzimy, że samo utworzenie tabeli oraz nałożenie PK spowodowało utworzenie INDEX typu UNIQUE.

Kolejnym krokiem będzie wypełnienie tabeli danymi. Wypełnijmy ją danymi w liczbie 1 000 000 rekordów poprzez poniższy skrypt PL/SQL:

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

COMMIT;

Jeszcze statystyki dla tabeli:

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

W tym momencie mamy przygotowane wszystkie dane niezbędne do testów:

  • Tabela utworzona
  • Rekordy dodane w ilości ok 1mln (jakieś 25MB)
  • Statystyki przeliczone

Wyszukanie rekordu

Żeby mieć porównanie czy index cokolwiek zmienia w planie zapytania i jego koszcie musimy mieć punkt odniesienia. Punktem odniesienia w tym przypadku będzie wyszukanie rekordu o wartości bar równej 500 000, tak jak w poniższym zapytaniu:

SELECT 
    BAR
FROM TEST_TABLE
WHERE 1 = 1
  AND BAR = 500000;

Bez indexu

Plan zapytania bez indexu:

Na powyższym planie widzimy wykonanie przeszukania poprzez FULL SCAN bezpośrednio na tabeli. Szacowany koszt takiego wyszukania wynosi 867

Z indexem non unique

Załóżmy teraz zwykły index i zobaczmy, czy plan się poprawi:

CREATE INDEX TEST_TABLE_BAR_IDX ON TEST_TABLE(BAR);

Plan zapytania z indexem:

Jak widać nałożenie zwykłego indexu znacznie poprawiło koszt zapytania. Z 867 zeszliśmy do kosztu 3. Jednak po planie widać, że użyty został RANGE SCAN a nie INDEX UNIQUE SCAN pomimo, że dane w tabeli BAR są unikalne. Dzieje się tak ponieważ założyliśmy zwykłī index NON UNIQUE co oznacza, że w dowolnym momencie możemy dodać rekord z zdublowaną wartością BAR. Skoro baza nie może zapewnić, że wszystkie rekordy są unikalne to nie użyje również INDEX UNIQUE SCAN.

Z indexem unique

Aby użyć INDEX UNIQE SCAN należy nałożyć index z klauzulą UNIQUE jak poniżej:

CREATE UNIQUE INDEX TEST_TABLE_BAR_U_IDX ON TEST_TABLE(BAR);

I zapytania z indexem unique

Teraz w planie zapytania pojawił nam się UNIQUE SCAN oraz wykorzystanie utworzonego indexu TEST_TABLE_BAR_U_IDX.

Na planie jest mniejszy koszt względem zapytania z indexem NON UNIQUE ponieważ w przypadku zwykłego indexu baza po znalezieniu rekordu z odpowiadającym kluczem nie ma pewności, że jest to jedyny rekord zgodny z kluczem i szuka dalej kluczy w bloku danych. Natomiast przy zastosowaniu indexu typu UNIQUE po znalezieniu pierwszej wartości odpowiadającej kluczowi skanowanie zostaje zakończone ponieważ nie ma potrzeby szukania kolejnych rekordów odpowiadających kluczowi dlatego, że znaleziony rekord jest zawsze jedynym (unikalnym).

UNIQUE INDEX na kilku kolumnach tabeli

Ciekawym przypadkiem wydaje się sytuacja gdy stworzymy UNIQUE INDEX na kilku kolumnach tabeli taki jak poniższy:

REATE UNIQUE INDEX TEST_TABLE_FOO_BAR_U_IDX ON TEST_TABLE(FOO, BAR);

Teraz wyszukanie rekordów zgodnie z poprzednim zapytaniem SELECT gdzie w klauzuli WHERE mamy BAR = 50000 pokaże ciekawy plan (TEST_TABLE_FOO_BAR_U_IDX jest jedynym indexem na tabeli)

Jak widać baza nie wykorzysta UNIQUE SCAN a SKIP SCAN i to z kosztem 12. Takie zachowanie bazy na pierwszy rzut oka nie jest oczywiste skoro wiemy, że wszystkie wartości w kolumnie BAR są unikalne. Zakładając UNIQUE INDEX na dwóch kolumnach baza nie ma pewności czy zaraz nie dodamy rekordu z zduplikowaną wartością w kolumnie BAR dlatego też nie może użyć UNIQUE INDEX. Jest to możliwe ponieważ index który założyliśmy jest unikalny w obrębie pary kolumn FOO, BAR i pilnuje unikalności tylko na tej parze.

Zgodnie z powyższym aby użyć skanowania UNIQUE musimy w zapytaniu nałożyć warunki „=” na wszystkie kolumny z indexu. Dla indexu TEST_TABLE_FOO_BAR_U_IDX zapytanie musi być ograniczone dwoma warunkami na kolumnę FOO oraz na kolumnę BAR np.:

SELECT 
    FOO
  , BAR
FROM TEST_TABLE
WHERE 1 = 1
  AND BAR = 5000000
  AND FOO = 'test';

W warunku WHERE mamy parę kolumn BAR i FOO która po założeniu indexu musi być unikalna, pilnuje tego stworzony przez nas index. Zatem w tej sytuacji możliwe jest użycie UNIQUE SCAN zgodnie z poniższym planem:

Konsekwencje UNIQUE INDEX

Ważną konsekwencją założenia indexu typu UNIQUE jest to, że na kolumnie na której został założony index nie można dodać wartości nieunikalnej. Poniższe zapytanie dodaje rekord już istniejący w tabeli TEST_TABLE:

INSERT INTO TEST_TABLE (BAR) VALUES (1);

Spowoduje błąd:


Error starting at line : 1 in command -
INSERT INTO TEST_TABLE (BAR) VALUES (1)
Error report -
ORA-00001: unique constraint (SYSTEM.TEST_TABLE_BAR_U_IDX) violated

Index UNIQUE na kolumnie NON UNIQUE

Nie ma możliwości założenia indexu typu UNIQUE na kolumnę która ma wartości zduplikowane. Poniższy przykład pokaże co się stanie gdy spróbujemy założyć taki index:

CREATE UNIQUE INDEX TEST_TABLE_FOO_U_IDX ON TEST_TABLE(FOO);

Spowoduje błąd:

Error starting at line : 1 in command -
CREATE UNIQUE INDEX TEST_TABLE_FOO_U_IDX ON TEST_TABLE(FOO)
Error report -
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
01452. 00000 -  "cannot CREATE UNIQUE INDEX; duplicate keys found"
*Cause:    
*Action:

Podsumowanie

Wyszukiwanie danych w indeksie przy użyciu skanowania UNIQUE SCAN jest najszybszym wyszukiwaniem jednak obarczone jest wieloma obwarowaniami, unikalnością klucza indexu, nałożeniem ograniczenia na unikalność rekordów oraz wyszukiwaniem w zapytaniu zawsze jednego rekordu. Dlatego też, tego typu wyszukiwanie najczęściej stosowane jest na PRIMARY KEY które zawsze jest unikalne i zawsze ma założony index typu UNIQUE.

3 komentarze

  1. Dzięki za komentarz.
    W kursie dodaję warunek z przyzwyczajenia ale geneza dodawania 1=1 bierze się z:
    1) Kiedy piszesz w PL/SQL to dynamicznie tworzony SQL jest nieraz „budowany” z kilku zależnych ograniczeń. Przykładowo gdybyś miał zapytanie „select * from foo” i chciał dodać warunek pierwszy musiałbyś dodać „where x=y” natomiast dodanie kolejnego warunku wymagałoby dodania „and x>100”. A co w sytuacji gdy nie chcesz dodać pierwszego warunku a tylko drugi? Musiałbyś sprawdzać, czy warunek który dodajesz jest pierwszym czy kolejnym i dodawać odpowiednią składnię z WHERE lub AND.
    W sytuacji gdy bazowym zapytaniem będzie „select * from foo where 1=1” zawsze dodajesz warunek „AND x=y” lub ” ANDx>100″
    2) W trakcie pisania testowego zapytania SQL łatwiej jest zakomentować któryś z warunków np.
    WHERE 1=1
    AND a > 100
    AND b in (‚x’, ‚y’)
    wtedy łatwo wyłączyć warunek AND a > 100 bo wystarczy dodać znak komentarza —
    WHERE 1=1
    — AND a > 100
    AND b in (‚x’, ‚y’)

    Nieużywanie 1=1 jest oczywiście też poprawne ale tak jak pisałem, jestem pod tym względem już zboczony 🙂

    OracleDev

Dodaj komentarz

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