Adattárházak elemzése dióhéjban

By 2016.05.11Adatbázisok, sql

Barangolás a végrehajtási tervek körül az Oracle-ben

 

Napjainkban – minden kétséget kizáróan – kijelenthető, hogy az adatbáziskezelők (DBMS) közül a relációsak (RDBMS) a legnépszerűbbek. Előkelő helyen szerepel ezek között is az Oracle adatbáziskezelője.
A relációs adatbáziskezelők natív programozási nyelve az SQL.
Az SQL a Structured Query Language rövidítése, a továbbiakban mi is ezt használjuk.
Az SQL nyelvek (mert hogy több nyelvjárása is létezik) népszerűségüket az alábbiaknak köszönhetik:

1. Alap szinten nagyon egyszerű a használatuk.
2.Az SQL deklaratív nyelv (tehát nem procedurális!).
3. Szabványos ISO és ANSI szerint is (a „kilengésektől” most tekintsünk el).
4. Az SQL negyedik generációs nyelv!

Azaz, az átlagos programozó azt fogalmazza meg, hogy mit szeretne és nem azt, hogy hogyan!
A továbbiakban ez utóbbi témára fókuszálunk.
Az átlagos, de főként kezdő SQL fejlesztő azt igyekszik megfogalmazni, hogy honnan és milyen adatokkal akar miféle számításokat, kimutatásokat készíteni.
Példáinkban az Oracle 12c adatbázis kezelőjét és annak példa sémáit (Sample Schemas:HR,SH) fogjuk használni.

I. Lekérdezések kettő vagy több táblából: IN versus Join

Gyakori szituáció, hogy többtáblás lekérdezéseket valósíthatunk meg allekérdezésekkel is, és táblák összekapcsolásával is. Jogos lehet a kérdés, hogy melyik a hatékonyabb. A válasz nem triviális, általában csak az ún. végrehajtási tervek vizsgálatával – olykor azokkal sem – derül ki.
A végrehajtási terv egy adott SQL utasítás végrehajtásához szükséges lépések (taskok) összessége hierarchikus formában.
A végrehajtási tervek készítése az Oracle Optimalizálójának (Optimizer) a feladata, amit a programozó megtekinthet, és szükség esetén befolyásolhatja annak jövőbeni elkészítését (lásd Hint-ek, légy nagyon óvatos és körültekintő!).
Tekintsük a következő egyszerű példát:
Ismervén a HR séma EMPLOYEES és DEPARTMERNTS táblájának szerkezetét és tartalmát, feltehetjük az alábbi kérdést:
„Kik azok a dolgozók, akik az 1700-as kódú város valamelyik osztályán dolgoznak?”
Témánk szempontjából két megoldás is kézen fekvőnek látszik .

1. Használjuk az IN operátort

SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments WHERE location_id = 1700);

sql1

 

 

2. Használjuk az egyen-összekapcsolást (Equi-join)

SELECT last_name, e.department_id, job_id
FROM employees e,departments d
WHERE e.department_id =d.department_id and location_id = 1700;

sql2_zerotohero

 

 

3. Hasonlítsuk össze a végrehajtási terveket!

sql3_zerotoherosql4_zerotohero

Mint látható, mind a két lekérdezés esetén az optimalizáló ugyanazt a tervet (lásd Plan Hash Value! – bármi is az) használta. Kijelenthető – esetünkben- hogy a két utasítás ugyanolyan hatékony (vagy nem!)

II. Korrelált allekérdezések és/vagy explicit összekapcsolások

Vannak olyan esetek, amikor az allekérdezés nem független a főlekérdezéstől, ilyenkor korrelált allekérdezésről beszélünk. Felmerülhet ekkor is, hogy explicit összekapcsolással helyettesítsük-e a korrelált lekérdezést.
Látni fogjuk, hogy a döntés nem mindig egyszerű.

1. Keressük ki azon dolgozókat az EMPLOYEES táblából, akik többet keresnek, mint saját osztályuk átlag fizetése. Az egyik lehetséges megoldás a következő:
SELECT e.last_name, e.department_id, e.salary
FROM employees e
WHERE e.salary> (SELECT AVG(salary) FROM EMPLOYEES WHERE department_id=e.department_id);

2. A másik  lehetséges megoldás explicit összekapcsolást használ  az alap tábla és egy ún. inline nézet között: (esetünkben ’d’ álnévvel illettük ezt a halmazt).

SELECT e.last_name, e.department_id, e.salary ,d.average
FROM employees e,
(SELECT department_id,avg(salary) average nézet
FROM employees GROUP BY department_id ) d
WHERE e.department_id=d.department_id AND e.salary>d.average ;

3. Tekintsük meg ismét a végrehajtási terveket!sql5_zerotoherosql6_zerotohero
Vegyük észre, hogy a két terv lényegileg azonos. Azaz a tervek azonosítói bár különbözőek, de a tervek tartalma azonos, eltekintve attól, hogy az utóbbi tervben az optimalizáló által létre hozott közbülső adathalmaz nevet kapott (VW_SQ_1.)

III. Adattárházak elemzése, ízelítő középfokon

Az adattárházak napjainkban fontos szerepet játszanak a döntéshozók életében (jegyezzük meg, ez a céljuk az adattárházaknak).
A következőkben pár – közepes nehézségűnek minősített – lekérdezést láthatunk az Oracle SH (Sales History) és a HR sémájára vonatkozóan.

1. Rangsoroljuk  a termékeket  kategóriák szerint a bevétel szempontjából.

A döntéshozókat azonban csak az első 3 legjobban teljesítő termék érdekli:
SELECT T.*
FROM (SELECT t.calendar_quarter_desc quarter ,P.prod_name, SUM(s.amount_sold) amount, SUM(s.quantity_sold),
RANK() OVER (PARTITION BY t.calendar_quarter_desc
ORDER BY t.calendar_quarter_desc,
SUM(s.amount_sold) DESC ) RANK
from sh.sales s, sh.times t , sh.products P
WHERE s.time_id=t.time_id AND S.prod_id=P.prod_id
GROUP BY t.calendar_quarter_desc ,P.prod_name) T
WHERE T.rank<=3
ORDER BY quarter,amount DESC;

sql7_zerotohero

 

 

 

 

2. A döntéshozók szeretnék összehasonlítani a bevételi adatokat az egy évvel korábbi, azonos időszakban elért adatokkal. Látni kívánják a halmozott (cumulative sum) értékeket minden évre.

SELECT t.calendar_month_desc,
MAX (t.calendar_month_name) MONTH_NAME ,
SUM(s.quantity_sold) AS quantity_sold,
LAG (SUM(s.quantity_sold),12) OVER (ORDER BY
t.calendar_month_desc ) AS year_before,
LEAD(SUM(s.quantity_sold),12) OVER (ORDER BY
t.calendar_month_desc ) AS year_after,
SUM (SUM(s.quantity_sold))
OVER(PARTITION BY t.calendar_year
ORDER BY t.calendar_month_desc RANGE UNBOUNDED PRECEDING )
AS cum_sum
FROM sh.sales s, sh.times t
WHERE s.time_id=t.time_id
GROUP BY t.calendar_year ,t.calendar_month_desc ;

sql8_zerotohero

 

 

 

 

 

 

3. A HR menedzser kimutatást szeretne látni a dolgozók fizetéséről, azok arányáról az osztály összesített fizetéséhez képest (RATIO_TO_REPORT), a halmozott fizetéseket – szintén – osztályonként, valamint a halmozott értékek arányát az osztályok összesített fizetéséhez képest.

SELECT department_id,last_name,salary,rr,cum_sum,
TO_CHAR(cum_sum /total_sum,'0.999') rate_for_cum_sum
FROM
(SELECT department_id, last_name, salary,
TO_CHAR(RATIO_TO_REPORT(salary) OVER (PARTITION BY
department_id),'0.999') AS rr,
SUM(salary) OVER ( partition by department_id ORDER BY salary desc
RANGE UNBOUNDED PRECEDING) cum_sum,
SUM(salary)
OVER (partition by department_id ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_sum
FROM employees) t
ORDER BY department_id, salary desc;

sql9_zerotohero

 

 

 

 

Amennyiben a fenti példák felkeltették érdeklődését, keresse fel egyesületünket (PCF – People Come First), mert igény esetén a fenti témákban előadásokat tartunk .
A szerző elérhetősége: laszlo.czinkoczki<kukac>webvalto.hu

Leave a Reply

22 − 18 =