Indeksy w bazie danych Oracle

1
Indeksy w bazie danych Oracle

• Autor Michal Krzyzanowski

2
Co to jest indeks?

• Jest to struktura powiazana z tabela lub klastrem
  umozliwiajaca szybsze wykonywanie zapytan SQL
• Posiada strukture logiczna i fizyczna niezalezna
  od tabeli do jakiej sie odwoluje
• Posiada wlasna przestrzen dyskowa
• Jest automatycznie utrzymywany przez system
  zarzadzania baza danych

3
Po co nam indeksy?

• Przyspieszaja dostep do danych
• Tworzone dla relacji
• Zalety
• przyspieszaja odczyt danych
• wplywaja na stopien wspólbieznosci wykonywanych
  operacji
• Wady
• moga znacznie spowolnic modyfikacje danych
• zajmuja przestrzen dyskowa

4
Podzial indeksów

• ze wzgledu na strukture
• - B-drzewa, bitmapowe,
• ze wzgledu na liczbe atrybutów indeksowych w
  kluczu
• - indeksy zwykle i indeksy zlozone,
• ze wzgledu na unikalnosc wartosci klucza
• - indeksy unikalne i indeksy nieunikalne,
• ze wzgledu na kolejnosc wartosci klucza
• - indeksy zwykle i indeksy odwrócone,
• ze wzgledu na sposób skladowania
• - indeksy nieskompresowane i indeksy
  skompresowane,
• ze wzgledu na zastosowania
• - indeksy funkcyjne i bitmapowe indeksy
  polaczeniowe.

5
Indeksy typu b-drzewo

• Wykorzystywany dla atrybutów o duzej
  selektywnosci
• Wielkosc indeksu slabo zalezna od rozmiaru
  dziedziny indeksowanego atrybutu
• Nie przechowuje informacji o wartosciach
  nullowych
• CREATE INDEX nazwa_indeksu ON tabela(kolumna)

6
Indeksy bitmapowe

• Wykorzystywane dla atrybutów o malej
  selektywnosci, np. plec
• Przechowuje wartosci nullowe
• Rozmiar indeksu scisle zalezny od rozmiaru
  dziedziny atrybutu indeksu
• Nie mozna zakladac globalnych indeksów
  bitmapowych na partycjonowanych tabelach
• CREATE BITMAP INDEX nazwa_indeksu ON tabela
  (kolumna)

7
Indeksy zlozone

• Skladaja sie z wiekszej niz jedna ilosci kolumn
• Stosuje sie je gdy w zapytaniach wystepuje kilka
  kolumn w w warunku WHERE
• Powinno sie stosowac kolejnosc kolumn wedlug
  czestotliwosci ich wystepowania w zapytaniach
• CREATE INDEX nazwa_indeksu ON tabela (kolumna1,
  kolumna2, kolumna3)

8
Indeks unikalny

• Tworzony, zeby zapewnic unikalnosc wartosci
  indeksowanej kolumnie lub kolumnach.
• Nie mozna jednoczesnie stworzyc indeksu
  unikalnego i bitmapowego.
• CREATE UNIXUE INDEX nazwa_indeksu ON tabela
  (kolumna)

9
Indeksy odwrócone

• Wartosc w indeksie jest przechowywana w odwrotny
  sposób niz wartosc przechowywana w atrybutach,
  np. wartosc ORACLE bedzie przechowywana jako
  ELCARO
• Stosowane do indeksowania sekwencji
• Powoduja rozproszenie wartosci w indeksie
• CREATE INDEX nazwa_indeksu ON tabela (kolumna)
  REVERSE

10
Indeksy skompresowane

• Redukuje zajmowana przestrzen dyskowa poprzez
  powtarzajace sie wartosci klucza indeksu
• Zwieksza zyzycie procesora przy przegladaniu
  wartosci indeksu
