Ugrás a fő tartalomra

Adattárház táblák ösfeltőltése és technikája

 Az ősbetöltés (initial load) az adattárház töltési folyamatának egyik legfontosabb lépése, amely során egy cél adattárházi táblát először töltünk fel teljes adatmennyiséggel. Ez az első betöltés biztosítja az adattárház alapállapotát, amelyre a későbbi incrementális (növekményes) frissítések épülnek.




Mikor van szükség ősbetöltésre?

✅ Új adattárház vagy új adatpiac (Data Mart) létrehozásakor
✅ Új dimenzió- vagy ténytábla bevezetésekor
✅ Rendszermigráció vagy nagyobb architekturális változások után
✅ Adattisztítás és újratöltés esetén (pl. sérült adatok helyreállítása)
✅ Archiválás után történő adatok visszatöltésénél

Ősbetöltés folyamata az adattárházban

Az initial load folyamata több lépésből áll:

1️⃣ Forrásrendszer adatainak kinyerése (Extract)

  • Adatok lekérése a forrásrendszerből SQL-lel, ETL eszközzel vagy export/import módszerekkel.
  • Példa forrásadatok lekérésére:
    SELECT * FROM source_table;

2️⃣ Adattisztítás és transzformáció (Transform)

  • Duplikációk kezelése, formátumok egységesítése, adattisztítás.
  • Példa null értékek kezelésére:
SELECT NVL(customer_name, 'UNKNOWN') FROM source_table;


       Példa formátumok egységesítésére

SELECT UPPER(TRIM(customer_name)) FROM source_table;

3️⃣ Betöltés az adattárházba (Load)

  • Az adatok beillesztése az adattárház cél táblájába.
  • Példa INSERT használatával:
    INSERT INTO dw_customer (customer_id, customer_name, address, phone)
SELECT customer_id, customer_name, address, phone FROM source_table

Nagyobb adatmennyiségnél SQL*Loader vagy Oracle External Table használata ajánlott.


Ősbetöltés kivitelezése Oracle-ben

Az adattárház betöltéséhez az alábbi Oracle megoldások használhatók:


INSERT INTO SELECT – Egyszerű betöltés kisebb adatmennyiségre

Ha a forrásadatok közvetlenül elérhetők, az alábbi SQL-lel egyszerűen átmásolhatók:

INSERT INTO dw_customer (customer_id, customer_name, address, phone)
SELECT customer_id, customer_name, address, phone FROM source_table;
COMMIT;

Előnye: Gyors és egyszerű.
Hátránya: Nagy adatmennyiségnél rollback és lock problémák léphetnek fel.


Direct Path Insert (INSERT /+ APPEND / SELECT)

Ha több millió rekordot kell betölteni, a DIRECT PATH módszer javasolt, amely gyorsítja a betöltést és minimalizálja az UNDO és REDO terhelést:

INSERT /*+ APPEND */ INTO dw_customer 
SELECT * FROM source_table;
COMMIT;



Fontos:
  • Az APPEND utasítás miatt az Oracle új extenteket foglal le, és a meglévő blokkokat nem használja.
  • Tranzakciós módosítások rollbackje nem lehetséges!

SQL*Loader – Nagy adatmennyiségnél fájl alapú betöltés
Ha a forrásadatok CSV, TXT vagy más fájlformátumban érkeznek, az SQL*Loader egy hatékony eszköz:
📌 Példa SQL*Loader vezérlő fájlra (customer.ctl):
LOAD DATA 
INFILE 'customer_data.csv' 
INTO TABLE dw_customer 
FIELDS TERMINATED BY ',' 
(customer_id, customer_name, address, phone)


Parancssorból
sqlldr username/password control=customer.ctl log=customer.log



Előnye: Nagyon nagy adatmennyiségnél gyorsabb, mint az INSERT.
Hátránya: Fájlkészítés szükséges.



 Partition Exchange Load – Extrém gyors betöltés
Ha egy nagy táblát kell gyorsan betölteni, érdemes particionált táblát használni.
📌 Példa: betöltés egy ideiglenes táblába, majd particiócserével:
ALTER TABLE dw_sales EXCHANGE PARTITION sales_2024_02
WITH TABLE sales_stage;


Előnye:
  • Nagyon gyors, mert nem történik tényleges adatmozgatás, csak particiócsere.
  • Minimalizálja a lockokat.
    Hátránya: Csak particionált táblák esetén működik.


 Ősbetöltés során figyelembe veendő szempontok

🔸 1. Indexek és Constraint-ek kezelése

  • Nagy adatmennyiségnél érdemes először letiltani az indexeket és külső kulcsokat, majd betöltés után újraépíteni.
  • Indexek újraépítése
ALTER INDEX idx_customer REBUILD;


Tranzakciókezelés és teljesítmény

  • Nagy betöltések előtt kapcsoljuk ki az AUTOCOMMIT-et.
  • Commit stratégia:
    • Kisebb adatmennyiségnél egy végső COMMIT ajánlott.
    • Nagyobb adatmennyiségnél batch commit (pl. 10.000 soronként).
DECLARE
  v_counter NUMBER := 0;
BEGIN
  FOR rec IN (SELECT * FROM source_table) LOOP
    INSERT INTO dw_table VALUES rec;
    v_counter := v_counter + 1;
    IF v_counter MOD 10000 = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;



Ősbetöltés utáni lépések
✅ Adattisztítás és ellenőrzés
✅ Indexek újraépítése
✅ Statisztikák frissítése a teljesítmény optimalizálásához


EXEC DBMS_STATS.GATHER_TABLE_STATS('DW_SCHEMA', 'DW_CUSTOMER');


Adatbázis tábla statisztikák

Az Oracle statisztikák (statistics) kulcsfontosságú szerepet játszanak az SQL lekérdezések 
teljesítményében, mert az Oracle Optimizer ezek alapján dönt az optimális végrehajtási tervről.

Ha a statisztikák nem megfelelően készülnek el, az adatbázis rossz végrehajtási terveket 
generálhat, ami teljesítménycsökkenést eredményezhet.

👉 A cél: A statisztika készítési stratégiát úgy kell optimalizálni, hogy az mindig a 
lehető legfrissebb és pontosabb képet adja az adateloszlásról, miközben minimalizáljuk a 
statisztikai számítások erőforrásigényét.



Hogyan lehet finomhangolni a statisztika készítést?
🔹 1. GYAKORISÁG MEGHATÁROZÁSA – Mikor frissítsük a statisztikákat?
📌 Alapértelmezett módszer:
Oracle automatikusan kezeli az auto_task mechanizmuson keresztül az éjfél és reggel 6 közötti időszakban. Ez jó kiindulási alap, de nagy forgalmú táblák esetében nem mindig elegendő.
📌 Finomhangolási lehetőségek:
  • Naponta frissülő adatokhoz:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', ESTIMATE_PERCENT => 20);





Csak ha jelentős változás történt (pl. 10% változás felett):
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', OPTIONS => 'GATHER AUTO');



Ütemezett statisztika frissítés (DBMS_SCHEDULER segítségével, pl. napi éjjel 2-kor)
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'UPDATE_STATS_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''SCHEMA_NAME''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled         => TRUE
  );
END;



✅ Előny: Csökkenti a statisztikai frissítés erőforrásigényét, és biztosítja, hogy csak szükséges esetben frissítsük.
❌ Hátrány: Ha túl ritkán futtatjuk, az Oracle elavult statisztikákkal dolgozik, ami rossz lekérdezési terveket eredményezhet.


🔹 2. ADATMEZŐK ELOSZLÁSÁNAK KEZELÉSE – Histogramok használata
Ha egy adott oszlop nem egyenletesen oszlik el, akkor egy histogram segíthet az Optimizernek abban, hogy jobb becsléseket adjon a lekérdezésekhez.
📌 Histogram létrehozása, ha egy oszlop adatainak eloszlása nem egyenletes:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', 
METHOD_OPT => 'FOR COLUMNS customer_type SIZE 10');


  • FOR COLUMNS … SIZE 10: Ez azt jelenti, hogy az customer_type oszlophoz 10 különböző bin kategóriát hoz létre.
  • Ha az adatok egyenletesen oszlanak el, histogram használata nem szükséges!

📌 Automatikus histogram generálás (csak ha az oszlop erősen szóródik):

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', 
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');



✅ Előny: Segít az Optimizernek pontosabb döntéseket hozni.
❌ Hátrány: A histogramok felesleges tárolása és karbantartása plusz overhead-et jelenthet.
🔹 3. STATISZTIKAI BECSLÉS FINOMHANGOLÁSA – Mekkora mintát vegyünk?
📌 Alapértelmezett mód:
Oracle alapértelmezetten az ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE beállítást használja, ami dinamikusan határozza meg a mintavételi arányt.
📌 Kézi beállítások:
  • Kisebb táblákhoz (pontosság fontosabb, mint a teljesítmény):
    EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', ESTIMATE_PERCENT => 100);

    Nagyobb táblákhoz (gyorsabb statisztikai számítás kisebb mintavétellel)

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', ESTIMATE_PERCENT => 10);



✅ Előny: Nagy tábláknál jelentősen csökkenti a statisztikai számítás idejét.
❌ Hátrány: Ha túl kicsi a mintavétel, akkor pontatlan statisztikák készülnek.
🔹 4. RÉSZLEGES STATISZTIKA FRISSÍTÉS – Ha csak az új rekordokat akarjuk kezelni
Ha egy táblában folyamatosan csak új adatok kerülnek be, a teljes statisztikai újraszámítás helyett csak a módosított partíciókra lehet fókuszálni.
📌 Csak az utolsó 3 hónap adatainak statisztikai frissítése
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCHEMA_NAME',
    tabname => 'TABLE_NAME',
    partname => 'PARTITION_2024_02'
  );
END;



✅ Előny: Nem kell feleslegesen újraszámítani az egész táblát.
❌ Hátrány: Ha nem megfelelően állítjuk be, az Optimizer rosszul kezeli a régebbi adatokat.
🔹 5. RÉGI STATTISZTIKÁK MENTÉSE – Ha rossz döntést hoztunk, vissza tudjunk térni
Ha egy statisztikai frissítés rosszabb teljesítményt eredményez, visszaállíthatjuk az előző verziót.
📌 Statisztika mentése és visszaállítása
EXEC DBMS_STATS.CREATE_STAT_TABLE('SCHEMA_NAME', 'OLD_STATS');
EXEC DBMS_STATS.EXPORT_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', 'SCHEMA_NAME', 'OLD_STATS');


Ha később vissza kell állítani:
EXEC DBMS_STATS.IMPORT_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME', 'SCHEMA_NAME', 'OLD_STATS');



✅ Előny: Ha egy statisztika frissítés után rossz teljesítmény lép fel, könnyen visszaállítható az előző
❌ Hátrány: Extra tárolóhely szükséges az előző statisztikák tárolására.


Összegzés
🔹 Gyakoriság: Csak akkor frissítsük a statisztikákat, ha szükséges.
🔹 Histogramok: Egyenlőtlen adateloszlás esetén használjuk.
🔹 Mintavétel: Nagy tábláknál kisebb mintát használjunk, de figyeljünk a pontosságra.
🔹 Particionált táblák: Csak az új partíciókon frissítsük a statisztikát.
🔹 Biztonsági mentés: Mindig mentsük az előző statisztikát visszaállítás céljából.

































Megjegyzések

Megjegyzés küldése