Hurtownie Danych i Przetwarzanie Analityczne

Partycjonowanie danych: zadania

Zad.1.
Utworzyć tabelę PURCHASES partycjonowaną zakresowo, z czterema partycjami. Schemat PURCHASES przedstawiono poniżej. Pierwsze trzy partycje PURCHASES mają przechowywać dane o sprzedaży odpowiednio: w styczniu 2002, lutym 2002 i marcu 2002. Czwarta partycja ma przechowywać pozostałe dane o sprzedaży. Atrubutem partycjonoującym jest purchase_date.
Uwaga 1: zakładamy, że w hurtowni danych nie będą przechowywane dane wcześniejsze niż ze stycznia 2002.
Uwaga 2: należy zwrócić uwagę na domyślny format daty.

PURCHASES

Name                Null?    Type
------------------- -------- ------------
PRODUCT_ID          NOT NULL VARCHAR2(8)
TIME_KEY            NOT NULL DATE
CUSTOMER_ID         NOT NULL VARCHAR2(10)
PURCHASE_DATE                DATE
PURCHASE_TIME                NUMBER(4)
PURCHASE_PRICE               NUMBER(6,2)
SHIPPING_CHARGE              NUMBER(5,2)
STATE_ID                     VARCHAR2(2)
TODAY_SPECIAL_OFFER          VARCHAR2(1)

Utworzyć indeks lokalny, jak niżej i wyświetlić informacje o partycjach tego indeksu. Następnie usunąć ten indeks.

create index purchases_local_indx
on purchases(purchase_date)
local; 

Utworzyć indeks lokalny, jak niżej i wyświetlić informacje o partycjach tego indeksu. Następnie usunąć ten indeks.

create index purchases_local_indx
on purchases(purchase_date)
local
(partition indx_purch_jan2002, 
 partition indx_purch_feb2002, 
 partition indx_purch_mar2002,
 partition indx_purch_apr2002); 

Zad.2.
Dodać do tabeli PURCHASES nową partycję przechowującą rekordy sprzedaży z kwetnia 2002.

Zad.3.
Utworzyć tabelę PURCHASES1 partycjonowaną haszowo, z trzema partycjami. Kluczem partycjonującym jest atrybut PRODUCT_ID. PURCHASES1 posiada identyczny schemat jak tabela PURCHASES.

Zad.4.
Utworzyć tabelę REGIONAL_SALES partycjonowaną listowo. Schemat REGIONAL_SALES przedstawiono poniżej. Tabela ma posiadać 6 partycji, których atrybutem partycjonującym jest STATE. Nazwy poszczególnych partycji i zbiory wartości atrybutu STATE przedstawiono poniżej.

NORTHEAST -> 'NH', 'VT', 'MA', 'RI', 'CT'
SOUTHEAST -> 'NC', 'GA', 'FL'
NORTHWAST -> 'WA', 'OR'
MIDWEST -> 'IL', 'WI', 'OH'
WEST -> 'CA', 'NV', 'AZ'
OTHERSTATES -> pozostałe

REGIONAL_SALES

Name            Null?    Type
--------------- -------- ------------
STATE           NOT NULL VARCHAR2(2)
STATE_NAME      NOT NULL VARCHAR2(20)
PRODUCT_ID      NOT NULL VARCHAR2(8)
PRODUCT_SALE    NOT NULL NUMBER(8)

Zad.5.
Utworzyć indeks globalny bez perfiksu na atrybucie PURCHASES.PRODUCT_ID. Indeks ma być podzielony na trzy partycje. Pierwsza o wartościach PRODUCT_ID < 'SP1000', druga o wartościach PRODUCT_ID < 'SP2000', a trzecia o wartościach PRODUCT_ID >='SP2000'.

