A lekérdezett táblák: HIVAS, KOZPONT, PRIMER

HIVAS   (kozp_azon_hivo, kozp_azon_fog, szol_azon, datum, ora, darab, masodperc)
KOZPONT (primer, kozp_azon, kozp_nev)
PRIMER  (primer, varos, szolgaltato, megye)

A HIVAS tábla dátum oszlop szerint partícionált, 6 partíciója van: 2012. év 01, 02 ... 06 hónapjai
Van egy HIVAS_v2 tábla azonos adattartalommal partícionálás nélkül.

Néhány utasítás, és a futási idejük. A futási idők nem biztos, hogy az aktuális táblákra vonatkoznak,
mivel többször, különböző méretű táblákra is futtattam a lekérdezéseket. Az arányok azonban
nagyjából helytállóak. 
Az alábbi utasítások azt mutatják be, hogy mennyit tud javítani a futási időn a partícionálás, 
index létrehozás, illetve mennyit lassít egy-egy további művelet vagy függvényhívás.
Az utasításokat többször is lefuttattam. A későbbi futásoknál már egyre több adat a memóriában 
van (cache), és nem kell az utasítás elemzését, optimalizálását sem elvégeznie a rendszernek.
Még jobb megoldás lenne a tesztelésre, ha ciklusban mondjuk 10-szer futtanánk le a lekérdezéseket,
és az így kapott futásidőket hasonlítanánk össze.

Az alábbi lekérdezések esetén nézzük meg a végrehajtási terveket is, és azt, hogy az optimalizáló
milyen értékűre becsüli a lekérdezés egyes lépéseinek és egészének költségét (COST).
------------------------------------------------------------------------------------------

--60 sec  INDEX létrehozása
create index hivas_datum_idx on hivas_v2(datum);

-- Nézzük meg a táblák és az index méreteit:
select sum(blocks) from dba_segments where segment_name='HIVAS';
select sum(blocks) from dba_segments where segment_name='HIVAS_V2';
select sum(blocks) from dba_segments where segment_name like 'HIVAS_DATUM_IDX%';

-- számoljuk meg a táblák sorait, nézzük meg a végrehajtási terveket és a futásidőket.
-- ez rögtön egy olyan példa, ahol hint segítségével jelentősen javíthatjuk a futásidőt.
select count(*) from hivas;
select count(*) from hivas_v2;
select /*+ no_index(hivas_v2) */ count(*) from hivas_v2;

-- tábla teljes végigolvasása  (1.3s)
select sum(masodperc) from nikovits.hivas;

-- nem jelent előnyt a partícionálás mivel a teljes táblát beolvassuk (1.3s)
select sum(masodperc) from nikovits.hivas_v2;

-- a plusz feltétel egy kicsit lassít (1.4s)
select sum(masodperc) from nikovits.hivas_v2 where datum < to_date('2012.07.01', 'yyyy.mm.dd');

-- az aritmetikai művelet, amit minden sorra elvégez, sokat lassít (5s)
select sum(masodperc) from nikovits.hivas_v2 where datum + 1 < to_date('2012.07.02', 'yyyy.mm.dd');

-- 1 partíció olvasása sokkal gyorsabb (0.2s)
select sum(masodperc) from nikovits.hivas where datum = to_date('2012.01.31', 'yyyy.mm.dd');

-- tábla olvasása INDEX segítségével sokkal gyorsabb (0.08s)
select sum(masodperc) from nikovits.hivas_v2 where datum = to_date('2012.01.31', 'yyyy.mm.dd');

-- az előző lekérdezés az index letiltásával (0.7s) 
select /*+ no_index(h) */ sum(masodperc) from nikovits.hivas_v2 h
where datum = to_date('2012.01.31', 'yyyy.mm.dd');

-- ahogy egyre több sort kapunk vissza, egyre kevésbé előnyös az index használata (0.9s)
select sum(masodperc) from nikovits.hivas_v2 
where datum between to_date('2012.01.31', 'yyyy.mm.dd') and to_date('2012.02.20', 'yyyy.mm.dd');