• Dostepne tylko dla indeksów typu B-drzewo
• CREATE INDEX nazwa_indeksu ON tabela(kolumna)
  COMPRESS 1

11
Indeksy funkcyjne

• Indeks przechowuje wartosc funkcji lub wyrazenia
  zamiast wartosci kolumny
• Wykorzystywany dla atrybutów, które w zapytaniach
  sa czesto argumentami funkcji
• Indeksowane wyrazenie nie moze zawierac funkcji
  agregujacych
• CREATE INDEX nazwa_indeksu ON tabela
  (UPPER(kolumna))

12
Bitmapowe indeksy polaczeniowe

• Jest tworzony przy zlaczaniu tabel
• Mozliwe jest zastosowanie klauzuli WHERE przy
  tworzeniu indeksu
• CREATE BITMAP INDEX nazwa ON relacja
  (lista_atrybutów)
• FROM relacja_1, relacja_2, ..., relacja_n
• WHERE warunek_polaczeniowy_1 AND
  warunek_polaczeniowy_2 ...
• AND warunek_polaczeniowy_n-1

13
Kryteria wyboru indeksów

• Dane nie powinny byc czesto modyfikowane
• Atrybuty czesto wystepujace w zapytaniach WHERE
• Atrybuty uzywane w warunkach zlaczeniowych
• Indeksu powinien znajdowac sie na innym dysku niz
  tabela zródlowa

14
Zarzadzanie indeksami

• Tworzenie indeksów
• Przebudowywanie indeksów
• Monitorowanie indeksów
• Usuwanie indeksów
• Zmiana nazwy indeksu

15
Przebudowa indeksów

• ALTER INDEX nazwa_indeksu REBUILD
• ALTER INDEX emp_name REBUILD ONLINE - opcja
  ONLINE pozwala na modyfikacje danych w tabeli
  zródlowej indeksu podczas jego przebudowy

16
Monitorowanie indeksu

• CREATE INDEX nazwa_indeksu COMPUTE STATISTICS
• ALTER INDEX nazwa_indeksu COMPUTE STATISTICS
• ALTER INDEX index MONITORING USAGE - wlaczanie
  monitoringu uzycia indeksu
• ALTER INDEX index NOMONITORING USAGE -
  wylaczanie monitoringu uzycia indeksu

17
Usuwanie indeksów

• DROP INDEX nazwa_indeksu
• Nie mozna usunac indeksu bedacego jednoczesnie
  kluczem unikalnym lub kluczem glównym tabeli.
  Nalezy wczesniej wylaczyc lub usunac te
  constrainty.

18
Zmiana nazwy indeksu

• ALTER INDEX nazwa_indeksu RENAME TO nowa_nazwa

19
Widoki zawierajace informacje o indeksach

• DBA_INDEXES, ALL_INDEXES, USER_INDEXES - opisuja
  indeksy w bazie danych
• DBA_IND_COLUMNS, ALL_IND_COLUMNS,
  USER_IND_COLUMNS przechowuja informacje o
  indeksowanych kolumnach
• DBA_IND_EXPRESSIONS, ALL_IND_EXPRESSIONS,
  USER_IND_EXPRESSIONS opisuja wyrazenia uzywane
  przez indeksy funkcyjne
• DBA_IND_STATISTICS, ALL_IND_STATISTICS,
  USER_IND_STATISTICS zawieraja statystyki
  optymalizatora dotyczace indeksów.

20
Tabele oparte na indeksach

• Dane sa przechowywane w strukturze B-drzewa
• Oracle nie buduje tabeli tylko przechowywuje
  wszystkie wymagane informacje w postaci indeksu

21
Tworzenie tabeli

• CREATE TABLE emp_iot (   
• emp_id number,   
• ename varchar2(20),   
• sal number(9,2),   
• deptno number,   
• CONSTRAINT pk_emp_iot_index PRIMARY KEY (emp_id)
  )
• ORGANIZATION index