środa, 23 października 2013

Oznaczanie kolumn jako UNUSED

Oznaczanie kolumn jako UNUSED


Usuwanie kolumn w bardzo dużych tabelach, z reguły trwa dość długo i generuje pewne obciążenie dla bazy danych. Jeśli zależy Ci na pozbyciu się kolumny bez takich negatywnych perturbacji i zależy Ci na czasie, możesz taką niepożądaną kolumnę oznaczyć jako UNUSED. Takie oznaczenie kolumny nie sprawi że zostanie ona usunięta, ani też nie uwolni zajmowanego przez nią miejsca. Nie będzie jednak widoczna w wynikach zapytania na tej tabeli, ewentualne referencje zostaną usunięte, a będzie można stworzyć kolejną kolumnę o takiej samej nazwie. Oznaczanie kolumn jako UNUSED wykonujemy taką komendą:

ALTER TABLE HR.EMPLOYEES SET UNUSED(SALARY,FIRST_NAME);


Sprawdzić czy mamy jakieś kolumny oznaczone jako unused możemy w słowniku DBA_UNUSED_COLS_TABS:

SELECT * FROM DBA_UNUSED_COLS_TABS WHERE TABLE_NAME='EMPLOYEES';



W przypadku zwykłego użytkownika mamy do dyspozycji słownik USER_UNUSED_COL_TABS:


SELECT * FROM USER_UNUSED_COL_TABS WHERE TABLE_NAME='EMPLOYEES';


W późniejszym czasie możemy fizycznie usunąć kolumny oznaczone jako UNUSED przy użyciu komendy:


ALTER TABLE EMPLOYEES DROP UNUSED COLUMNS;

poniedziałek, 21 października 2013

Synonimy w Oracle


Synonimy




Synominy są alternatywnymi nazwami dla tabel, widoków, sekwencji, procedur, funkcji, pakietów, widoków zmaterializowanych, klas javowych, typów obiektowych zdefiniowanych przez użytkownika, lub innych synonimów. Synonimy są użyteczne gdy np. zechcemy mieć krótszą nazwę dla jakiegoś obiektu, lub nazwę bardziej zrozumiałą dla człowieka. Np. tabela nazywa się „HUR_KLI_SZK_ORA” i nadajemy jej synomin „klienci_szkolen_oracle”. Nadanie użytkownikowi uprawnień do synonimu nie powoduje nadania uprawnień do obiektu dla którego synonim został stworzony. Uprawnienia do takiego obiektu muszą zostać nadane oddzielnie.
Synonimy możemy wykonywać w operacjach : SELECT, INSERT, DELETE, UPDATE, FLASHBACK, EXPLAIN PLAN, LOCK TABLE, AUDIT, NOAUDIT, GRANT, REVOKE, COMMENT



Uprawnienia do tworzenia synonimów




Aby tworzyć prywatne synonimy, wymagane jest uprawnienie CREATE SYNONYM
Aby tworzyć publiczne synonimy, wymagane jest uprawnienie CREATE PUBLIC SYNONYM
Aby tworzyć prywatne synonimy w schemacie innego użytkownika, wymagane jest uprawnienie CREATE ANY SYNONYM



Synonimy prywatne i publiczne




Synonimy publiczne, to synonimy widoczne dla wszystkich użytkowników. Aby korzystać z synonimów, użytkownicy muszą mieć dostęp do obiektów do których synonim się odnosi. Synonimy prywatne są widoczne tylko dla użytkownika w którego schemacie synonim się znajduje.




Tworzenie synominów




Składnia tworzenia publicznych synonimów jest następująca:



CREATE PUBLIC SYNONYM EMPS FOR EMPLOYEES;



Składnia tworzenia prywatnych synonimów jest następująca:



CREATE SYNONYM EMPS FOR EMPLOYEES;



Składnia tworzenia synonimu w schemacie innego użytkownika:



CREATE SYNONYM HR.EMPS FOR HR.EMPLOYEES;



Jeśli nazwiemy synonim prywatny i publiczny tak samo, system domyślnie będzie odwoływał się do synonimu prywatnego.



Tworząc synonim możemy również zastosować klauzulę OR REPLACE:



CREATE OR REPLACE SYNONYM HR.EMPS FOR HR.EMPLOYEES;



Sprawi ona, że jeżeli synonim o takiej nazwie już istnieje, zostanie podmieniony, a jeśli nie istnieje, zostanie utworzony.

Kasowanie synonimów




Aby skasować synonim prywatny :



DROP SYNONYM EMPS;



Aby skasować synonim publiczny:



DROP PUBLIC SYNONYM EMPS;



Aby skasować synonim ze schematu innego użytkownika:



DROP SYNONYM HR.EMPS;

Synonimy dla procedur, funkcji i pakietów




Tworzę w schemacie użytkownika SYS procedurę WITACZ:



