Blog > Komentarze do wpisu
Oracle - indeksy na kluczach obcych (foreign keys)

Indeksowanie kluczy obcych uchodzi za dobra praktykę i tak naprawdę w 99% przypadków powinno się to robić. Mimo to baza danych Oracle nie tworzy takich indeksów automatycznie w momencie tworzenia kluczy obcych.

Indeksy nie są tak istotne w sytuacjach, gdy:

 

  1. nigdy nie usuwamy rekordów z tabeli nadrzędnej (gdy wykorzystujemy delete cascade, usuwanie rekordów z tabeli nadrzędnej jest jeszcze kosztowniejsze gdyż wykonywany jest pełny skan tabeli podrzędnej tak, aby wyszukać wszystkie pasujące rekordy),
  2. nigdy nie zmieniamy wartości klucza głównego,
  3. nie łączymy rezultatów zapytań w oparciu o relację Parent - Child.


//wszyscy pracownicy z wydzialu o numerze :X
select * from dept, emp
     where emp.deptno = dept.deptno and dept.deptno = :X;

O ile punkt 3 potencjalnie wpływa „jedynie” na wydajność zapytań, o tyle usuwanie i / bądź modyfikacja wartości kluczy głównych w tabeli nadrzędnej (punkty 1 i 2) może prowadzić do pełnej blokady tabeli podrzędnej ponieważ w takich wypadkach Oracle zakłada na tabeli blokadę „table level lock”. W moim wypadku to właśnie doprowadziło do poważnych problemów dwa tygodnie temu.

Poniższe zapytanie, oparte na znalezionym w książce Expert Oracle Database Architecture wynajduje wszystkie, niepoindeksowane klucze obce. W zależności od posiadanych uprawnień możemy je wykonać na różnych widokach systemowych (na przykład ALL_CONS_COLUMNS A, USER_CONS_COLUMNS A, DBA_CONS_COLUMNS A) wystarczy odpowiednio „przesunąć” komentarze.

Skrypt generuje polecenia create index jak również, na wszelki wypadek, polecenia drop index. Nazwy indeksów są tworzone automatycznie na podstawie nazwy klucza obcego:

SUBSTR (CONSTRAINT_NAME, 1, 28) || '_I

Przed utworzeniem indeksów dobrze jest sprawdzić czy nazwy tworzonych obiektów będą unikalne i czy np. nie zawierają dziwnych znaków, np. u mnie ktoś użył znaku # w nazwie klucza do reprezentacji numeru.

Skrypt sprawdza, czy istnieje dokładnie pasujący indeks i jeśli go nie znajdzie, wyświetla ostrzeżenie *CHECK*. Jeśli na danej tabeli założony jest indeks złożony (niepasujący dokładnie do kolumn klucza), zapytanie wyświetla dodatkową informacje, aby „ręcznie” sprawdzić istniejący indeks złożony. Nie musi być on identyczny tak długo jak wiodąca jego cześć zgodna jest z kolejnością kolumn klucza obcego.

 

Poniższy przykład pożyczony ze strony AskTom[2] ilustruję sytuację:

create table emp ( empno int primary key, ...  deptno references DEPT );

create index on dept(deptno,empno);  -- OK
create index on dept(empno,deptno);  -- wystapi lock
create index on dept(deptno);  -- OK


Jeśli nie chcecie kopiować tekstu, skrypt możecie pobrać tutaj.

 

--
-- http://notatkiprogramisty.blox.pl
--
-- Query looks for Foreign Keys without indexes
-- Adapted from SQL statement that was found in the Expert Oracle Database Architecture.
--
-- 1. Before running create statements check if automatically created index names are unique
-- 2. Modify schema owners XXXX, YYYY as appropriate
--
WITH TAB
     AS (  SELECT DECODE (B.TABLE_NAME, NULL, '*Check*', 'OK') STATUS1,
                  A.OWNER,
                  A.TABLE_NAME CHILD_TABLE,
                  B.TABLE_NAME PARENT_TABLE,
                  A.CONSTRAINT_NAME,
                  A.COLUMNS,
                  B.COLUMNS INDEX_COLUMNS
             FROM (  SELECT A.OWNER,
                            SUBSTR (A.TABLE_NAME, 1, 30) TABLE_NAME,
                            SUBSTR (A.CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,
                               MAX (DECODE (POSITION, 1, SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 2, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 3, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 4, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 5, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 6, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 7, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 8, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 9, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 10, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 11, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 12, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 13, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 14, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 15, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (POSITION, 16, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                               COLUMNS
                       FROM ALL_CONS_COLUMNS A,    --USER_CONS_COLUMNS A, --DBA_CONS_COLUMNS A,
                            ALL_CONSTRAINTS B      --USER_CONSTRAINTS B --DBA_CONSTRAINTS B
                      WHERE     A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
                            AND A.OWNER = B.OWNER
                            AND B.CONSTRAINT_TYPE = 'R'
                   GROUP BY A.OWNER,
                            SUBSTR (A.TABLE_NAME, 1, 30),
                            SUBSTR (A.CONSTRAINT_NAME, 1, 30)) A,
                  (  SELECT TABLE_OWNER,
                            SUBSTR (TABLE_NAME, 1, 30) TABLE_NAME,
                            SUBSTR (INDEX_NAME, 1, 30) INDEX_NAME,
                               MAX (DECODE (COLUMN_POSITION, 1, SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 2, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 3, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 4, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 5, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 6, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 7, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 8, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 9, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 10, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 11, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 12, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 13, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 14, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 15, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                            || MAX (DECODE (COLUMN_POSITION, 16, ', ' || SUBSTR (COLUMN_NAME, 1, 30), NULL))
                               COLUMNS
                       FROM ALL_IND_COLUMNS -- USER_IND_COLUMNS -- DBA_IND_COLUMNS
                   GROUP BY TABLE_OWNER,
                            SUBSTR (TABLE_NAME, 1, 30),
                            SUBSTR (INDEX_NAME, 1, 30)) B
            WHERE     A.TABLE_NAME = B.TABLE_NAME(+)
                  AND A.OWNER = B.TABLE_OWNER(+)   --SCHEMA NAME
                  AND B.COLUMNS(+) LIKE A.COLUMNS || '%'
                  AND A.OWNER IN ('YYYY', 'XXXX')
         ORDER BY A.OWNER, A.TABLE_NAME)
  SELECT TAB.*,
         CASE
            WHEN REGEXP_COUNT (COLUMNS, ',') + 1 > 1
            THEN
               '*Check Existing Composite Indexes*'
            ELSE
               'OK'
         END STATUS2,
         'CREATE INDEX ' || SUBSTR (CONSTRAINT_NAME, 1, 28) || '_I ON ' || CHILD_TABLE || '(' || COLUMNS || ');' CREATE_STM,
         'DROP INDEX ' || SUBSTR (CONSTRAINT_NAME, 1, 28) || '_I' || ';' DROP_STM
    FROM TAB
   WHERE STATUS1 != 'OK'
ORDER BY STATUS2, CHILD_TABLE;

 

UWAGA!

Jeśli w systemie brakuje wielu indeksów (u mnie brakowało ich niemal 300) należy liczyć się z tym, że ich utworzenie, może negatywnie wpłynąć na wydajność systemu. Ja sam po operacji musiałem dwa indeksy usunąć do momentu poprawienia jednego z zapytań.

 

Dodatkowe informacje / linki:

  1. http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#autoId24
  2. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754

 

poniedziałek, 29 kwietnia 2013, m0rt1m3r

Related Posts Plugin for WordPress, Blogger...

Polecane wpisy





PowerBuilder Tetris
D - Tetris



Programowanie iOS

C# ToolBox

SQL / TSQL / PLSQL ToolBox

Linux / Unix ToolBox





Zaprzyjaznione Strony

Sprite Bandits

Cake Time