środa, 28 sierpnia 2013

Indeksy bitmapowe łączeniowe


Indeksy bitmapowe łączeniowe są wykorzystywane głównie w hurtowniach danych, często wraz z Star Transformation. Indeksy łączeniowe pojawiły się z wersją 9i Oracle.
Takie indeksy pozwalają zmniejszyć ilość wykonywanych kosztownych operacji łączenia tabel. Taki indeks będzie przechowywał wcześniej wyliczone wyniki łączenia. Podczas zapytania z użyciem łączenia, będzie można użyć danych z indeksu, zamiast wykonywać operację łączenia.
W indeksach bitmapowych każda unikalna wartość w kolumnie jest powiązana z mapą bitową, w której każdy bit reprezentuje jeden wiersz w tabeli. 1 oznacza że wartość występuje w danym wierszu, 0 że nie występuje. Indeksy takie zakłada się na kolumnach o małym zróżnicowaniu. Nie powinno się ich stosować na tabelach często modyfikowanych (tzn. na tabelach w których pojawiają się często nowe wiersze, kasowane są stare, lub zmienia się wartość kolumny na której założony jest taki indeks). Wiąże się to z obciążeniem wynikającym z konieczności aktualizacji takiego indeksu.

Przyjrzyjmy się teraz praktycznemu zastosowaniu tego typu indeksów. Pracujemy na schemacie SH , który jest schematem przykładowym dostarczanym przy instalacji bazy. Wystarczy ten schemat odblokować. Sprawdźmy plan wykonania zapytania wymagającego łączenia tabel customers i sales. Większość obciążenia generowane jest przez obciążenia wynikającego z samego łączenia, lub operacji z łączeniem związanych:


select count(*) from sales join customers using(cust_id) where cust_city='Yokohama';





Założymy teraz indeks bitmapowy łączeniowy i porównamy wyniki. Najprostszy taki indeks mógłby zawierać samo tylko złączenie:


create bitmap index lacz1 on sales(c.cust_id)
from sales s , customers c
where s.cust_id=c.cust_id local;

Słowo local na końcu jest wymagane wyłącznie w przypadku tabel partycjonowanych. W tym jednak przypadku stosujemy warunek na kolumnie cust_city, więc najlepiej byłoby gdyby ta kolumna również znalazła się w indeksie. W innym wypadku, w związku koniecznością dostępu do danych z tej kolumny, CBO decydowałby o nie używaniu naszego nowego indeksu. Stworzymy więc indeks zawierający nie tylko łączenie, ale również kolumnę cust_city z tabeli customers:


create bitmap index lacz1 on sales(customers.cust_city)
from sales , customers
where sales.cust_id=customers.cust_id local;

Gdybyśmy dostali komunikat o braku klucza głównego , należy uruchomić poniższe polecenie:

alter table customers enable constraint customers_pk;

Dotyczy to jednak jedynie powyższego przykładu w schemacie SH. Indeksy bitmapowe łączeniowe wymagają, by łączenie następowało z użyciem klucza głównego, a ten w tabeli curstomers istnieje, ale jest wyłączony. Porównajmy teraz plany wykonania zapytania i ich koszty:


Znacznie mniejszy koszt wykonania tego zapytania z użyciem indeksu bitmapowego łączeniowego, wynika z faktu, że nie ma konieczności wykonywania łączenia ze sobą tabel i związanych z tym odczytów danych które są do tego potrzebne. Informacje o połączeniu są przechowywane w indeksie. Oczywiście w przypadku np. dodania wiersza do tabeli sales, trzeba będzie znaleźć wszystkie odpowiedniki po stronie drugiej tabeli i zaktualizować indeks. To wiąże się z generowaniem sporego obciążenia przy ładowaniu danych.
Weźmy teraz troszkę bardziej skomplikowany przypadek. Łączymy ze sobą trzy tabele liniowo, przy czym warunek filtrowania wierszy stosujemy na ostatniej.


select count(*) from sales join customers using(cust_id)
join countries using (country_id) where country_name='Poland';




Ponownie pojawia nam się spore obciążenie wynikające z potrzeby łączenia tabel. Tworzę więc indeks bitmapowy łączeniowy, tym razem na łączeniu ze sobą trzech tabel:

create bitmap index lacz2 on sales(countries.country_name)
from sales , customers, countries
where sales.cust_id=customers.cust_id
and customers.country_id=countries.country_id
local;

Sprawdźmy jak prezentuje się nam plan wykonania teraz:




Ograniczenia indeksów bitmapowych łączeniowych

  • nie da się założyć takiego indeksu na tabeli tymczasowej , ani tabeli typu IOT.
  • W klauzuli FROM nie można wymienić tej samej tabeli dwukrotnie
  • nie może to być indeks funkcyjny











wtorek, 27 sierpnia 2013

Wyłączanie ACL w Oracle 11g

Jak całkiem wyłączyć ACL w Oracle 11g? Metoda znaleziona w internetach, może się komuś też przyda:


begin
 begin
 dbms_network_acl_admin.drop_acl(
 acl => 'all-network-PUBLIC.xml'
 );
 exception
 when others then null;
 end;
 dbms_network_acl_admin.create_acl(
 acl => 'all-network-PUBLIC.xml',
 description => 'Network connects for all',
 principal => 'PUBLIC',
 is_grant => true,
 privilege => 'connect'
 );
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
 acl => 'all-network-PUBLIC.xml',
 principal => 'PUBLIC',
 is_grant => true,
 privilege => 'resolve'
 );
 dbms_network_acl_admin.assign_acl(
 acl => 'all-network-PUBLIC.xml',
 host => '*'
 );
 end;
 /
 commit;