Partycjonowanie tabel w Oracle Ograniczenia

PODSTAWY
BAZ DANYCH
10. Partycjonowanie tabel i indeksów
2009/2010 - Notatki do wykładu "Podstawy baz danych"
1
Partycjonowanie tabel i indeksów w Oracle
W celu poprawienia efektywności dostępu do danych oraz
ułatwieniu zarządzania bardzo dużymi zbiorami danych w Oracle
wprowadzone zostały nowe struktury danych:
1. Partycjonowane tabele i indeksy
2. Tabele – indeksy
Przy wyborze systemu zarządzania bazą danych należy brać pod
uwagę istnienie podobnych rozwiązań, które przy dużych bazach
danych będą miały wpływ na wydajność systemu bazodanowego.
2009/2010 - Notatki do wykładu "Podstawy baz danych"
2
Partycjonowanie tabel i indeksów w Oracle
2009/2010 - Notatki do wykładu "Podstawy baz danych"
3
Partycjonowanie tabel w Oracle
CREATE TABLE faktury
( nr_faktury NUMBER(5) PRIMARY KEY,
id_klienta NUMBER(3)NOT NULL,
data_wystawienia DATE NOT NULL,
kwota NUMBER(8,2) NOT NULL )
PARTITION BY RANGE(data_wystawienia)
(
PARTITION m_10_2003 VALUES LESS THAN
(TO_DATE(’01/11/2003’,’dd/mm/yyyy’))
TABLESPACE ts10;
PARTITION m_11_2003 VALUES LESS THAN
(TO_DATE(’01/12/2003’,’dd/mm/yyyy’))
TABLESPACE ts11;
PARTITION nadmiar VALUES LESS THAN MAXVALUE)
TABLESPACE tsnadmiar;
);
2009/2010 - Notatki do wykładu "Podstawy baz danych"
4
Partycjonowanie tabel w Oracle
Partycje muszą być definiowane w kolejności rosnącej wartości
atrybutu partycjonującego. W przypadku braku partycji
nadmiar, gdy wstawiany wiersz nie pasuje do żadnej istniejącej
partycji zgłoszony będzie błąd i wiersz nie zostanie wstawiony.
Ograniczenia:
1. Nie można modyfikować wartości atrybutu partycjonującego
o ile spowoduje to konieczność przeniesienie wiersza do innej
partycji.
Wtedy należy wiersz usunąć i ponownie wstawić z nową
wartością atrybutu.
2. Nie można wstawić wiersza nie mieszczącego się na żadnej
partycji (w przypadku braku partycji nadmiar).
2009/2010 - Notatki do wykładu "Podstawy baz danych"
5
Odwoływanie się do tabel partycjonowanych
Tradycyjnie
SELECT *
FROM faktury
WHERE warunek;
Wybór z odpowiedniej partycji
SELECT *
FROM faktury PARTITION(m_10_2003)
WHERE warunek;
Nowe polecenia SQL służące do zarządzania partycjonowanymi
tabelami.
Są to polecenia z grupy ALTER TABLE:
ADD PARTITION,DROP PARTITION,
TRUNCATE PARTITION, SPLIT PARTITION,
…
2009/2010 - Notatki do wykładu "Podstawy baz danych"
6
Partycjonowanie tabel w Oracle – przykłady modyfikowania tabel
Podział partycji
ALTER TABLE faktury
SPLIT PARTITION nadmiar
AT (TO_DATE(‘01/01/2004’,’dd/mm/yyyy’))INTO
(
PARTITION m_12_2003,
PARTITION nadmiar
);
Usunięcie wierszy z partycji
ALTER TABLE faktury TRUNCATE PARTITION nadmiar;
Jest to bardziej optymalne rozwiązanie niż usuwanie wierszy
poleceniem DELETE.
2009/2010 - Notatki do wykładu "Podstawy baz danych"
7
Partycjonowanie tabel w Oracle – przykłady modyfikowania tabel
Usunięcie partycji
ALTER TABLE faktury DROP PARTITION nadmiar;
Dodanie partycji
ALTER TABLE faktury
ADD PARTITION nadmiar LESS THAN (maxvalue)
TABLESPACE tsnadmiar;
2009/2010 - Notatki do wykładu "Podstawy baz danych"
8
Partycjonowanie indeksów w Oracle
Można wyróżnić następujące rodzaje partycjonowania indeksów:
Lokalne - zgodne z partycjami tabel;
Globalne- niezgodne z partycjami tabel;
Przykład. Indeks lokalny
CREATE BITMAP INDEX idx_klient
ON faktury (id_klienta)
LOCAL;
Powstanie indeks lokalny rozdzielony identycznie jak tabela
(według atrybutu data_wystawienia).
2009/2010 - Notatki do wykładu "Podstawy baz danych"
9
Partycjonowanie indeksów w Oracle
Przykład. Indeks globalny
CREATE INDEX idx_faktury
ON faktury (id_faktury)
GLOBAL
PARTITION BY RANGE (id_faktury)
(
PARTITION p_1 VALUES LESS THAN (500),
PARTITION nadmiar VALUES LESS THAN (maxvalue)
);
Powstanie indeks globalny rozdzielony inaczej jak
partycjonowana tabela faktury.
2009/2010 - Notatki do wykładu "Podstawy baz danych"
10
Tabele - indeksy w Oracle
W Oracle dostępne są możliwości tworzenia tabel, które będą
zorganizowane w postaci B*-drzewa.
CREATE TABLE nazwa_tabeli
( atrybut [,atrybut [,atrybut]…] )
ORGANIZATION INDEX
[ PCTTHRESHOLD n]
[ OVERFLOW TABLESPACE przestrzeń_tabel ];
gdzie:
n - oznacza rozmiar wiersza w procencie rozmiaru bloku,
powyżej którego jego atrybuty niekluczowe są
przechowywane w oddzielnym segmencie;
przestrzeń_tabel - nazwa przestrzeni tabel (tablespace), w której
zapisany zostanie segment z nadmiarowymi fragmentami
wiersza;
2009/2010 - Notatki do wykładu "Podstawy baz danych"
11
Partycjonowanie tabel w Oracle według wielu atrybutów - przykład
Można również tworzyć partycje według wielu atrybutów.
Przykład.
CREATE TABLE dostawca_czesci
(
dostawca_id
NUMBER,
num_czesci
NUMBER,
cena
NUMBER
)
PARTITION BY RANGE (dostawca_id, num_czesci)
(
PARTITION p1 VALUES LESS THAN (10,100),
PARTITION p2 VALUES LESS THAN (10,200),
PARTITION p3 VALUES LESS THAN
(MAXVALUE,MAXVALUE)
);
2009/2010 - Notatki do wykładu "Podstawy baz danych"
12
Partycjonowanie tabel w Oracle według wielu atrybutów - przykład
CREATE TABLE sprzedaz
(
rok
NUMBER,
miesiac
NUMBER,
dzien
NUMBER,
ilosc
NUMBER
)
PARTITION BY RANGE (rok, miesiac)
(
PARTITION przed2001 VALUES LESS THAN (2001,1),
PARTITION q1_2001 VALUES LESS THAN (2001,4),
PARTITION q2_2001 VALUES LESS THAN (2001,7),
PARTITION q3_2001 VALUES LESS THAN (2001,10),
PARTITION q4_2001 VALUES LESS THAN (2002,1),
PARTITION nadmiar VALUES LESS THAN(MAXVALUE,0)
);
2009/2010 - Notatki do wykładu "Podstawy baz danych"
13
Partycjonowanie tabel w Oracle według wielu atrybutów
Przykład. Tabela jest partycjonowana według trzech atrybutów.
Który z następujących podziałów jest poprawny?
(a) poprawny
(b) niepoprawny
(10, 20, 30)
(10, 20, 30)
(20, 15, 20)
(20, 15, 20)
(20, 30, 10)
(20, 30, 10)
(20, 30, 20)
(20, 30, 40)
(20, 30, 30)
(20, 30, 20)
(20, 30, 40)
(20, 30, 30)
2009/2010 - Notatki do wykładu "Podstawy baz danych"
14
Haszowane partycjonowanie tabel
Partycje bez specyfikacji przestrzeni tabel i nazw partycji.
Przykład. 16 partycji bez specyfikacji nazw.
CREATE TABLE osoby
( id NUMBER(5), dane VARCHAR(32) )
PARTITION BY HASH( id ) PARTITIONS 16;
/* id mod 16 */
Trzy partycje ze specyfikacją nazw i przestrzeni tabel.
CREATE TABLE osoby
( id NUMBER(5), dane VARCHAR(32) )
PARTITION BY HASH( id )
(
PARTITION p1 TABLESPACE ts2,
PARTITION p2 TABLESPACE ts1,
PARTITION p3 TABLESPACE ts3
);
2009/2010 - Notatki do wykładu "Podstawy baz danych"
/* id mod 3 */
15
Partycjonowanie tabel z podpartycjami haszowanymi (subpartition)
Można również tworzyć podpartycje.
CREATE TABLE test
( i NUMBER(5), j NUMBER(5) )
PARTITION BY RANGE(j)
SUBPARTITION BY HASH(i)
(
PARTITION p1 VALUES LESS THAN (10)
SUBPARTITION t1_pls1 /* id mod 2 */
SUBPARTITION t1_pls2,
PARTITION p2 VALUES LESS THAN (20)
SUBPARTITION t2_p2s1
/* id mod 2 */
SUBPARTITION t2_p2s2)
);
2009/2010 - Notatki do wykładu "Podstawy baz danych"
16
Partycjonowanie tabel według listy wartości
Można również tworzyć partycje według listy wartości.
CREATE TABLE miasta
(
id NUMBER(5),
miasto VARCHAR(20),
panstwo CHAR(2) )
PARTITION BY LIST (panstwo)
(
PARTITION p_wschod
VALUES ('PL', 'CZ',…) TABLESPACE tbs1,
PARTITION p_zachod
VALUES ('ES', 'FR',…) TABLESPACE tbs2
);
2009/2010 - Notatki do wykładu "Podstawy baz danych"
17
Indeksy prefiksowane i bez prefiksu
Oprócz podziału indeksów ze względu na typ partycjonowania
w Oracle można dokonać podziału indeksów według
indeksowanych atrybutów na:
•
Prefiksowane - jeżeli pierwsze kolumny indeksu bazują na
atrybutach partycjonujących (z zachowaniem kolejności);
•
Bez prefiksu - jeżeli pierwsze kolumny indeksu nie
pokrywają się z atrybutami partycjonującymi;
2009/2010 - Notatki do wykładu "Podstawy baz danych"
18
Indeksy prefiksowane i bez prefiksu - przykład
Utwórzmy tabelę:
CREATE TABLE test
(
id1 NUMBER(3),
id2 NUMBER(3),
id3 NUMBER(3),
. . .
)
PARTITION BY RANGE ( id1, id2 )
(
PARTITION p_1 VALUES LESS THAN (10,20),
PARTITION p_nadm VALUES THAN
(maxvalue, maxvalue)
);
2009/2010 - Notatki do wykładu "Podstawy baz danych"
19
Indeksy prefiksowane i bez prefiksu - przykład
Indeks prefiksowany
CREATE INDEX idx_id1_id2
ON test ( id1, id2 )
LOCAL;
Indeks bez prefiksu
CREATE INDEX idx_id3
ON test ( id3 )
LOCAL;
Zalecane jest stosowanie indeksów lokalnych prefiksowanych.
2009/2010 - Notatki do wykładu "Podstawy baz danych"
20