-- itt már magától nem is használna indexet, csak a hint hatására (3.5s)
-- index nélkül sokkal gyorsabb (1.2s)
select /*+ index(h) */ sum(masodperc) from nikovits.hivas_v2 h
where datum between to_date('2012.01.31', 'yyyy.mm.dd') and to_date('2012.04.20', 'yyyy.mm.dd');

-- csak INDEX olvasása tábla nélkül (0.02s)
select count(*) from nikovits.hivas_v2 where datum = to_date('2012.01.31', 'yyyy.mm.dd');

-- INDEX FAST FULL SCAN (nem B-fa sorrendben olvasás)  (0.8s)
select count(*) from nikovits.hivas_v2;

-- HINT-tel megtiltjuk, hogy indexet használjon, és ezáltal gyorsabb is lesz a futás. (0.3s)
-- Ez egy példa arra, amikor az optimalizáló nem a legjobb megoldást találja meg.
select /*+ no_index(hivas_v2) */ count(*) from nikovits.hivas_v2;

-- HASH join; hint nélkül ezt választja, pedig van jobb (1.6s)
-- Ez még nem is igazi join, mert az egyik oldalon csak 1 sor lesz
select sum(masodperc) from nikovits.hivas h, nikovits.kozpont k 
where h.kozp_azon_hivo=k.kozp_azon and k.kozp_nev='kozp_370';

-- SORT-MERGE join; ez lassú a nagy tábla rendezése miatt (96s)
select /*+ use_merge(h,k) */ sum(masodperc) from nikovits.hivas h, nikovits.kozpont k 
where h.kozp_azon_hivo=k.kozp_azon and k.kozp_nev='kozp_370';

-- NESTED-LOOPS join; most ez a legjobb, mert az egyik oldalon csak 1 sor van (0.9s)
select /*+ use_nl(h,k) */ sum(masodperc) from nikovits.hivas h, nikovits.kozpont k 
where h.kozp_azon_hivo=k.kozp_azon and k.kozp_nev='kozp_370';

-- Az alábbi lekérdezés már egy igazi join, ahol egy viszonylag kisebb és egy nagy táblát 
-- kapcsolunk össze. Itt már jól látható, milyen hatalmas különbség lehet az egyes algoritmusok
-- között. A NESTED-LOOPS join gyakorlatilag kivárhatatlanul lassú. (kilőttem)
-- Sajnos ha a join feltétel nem egyenlőség, akkor sokszor csak ez az algoritmus jön szóba.

-- HASH join (2.4s)
select /*+ use_hash(h,k) */ k.kozp_nev, sum(masodperc) 
from nikovits.hivas h, nikovits.kozpont k where h.kozp_azon_hivo=k.kozp_azon group by k.kozp_nev
order by 2 desc;



===========================================================
Statisztikák létrehozása, törlése és lekérdezése a táblához.
Statisztikák hiányában az optimalizáló téves költség és méretbecsléseket tehet.

-- a statisztikák létrehozása is lassú (10 sec)
CALL dbms_stats.gather_table_stats('nikovits','hivas_v2');
CALL dbms_stats.delete_table_stats('nikovits','hivas_v2');
CALL dbms_stats.delete_column_stats('nikovits','hivas_v2', 'datum');
BEGIN
 dbms_stats.gather_table_stats('NIKOVITS', 'hivas_v2', method_opt => 'FOR COLUMNS datum SIZE 15' );
END;
/
select table_name, num_rows from user_tables where table_name='HIVAS_V2';

-- A density oszlop a szelektivitásra utal, a histogram pedig annak típusára (FREQUENCY, HEIGHT BALANCED)
select table_name, column_name, num_distinct, low_value, high_value,
       num_nulls, density, num_buckets, histogram
from user_tab_columns where table_name='HIVAS_V2';

(A user_tab_col_statistics kevesebb oszlopot tartalmaz, mint az előző, de a fenti oszlopok abban is megvannak)

-- Hisztogram-beli intervallumok lekérdezése
select table_name, column_name, endpoint_number, endpoint_value
from user_histograms where table_name='HIVAS_V2' order by 1,2,3;