CREATE PROCEDURE WITACZ(IMIE VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('WITAJ '||IMIE);
END;



Następnie tworzę publiczny synonim o nazwie WITACZ dla procedury WITACZ. Zauważ że podając nazwę procedury, nie podaję jej parametrów:



CREATE PUBLIC SYNONYM WITACZ FOR WITACZ;



Ponieważ sama możliwość sięgnięcia do synonimu publicznego przez użytkownika HR nie sprawia że może on wywołać procedurę której dotyczny synonim, nadaję osobne uprawnienia do tej procedury:



GRANT EXECUTE ON WITACZ TO HR;



Z poziomu użytkownika HR wywołuję procedurę korzystając z synonimu:



EXECUTE WITACZ('ANDRZEJ');



W sposób analogiczny odbywa się to dla funkcji.



Nie możemy nadawać synonimów dla procedur i funkcji znajdujących się w pakietach. Możemy za to nadać synonim pakietowi:



CREATE PUBLIC SYNONYM PISANIE FOR DBMS_OUTPUT;



Następnie korzystając z synonimu dla pakietu, odwoływać się do elementów tego pakietu:



EXECUTE PISANIE.PUT_LINE('HELLO!');

poniedziałek, 14 października 2013

Wielotabelowy INSERT (Multitable Insert)

Wielotabelowy insert (Multitable insert)

Przypuśćmy że wynik poniższego zapytania:



select department_name, count(*), max(salary), min(salary),round(avg(salary))

from employees join departments using(department_id) group by department_name;



Chciałbym załadować do kilku tabel, rozbijając wynik na wiele elementów. Przykładowo do tabeli „liczba_pracowników” trafiłaby nazwa departamentu i liczba pracowników, do tabeli „srednie_zarobki” trafiłaby nazwa tabeli i średnie zarobki etc.
W pierwszej kolejności tworzę tabele do których następnie załaduję dane:

create table liczba_pracownikow(
nazwa_departamentu varchar2(50),
liczba integer
);

create table najwyzsze_zarobki(
nazwa_departamentu varchar2(50),
kwota number
);

create table najnizsze_zarobki(
nazwa_departamentu varchar2(50),
kwota number
);

create table srednie_zarobki(
nazwa_departamentu varchar2(50),
kwota number
);


Mógłbym teraz zastosować kilka osobnych insertów w taki sposób:

insert into liczba_pracownikow select department_name,count(*)
from employees join departments using(department_id) group by department_name;
insert into najwyzsze_zarobki select department_name,max(salary)
from employees join departments using(department_id) group by department_name;
insert into najnizsze_zarobki select department_name,min(salary)
from employees join departments using(department_id) group by department_name;
insert into srednie_zarobki select department_name,round(avg(salary))
from employees join departments using(department_id) group by department_name;

Jednak ma to zasadnicze wady:

  • Robiąc kilka insertów na podstawie zapytań, doprowadzam do wielokrotnych skanów tabeli źródłowej. To problematyczne i nieefektywne przy dużych tabelach.
  • Pomiędzy poszczególnymi insertami dane w tabeli źródłowej mogą ulec zmianie, więc efekty mogą być niespójne

    Zamiast wielu insertów, możemy zrobić jeden insert wielotabelowy:

    insert all
    into liczba_pracownikow(nazwa_departamentu,liczba) values(department_name,liczba)
    into najwyzsze_zarobki(nazwa_departamentu,kwota) values(department_name,maksi)
    into najnizsze_zarobki(nazwa_departamentu,kwota) values(department_name, mini)
    into srednie_zarobki(nazwa_departamentu,kwota) values(department_name,srednia)
    select department_name,
    count(*) liczba, max(salary) maksi, min(salary) mini,round(avg(salary)) srednia
    from employees join departments using(department_id) group by department_name;


    W tym przypadku, skan po tabeli źródłowej wykonywany jest raz, a odczytane dane są rozbijane po czterech tabelach na raz. Ponadto dane pozostają spójne. Przyjrzyjmy się planowi wykonania zapytania:





poniedziałek, 7 października 2013

Pivot (Tabele przestawne) w Oracle

Pivot (tabele przestawne)


Pivot (tabele przestawne) jest dostępny od wersji 11g Oracle. Umożliwia nam przestawianie kolumn w miejsce wierszy i odwrotnie. Jest to wspaniałe narzędzie analityczne, pozwala wygodnie przeglądać np. tendencje. Pivot jest operacją agregującą. Przykład działania pivota:


Przed:



Po:



Prawda że czytelniej?
Przejdziemy teraz całą ścieżkę krok po kroku. W pierwszej kolejności tworzymy tabelę:

create table wyniki_sprzedazy(
okres varchar2(50),
produkt varchar2(50),
suma_przychodu number
);



Następnie ładujemy do niej dane:


insert into wyniki_sprzedazy values('06-2013','Pralki',65040);
insert into wyniki_sprzedazy values('07-2013','Pralki',31650);
insert into wyniki_sprzedazy values('08-2013','Pralki',24678);
insert into wyniki_sprzedazy values('06-2013','Telewizory',40600);
insert into wyniki_sprzedazy values('07-2013','Telewizory',12300);
insert into wyniki_sprzedazy values('08-2013','Telewizory',9004);
insert into wyniki_sprzedazy values('06-2013','Lodówki',45080);
insert into wyniki_sprzedazy values('07-2013','Lodówki',21600);
insert into wyniki_sprzedazy values('08-2013','Lodówki',4670);
commit;


Zajrzyjmy teraz do tabeli:

select * from wyniki_sprzedazy;



Dane przedstawione są w tej chwili w sposób płaski i mało czytelny. Zawartość tabeli reprezentuje sprzedaż trzech produktów na przestrzeni trzech miesięcy.
Przestawimy teraz troszeczkę te dane. Chcemy wyświetlić jak zmieniała się sprzedaż produktów na przestrzeni czasu. Robimy pewnego rodzaju szachownicę gdzie jeden bok jest wyznaczany przez produkty, a drugi przez okresy. Na przecięciu kolumn i wierszy ma znaleźć się suma sprzedaży danego produktu w danym okresie.


select * from wyniki_sprzedazy pivot
(
sum(suma_przychodu) for okres in ('06-2013','07-2013','08-2013')
)
;



sum(suma_przychodu) to element agregacji, musi się tutaj pojawić.
For okres in wyznacza kolumny jakie mają powstać na podstawie danych z kolumny okres.


Pełna składnia polecenia PIVOT:


SELECT …..
FROM …..
PIVOT
(
PIVOT_CLAUSE
PIVOT_FOR_CLAUSE
PIVOT_IN_CLAUSE
) WHERE......

PIVOT_CLAUSE to funkcja wyliczeniowa (agregująca) u nas jest to sum(suma_przychodu).
PIVOT_FOR_CLAUSE określa dane na podstawie których mają powstać kolumny. U nas jest to klauzula „for okres ….” która definiuje na podstawie jakich danych powstaną kolumny.
PIVOT_IN_CLAUSE to lista wartości z danych określonych w PIVOT_FOR_CLAUSE na podstawie których powstają kolumny. Tutaj „in('06-2013','07-2013','08-2013')”. Krótko mówiąc PIVOT_FOR_CLAUSE określa z danych jakiej kolumny w źródle mają powstać kolumny w pivocie, a PIVOT_IN_CLAUSE określa zakres tychże danych -tj. ile kolumn ma być.

Powstającym kolumnom podczas pivotowania możemy nadać aliasy:


select * from wyniki_sprzedazy pivot
(
sum(suma_przychodu) for okres in ('06-2013' czerwiec,'07-2013' lipiec,'08-2013' sierpien)
)
;





Robimy to wewnątrz PIVOT_IN_CLAUSE.


Dane wynikowe możemy też sortować:


select * from wyniki_sprzedazy pivot
(
sum(suma_przychodu)
for okres
in ('06-2013' czerwiec,'07-2013' lipiec,'08-2013' sierpien)
) ORDER BY 1; 




Klauzulę dotyczącą sortowania dajemy na samym końcu całego zapytania.
Inny przykład – tym razem z użyciem schematu HR. Mamy w tabelce employees kolumny JOB_ID DEPARTMENT_ID. Job_id to indentyfikator zawodu wykonywanego przez pracownika, department_id to identyfikator departamentu w którym dany pracownik pracuje. Chcielibyśmy teraz zrobić jakieś zestawienie obrazujące ilość pracowników na różnych stanowiskach w różnych departamentach. Zaczynamy więc od wykonania grupowania:


select job_id,department_id,count(*) from employees
group by job_id,department_id;



Musieliśmy ograniczyć całą zawartość tabeli do tych dwóch kolumn (job_id i department_id) oraz jednej kolumny z agregacją (count(*)). Pozostałe kolumny nie są nam potrzebne, mając więcej niż 3 wartość ( X , Y i wartość na przecięciu X – Y) wynik byłby całkowicie niezrozumiały. Dane jako takie już mamy przygotowane, są jednak przedstawione w formie płaskiej. Teraz chcielibyśmy te dane troszeczkę przesunąć i stworzyć z nich „szachownicę” czyli tabelę przestawną.

select * from (
select job_id,department_id,count(*) liczba from employees
group by job_id,department_id
) pivot (
sum(liczba) for department_id in (10,20,30,40,50,60,70,80,90,100,110)
);

Zauważ że po FROM może znaleźć się nie tylko nazwa tabeli, ale również całe zapytanie: