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;

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!');

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:
  
  
  
  
  
  
  

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:

Oracle Outlines

Outlines

Outlines zostały wprowadzone aby zagwarantować stały plan wykonania, bez względu na zmiany w środowisku uruchomieniowym, czy zmiany statystyk. Stają się bardzo użyteczne gdy zechcemy wykonać zapytanie na innym serwerze w dokładnie taki sam sposób jak na pierwotnym, w przypadku upgrade wersji Oracle czy migracji z optymalizatora regułowego do kosztowego.

Outlines są obiektami powiązanymi z zapytaniami, zbiorami hintów optymalizatora kosztowego które są w stanie zmusić go do wykonania zapytania w określony sposób, definiując w sposób jednoznaczny plan zapytania. W pewnym sensie „zamrażają” plan wykonania zapytania.

Dzięki Outlines jesteśmy w stanie wpływać na pracę optymalizatora kosztowego i wybór planu, bez konieczności modyfikacji kodu zapytania. Hinty przechowywane są w słownikach systemowych, są powiązane z konkretnym zapytaniem i optymalizator kosztowy wybiera je automatycznie. Jest to bardzo przydatne w przypadku konieczności optymalizacji aplikacji której kodu źródłowego nie jesteśmy w stanie zmienić. W czasie działania optymalizator kosztowy poddaje treść zapytania normalizacji (tj. np. powiększa wszystkie znaki w wyrażeniach składniowych) i poszukuje w słownikach outlines dla takiego właśnie zapytania. Jeśli znajduje, stosuje je do wygenerowania planu zapytania. Outlines są dostępne od wersji 10g wzwyż, we wszystkich licencjach włącznie z Express Edition.

Tworzenie Outlines

Tworzyć outlines można na dwa sposoby: manualnie, bądź zlecić systemowi automatyczne

generowanie outlines dla wszystkich zapytań z sesji, bądź całej bazy.

Automatyczne generowanie outlines

Automatyczne generowanie outlines możemy uruchomić ustawiając parametr create_stored_outlines na True. Parametr ten można ustawić dla sesji, lub całego systemu. Outlines będą wtedy tworzone odpowiednio dla wszystkich zapytań z sesji, lub dla wszystkich zapytań uruchamianych w ramach instancji.

Uruchamianie automatycznego generowania Outlines w ramach sesji:

ALTER SESSION SET CREATE_STORED_OUTLINES=TRUE;

Wyłączanie automatycznego generowania Outlines w ramach sesji:

ALTER SESSION SET CREATE_STORED_OUTLINES=FALSE;

Uruchamianie automatycznego generowania Outlines w ramach całej instancji (oczywiście potrzebne są do tego uprawnienia):

ALTER SYSTEM SET CREATE_STORED_OUTLINES=TRUE;

Wyłączanie automatycznego generowania Outlines w ramach instancji:

ALTER SYSTEM SET CREATE_STORED_OUTLINES=FALSE;

Manualne generowanie Outlines

Outlines możemy tworzyć również manualnie, dla wybranego zapytania. Jeśli zechcemy robić to jako jakiś inny niż SYS bądź SYSTEM użytkownik, będziemy potrzebowali stosownych uprawnień:



GRANT CREATE ANY OUTLINE TO NAZWA_UZYTKOWNIKA;



Outline dla zapytania tworzymy w taki sposób:



CREATE OUTLINE JOIN_EMP_DEP_ORDER ON
SELECT * FROM EMPLOYEES JOIN DEPARTMENTS
USING(DEPARTMENT_ID) ORDER BY DEPARTMENT_NAME,LAST_NAME;

Przeglądać nasze stworzone outlines możemy w słowniku USER_OUTLINES :

Same hinty powiązane za danym Outline znajdziemy w słowniku USER_OUTLINES_HINTS:

Administrator ma do dyspozycji dwa słowniki DBA_OUTLINES i DBA_OUTLINE_HINTS, w których może przeglądać Outlines i związane z nimi hinty wszystkich użytkowników:

Używanie Outlines

Same outlines mamy już stworzone, jednak nie zostały do tej pory użyte i do czasu aż nie ustawimy parametru QUERY_REWRITE_ENABLED nie będą używane. To czy dany Outline został użyty czy też nie, możemy stwierdzić sprawdzając wartość w kolumnie USED słownika USER_OUTLINES :

Wydaję więc polecenie :



ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

Możemy też uruchomić tą opcję dla całego systemu:



ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;



Musimy jednak dysponować uprawnieniami do zmiany parametrów systemowych.






Outlines są zbierane w grupy, które możemy zdefiniować podczas tworzenia danego zarysu. Jeśli nie zdefiniujemy grupy (kategorii) do której nasz outline ma należeć, zostanie on przypisany do grupy DEFAULT. Niemniej musimy podać systemowi informację, z której grupy ma korzystać:




ALTER SESSION SET USE_STORED_OUTLINES=DEFAULT;

Jeśli tego nie zrobimy, system nie będzie wykorzystywał naszych outlines!

Testy

W dalszej kolejności uruchamiam zapytanie dla którego został stworzony Outline :

Po tej czynności sprawdzam jeszcze wpis w słowniku USER_OUTLINES. Wartość w kolumnie USED dla naszego Outline, powinna zmienić się z UNUSED na USED.

Jeśli tak się nie stało, to mogą być tego trzy przyczyny:

• nie włączyliśmy parametru QUERY_REWRITE_ENABLED
  
• nie ustawiliśmy kategorii używanych Outlines poprzez parametr USE_STORED_OUTLINES
  
• Nasze zapytanie czymś się jednak różni od tego, dla którego Outline został stworzony.
  

Odbudowywanie Outlines

Jeśli zmienią się struktury dostępu, np. zostaną dodane nowe indeksy, warto odtworzyć Outline'y i w ten sposób odświeżyć związane z nimi hinty. W tym celu stosujemy polecenie:



ALTER OUTLINE NAZWA_OUTLINE REBUILD;

Włączanie i wyłączanie wybranych Outlines

Jeśli zechcemy jakiś Outline tymczasowo wyłączyć, nie kasując go trwale wydajemy poniższe polecenie:



ALTER OUTLINE NAZWA_OUTLINE DISABLE;



Później zawsze możemy go włączyć do użycia:



ALTER OUTLINE NAZWA_OUTLINE ENABLE;

Eksport i import Outlines

Niestety Oracle nie dostarcza żadnego narzędzia do przenoszenia między serwerami ani eksportu i importu outlines. Musimy więc sobie poradzić jakoś inaczej. Posłużymy się programami imp oraz exp, dostępnymi we wszystkich licencjach Oracle. Wszystkie outlines znajdują się w słownikach schematu OUTLN. Eksportuje więc ten schemat do pliku:

Muszę teraz zaimportować ten schemat na serwerze na który chcę wdrożyć stworzone wcześniej Outlines.
 

Po operacji importu, musimy po stronie serwera na którym wdrażamy nasze outlines uruchomić ich używanie – tak jak robiliśmy to na serwerze źródłowym:



ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE;
ALTER SYSTEM SET USE_STORED_OUTLINES=DEFAULT;



lub tylko w ramach sesji:



ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
ALTER SESSION SET USE_STORED_OUTLINES=DEFAULT;




Od tej pory outlines będą wykorzystywane tak jak na serwerze źródłowym. Warto sprawdzić jako administrator zawartość słowników DBA_OUTLINES , DBA_OUTLINE_HINTS czy na pewno wszystko się przeniosło właściwie.

Obliczanie odległości na podstawie współrzędnych w PL/SQL

Akurat było mi potrzebne i musiałem zrobić, może komuś też się nada. Nie jest to wyliczenie grzeszące precyzją, ale np. do wyznaczenia miast znajdujących się mniej więcej w promieniu X od punktu się nada :)

declare
dl_a number:=18.3594444;
dl_b number:=21.0508155;
szer_a number:=54.4019444;
szer_b number:=52.3317039;
roznica_szerokosci number;
roznica_dlugosci number;
begin
roznica_szerokosci:=szer_b-szer_a;
roznica_dlugosci:=dl_b-dl_a;
dbms_output.put_line(sqrt(power(roznica_szerokosci,2)+power(roznica_dlugosci,2))*111.1);
end;