Zad.6.
Utworzyć tabelę PURCHASES_JAN2002 jako kopię tabeli wrembel_r.PURCHASES (wykorzystać polecenie create table as select), zawierającą wyłącznie dane o sprzedaży ze stycznia 2002 (atrybut purchase_date).
Wymienić dane między tabelą PURCHASES_JAN2002, a partycją tabeli PURCHASES przechowującą dane ze stycznia 2002 (utworzoną w ćwiczeniu 1).

Zad.7.
Wczytać do tabeli PURCHASES dane o sprzedaży w lutym 2002. Źródłem danych jest tabela wrembel_r.PURCHASES. Odczytać zawartość partycji przechowującej dane z lutego.

Zad.8.
Obserwacja planów wykonania zapytań.
Korzystając z polecenia set autotrace traceonly explain włączyć wyświetlanie planów zapytań.

Wykonać poniższe zapytania i zaobserować różnicę w planach ich wykonania.

select * from purchases where purchase_date > '31-01-2002';
select * from purchases where purchase_date < '31-01-2002';

Zad.9.
Utworzyć tabelę Products_Part, o schemacie przedstawionym poniżej, partycjonowaną listowo z trzema partycjami: pierwsza przechowuje produkty kateogrii 'HDRW', druga - 'ELEC', a trzecia - 'MUSC'.

product_id             varchar2(8) PRIMARY KEY
product_name           varchar2(30)
category               varchar2(4)
cost_price             number (6,2)
sell_price             number (6,2)
weight                 number (4,2)
shipping_charge        number (5,2)
manufacturer           varchar2(20)
supplier               varchar2(10)

Wczytać dane do Products_Part z tabeli wrembel_r.PRODUCT.

Utworzyć tabelę Sales_Part_Prod, o schemacie przedstawionym poniżej, partycjonowaną referencyjnie, tak, aby podział na partycje odpowiadał podziałowi na partycje tabeli Products_Part.

product_id             varchar2(8) FK -> Products_Part(product_id)
time_key               date
customer_id            varchar2(10)
purchase_date          date
purchase_time          number(4,0)
purchase_price         number(6,2)
shipping_charge        number(5,2)
state_id               varchar2(2)
today_special_offer    varchar2(1)

Uwaga: poprawnie zdefiniować ograniczenie integralnościowe klucza obcego.

Sprawdzić jakie partycje posiada tabela Sales_Part_Prod.

Wczytać z tabeli wrembel_r.PURCHASES dane do tabeli Sales_Part_Prod i sprawdzić ile rekordów znajduje się w każdej z partycji.

Zad.11.
Zbadać plany wykonania następujących zapytań.

select product_name, count(1) 
from Products_Part p, Sales_Part_Prod s
where p.product_id=s.product_id
and p.category='ELEC'
group by product_name;

select product_name, count(1) 
from Products_Part p, Sales_Part_Prod s
where p.product_id=s.product_id
group by product_name;

Zebrać statystyki dla obu tabel i powtórzyć zapytania. Szablon polecenia zbierającego statystyki:

EXEC DBMS_STATS.gather_table_stats('nazwa-uzytk', 'nazwa-tabeli', estimate_percent 
=> DBMS_STATS.auto_sample_size);

Zad.12.
Korzystając ze słownika bazy danych wyświetlić informacje o wszystkich partycjach tabeli PURCHASES i partycjonowanych indeksach.

Zad.13.
Utworzyć tabelę Sales_Dyn_Part o poniższym schemacie, z dynamicznymi partycjami, z których każda przechowuje dane z jednego miesiąca. Warunek less than niech przyjmuje wartość pierwszego dnia bieżącego miesiąca.

Name                Null?    Type
------------------- -------- ------------
PRODUCT_ID          NOT NULL VARCHAR2(8)
PURCHASE_DATE                DATE
QUANTITY                     NUMBER(3)

Wstawić do Sales_Dyn_Part kilka rekordów testowych z datami z różnych miesięcy. Sprawdzić w słowniku bazy danych czy i jakie partycje są tworzone dla tej tabeli.