czwartek, 21 listopada 2013

Tabele zewnętrzne (External Table) typu DATA_PUMP

Tabele zewnętrzne typu Data_Pump


Od wersji 10g istnieje możliwość szybkiego utworzenia pliku na dysku na podstawie danych z zapytania. Taki plik jest w formacie Oracle Data Pump i może być przez to narzędzie odczytywany. Plik taki możemy też przenieść do innego systemu i podpiąć go jako tabelę zewnętrzną.
Nie możemy wyrzucić danych do dowolnego katalogu na dysku, a jedynie do tych które zostały zamapowane i mamy uprawnienia do zapisu w nich. W pierwszej kolejności mapujemy więc istniejący katalog i nadajemy użytkownikowi stosowne uprawnienie (jako administrator):

create directory temp as 'c:\temp';
grant read, write on directory temp to hr;


Następnie przystępujemy do eksportu:


create table lista_plac
organization external
( type oracle_datapump
default directory temp
location ('lista_plac.dmp')
) as select last_name,first_name,salary from employees;



















Parametr default directory określa alias katalogu w którym ma się znaleźć nasz plik eksportu. Location służy do podania nazwy pliku do jakiego dane mają zostać wyeksportowane. Dane jakie mają zostać wyeksportowane są określane przez zapytanie na końcu instrukcji.
Po takiej tabeli możemy wywoływać select'y jak po każdej innej:





















nie możemy jednak niestety aktualizować danych w takiej tabeli. Pozostanie ona tylko do odczytu.
Plik taki możemy za to przenieść do innego systemu i podpiąć go pod inną bazę.
Po stronie drugiej bazy musimy zamapować katalog w którym plik z danymi się znajdzie, oraz nadać do niego odpowiednie uprawnienia:


create directory dane as 'c:\temp\imporciki';
grant read,write on directory dane to hr;

Następnie tworzymy tabelę:


create table zarobki
(
first_name varchar2(50),
last_name varchar2(50),
salary number
)
organization external
( type oracle_datapump
default directory dane
location ('lista_plac.dmp')
) ;



















Nazwy kolumn w takiej tabeli nie mogą być przypadkowe, muszą odpowiadać nazwom pod jakimi zostały wyeksportowane. Definiujemy katalog w którym znajduje się taki plik, oraz jego nazwę. Z takiej tabeli korzystamy tak jak i wcześniej:





czwartek, 14 listopada 2013

Operatory Exists i Not Exists

Operatory Exists i Not Exists



Operator Exists sprawdza czy kolejne elementy z jednego zbioru znajdują swój odpowiednik w drugim zbiorze. Istotny jest sam fakt zaistnienia odpowiednika po drugiej stronie, a nie ilość wystąpień.



select * from departments d where exists(
select * from employees e where e.department_id=d.department_id

);

























Powyższe zapytanie zwraca nam departamenty z tabeli departments w których pracuje jakikolwiek pracownik. W podzapytaniu występuje warunek where e.department_id=d.department_id , określający zasadę według której system poszukuje odpowiedników w podzbiorze. Brany jest każdy kolejny wiersz z tabeli departments , a następnie przeszukiwana tabela employees w poszukiwaniu wiersza który w polu department_id miałby taką samą wartość. Wystarczy znalezienie jednego odpowiednika i system przestaje skanować dane. Z tego wynika wydajnościowa przewaga operatora EXISTS nad operatorem IN , oraz NOT EXISTS nad operatorem NOT IN.

Możemy też wykonać operację odwrotną, tj. wyświetlić te departamenty, w których nikt nie pracuje tj. które nie znajdują swojego odpowiednika w drugim zbiorze:



select * from departments d where not exists(
select * from employees e where e.department_id=d.department_id

);


czwartek, 7 listopada 2013

Klauzula grouping w SQL

 Grouping



Przy używaniu klauzul Rollup czy Cube powstaje jeden problem. Jak odróżnić które Nulle w wierszach wynikają z tego że dany wiersz to podsumowanie, a które nulle to brak danych? Objawia się ten problem zwłaszcza jeśli wynik posortujemy w taki sposób że podsumowania nie są na dole.
Zobrazujmy to przykładem:



select manager_Id,department_id,count(*), round(avg(salary))
from employees

group by rollup(department_id,manager_id);






















Podświetliłem agregację dla wartości 90 w department_id. Który z dwóch wierszy w których występuje null w kolumnie manager_id to ten z podsumowaniem?

Ten problem rozwiązuje nam zastosowanie klauzuli GROUPING :



select grouping(department_id),grouping(manager_id) ,
manager_Id,department_id,count(*), round(avg(salary)) from employees
group by rollup(department_id,manager_id);



Klauzulę GROUPING stosujemy wobec kolumn wg których grupujemy. Grouping zwróci nam wartość 0 albo 1 w zależności od tego czy dany wiersz zawiera podsumowanie czy nie.

 
Na powyższym przykładzie w najniższym z trzech zaznaczonych wierszy w kolumnie powstałej dzięki „grouping(manager_id)” widzimy wartość 1. To oznacza że null w kolumnie „manager_id” wynika z podsumowania, a nie z braku danych.


piątek, 1 listopada 2013

Klauzula WITH w SQL

Klauzula WITH




Klauzula WITH pozwala między inymi na nieco bardziej przejrzyste budowanie kodu. Pozwala podzapytaniu nadać nazwę i dzięki temu możemy później odwoływać się do niego poprzez tą nazwę, bez potrzeby wielokrotnego wstawiania tego samego kawałka kodu. Przykładowo taki kod:



select last_name,department_name from employees
join (
select * from departments join locations
using(location_id) where city='Seattle'
)

using(department_id);



























Można zamienić na znacznie bardziej przejrzystą postać:




with departamenty_seattle as (
select * from departments join locations
using(location_id) where city='Seattle'
)
select last_name,department_name from employees

join departamenty_seattle using(department_id);