Adatpiac töltés, indexek, tömörítések

Integrációs terület → Adatpiac (Data Mart) töltése

Az adatpiac (Data Mart) az adattárház egyik célterülete, ahol az üzleti felhasználók számára optimalizált, témakör-specifikus adatok találhatók. Az adatpiacokat általában dimenzió-tény (star schema vagy snowflake schema) struktúrában hozzuk létre.

Ebben a lépésben az adatok konszolidált, elemzésre kész formába kerülnek, a szükséges üzleti szabályok és aggregációk alkalmazásával.

2. Milyen technikai mezőket használunk az Adatpiacban?

Az adatpiacban az üzleti felhasználók számára optimalizált adatok vannak, így a technikai mezők főként az adatintegritás és a riportálás támogatására szolgálnak.

1️⃣ Dimenziótáblák (pl. D_CUSTOMER) technikai mezői

Mező neve	Jelentése / Szerepe
`DIM_CUSTOMER_ID`	Surrogate key (helyettesítő kulcs), egyedi azonosító a dimenzióban
`SOURCE_CUSTOMER_ID`	Az eredeti ügyfél azonosító a forrásrendszerben
`VALID_FROM`	Az érvényesség kezdete (SCD Type 2 esetén)
`VALID_TO`	Az érvényesség vége (SCD Type 2 esetén)
`IS_CURRENT`	Aktív rekordot jelölő flag (SCD Type 2 támogatás)
`LAST_UPDATE_DATE`	Utolsó módosítás dátuma

2️⃣ Ténytáblák (pl. F_SALES) technikai mezői

Mező neve	Jelentése / Szerepe
`SALES_ID`	Egyedi azonosító (ha szükséges)
`DIM_CUSTOMER_ID`	Külső kulcs a dimenzióhoz
`DIM_PRODUCT_ID`	Külső kulcs a termékdimenzióhoz
`DIM_DATE_ID`	Külső kulcs az idődimenzióhoz
`SALES_AMOUNT`	Eladási összeg
`SALES_QUANTITY`	Eladott mennyiség
`RECORD_SOURCE`	Megmutatja, hogy az adat melyik forrásrendszerből származik
`LOAD_DATE`	Az adatrekord betöltésének dátuma
`BATCH_ID`	A betöltési folyamat azonosítója

3. Milyen szabályokat és elveket kell figyelembe venni?

1️⃣ Teljesítményoptimalizálás és indexelés

Particionálás a nagy ténytáblákon (DATE_ID, REGION_ID szerint)
Materializált nézetek használata a gyors riportálás érdekében

2️⃣ Slowly Changing Dimension (SCD) támogatás

SCD Type 1: régi adat felülírása
SCD Type 2: időbélyeges verziózás (VALID_FROM, VALID_TO, IS_CURRENT)
SCD Type 3: korlátozott történetkezelés egy új oszlopban

3️⃣ Delta képzés és idősoros kezelés

A ténytáblákban mindig idődimenzióra hivatkozunk (DIM_DATE_ID), hogy az idősoros elemzések elvégezhetők legyenek

4️⃣ Adatintegritás és üzleti szabályok alkalmazása

Külső kulcsok (FK_CUSTOMER_ID, FK_PRODUCT_ID) biztosítják az adatkapcsolatokat
Üzleti szabályok alkalmazása (pl. negatív eladási mennyiség kiszűrése)

5️⃣ Adatfrissítési stratégia

Full refresh vagy inkrementális töltés?
- Ténytáblákban inkrementális töltés (MERGE, UPSERT)
- Dimenziók esetén SCD szabályok betartása

Adattárház teljesítményoptimalizálási megoldások

Az adattárházak hatékony működéséhez olyan optimalizálási technikákra van szükség, amelyek gyorsítják a lekérdezéseket, csökkentik az erőforrásigényt és optimalizálják a tárhelyhasználatot.

Ebben a szakaszban az alábbi három fő megoldásra fókuszálunk:

Particionált táblák
Materializált nézetek
Egyéb teljesítményoptimalizálási megoldások

Particionált táblák

A particionálás egy nagy méretű táblát kisebb, logikailag elkülönített részekre oszt, amelyeket a lekérdezések hatékonyabban tudnak feldolgozni.

Milyen előnyökkel jár a particionálás?

✅ Gyorsabb lekérdezések → A WHERE feltételek szűkítik a bejárandó adatokat, nem kell az egész táblát beolvasni.
✅ Kevesebb memória- és CPU-használat → A feldolgozás célzottan a szükséges partíciókra korlátozódik.
✅ Adatkezelés egyszerűbb → Lehetőség van csak egy adott partíció betöltésére, archiválására vagy törlésére anélkül, hogy az egész táblát érintenénk.

Particionálási módszerek

Típus	Leírás	Tipikus használat
Range Partitioning	Az adatokat egy adott tartomány szerint osztja fel (pl. dátum).	Nagy mennyiségű idősoros adatok (pl. napi/havi eladások)
List Partitioning	Egy adott oszlop előre meghatározott értékei szerint osztja az adatokat.	Pl. országkód, régió, ügyfélkategória
Hash Partitioning	Egy adott oszlop hash-értéke alapján osztja az adatokat, hogy egyenletes terhelést biztosítson.	Nagy, egyenletes elosztású adathalmazok, pl. ügyfélrekordok
Composite Partitioning	Több particionálási módszer kombinációja (pl. range + hash).	Nagy, vegyes struktúrájú adatok, pl. idő + földrajzi terület

Példa: Range Partitioning (Oracle)

CREATE TABLE SALES (

SALES_ID NUMBER,

SALES_DATE DATE,

SALES_AMOUNT NUMBER

)

PARTITION BY RANGE (SALES_DATE) (

PARTITION sales_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),

PARTITION sales_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),

PARTITION sales_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),

PARTITION sales_2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))

);

🔹 Lekérdezéskor az Oracle automatikusan csak a releváns partíciókat olvassa be

🔹 Új negyedév betöltésekor elegendő egy új partíciót hozzáadni

Materializált nézetek (Materialized Views)

A materializált nézet egy fizikai másolat (cache) egy lekérdezés eredményéről, amely időszakosan vagy azonnal frissíthető, így a lekérdezések jelentősen gyorsulhatnak.

Előnyei

✅ Lényegesen gyorsabb lekérdezések → Az eredmények előre kiszámítva tárolódnak.

✅ Aggregációk előre számolhatók → KPI-k, összegzések, idősoros adatok lekérdezése felgyorsul.

✅ Lekérdezés-újrafelhasználás → Az adatok tárolt másolata csökkenti az alapadatok terhelését.

Mikor hasznos?

🔹 Összegzett, aggregált adatoknál (pl. havi értékesítések)

🔹 Bonyolult csatlakozásoknál (pl. több táblából származó ügyfél- és tranzakciós adatok)

🔹 Statikus vagy ritkán változó adatoknál (pl. termékkategóriák, ügyfélhierarchia)

Példa: Materializált nézet létrehozása (Oracle)

CREATE MATERIALIZED VIEW MV_MONTHLY_SALES

BUILD IMMEDIATE

REFRESH FAST ON COMMIT

SELECT

TO_CHAR(SALES_DATE, 'YYYY-MM') AS MONTH,

SUM(SALES_AMOUNT) AS TOTAL_SALES

FROM SALES

GROUP BY TO_CHAR(SALES_DATE, 'YYYY-MM');

🔹 FAST REFRESH → Csak az új vagy módosult adatok frissülnek

🔹 ON COMMIT → Automatikusan frissül, amikor az alapadatok változnak

Egyéb optimalizálási megoldások

Indexek használata

✅ B-tree index* → Gyors keresésekhez használható (WHERE feltételeknél)
✅ Bitmap index → Kevés egyedi értéket tartalmazó oszlopokhoz (pl. nem, státusz, kategória)

Partition-wise joins (Oracle)

✅ Ha mindkét tábla azonos particionálási logikát használ, az Oracle csak a megfelelő partíciókat fogja összekapcsolni.

🔹 Különösen fontos ténytábla + dimenzió kombinációknál

Parallel Query Execution

✅ Oracle Parallel Query: Nagy lekérdezések több processzoron párhuzamosan futnak, csökkentve a válaszidőt.
🔹 Hasznos nagy méretű ténytáblák elemzésénél

BigQuery vagy Snowflake esetén: Clustering és Micro-Partitioning

✅ Google BigQuery: Clustering → Adatokat logikailag tárolja egy adott oszlop alapján
✅ Snowflake: Micro-Partitioning → Automatikusan rendezi az adatokat a háttérben

Indexelési stratégiák és index típusok az adatbázisokban és adattárházban

Az indexek az adatbázis teljesítményének egyik legfontosabb optimalizációs eszközei. Gyorsabb keresést, szűrést és kapcsolásokat tesznek lehetővé, csökkentve az I/O műveletek számát.

Egy jól megválasztott index stratégia jelentősen csökkentheti a lekérdezések végrehajtási idejét, de a rosszul megválasztott indexek felesleges tárhelyet foglalhatnak, lassíthatják a DML műveleteket (INSERT, UPDATE, DELETE) és növelhetik az index karbantartási költségeket.

Indexelési stratégiák

Az indexek alkalmazásának megtervezésénél az alábbi stratégiákat érdemes követni:

1️⃣ Index csak akkor szükséges, ha valóban gyorsítja a lekérdezést

Olyan oszlopokat indexeljünk, amelyeket gyakran használunk a keresésekhez, szűrésekhez (WHERE), rendezésekhez (ORDER BY) vagy csatlakozásokhoz (JOIN).
Ha az adott oszlop gyakran frissül (UPDATE/DELETE), az index lassíthatja az adatbázist a karbantartási költségek miatt.

2️⃣ Válasszuk meg a megfelelő index típust

Nem minden lekérdezéshez ugyanaz az index típus optimális.
Például OLTP rendszereknél (gyakori beillesztés/frissítés) B-tree index ajánlott, míg OLAP esetén (adattárház) a Bitmap index lehet hatékony.

3️⃣ Tartsuk szem előtt az indexek karbantartási költségeit

Túl sok index rontja az adatbázis teljesítményét, mert minden INSERT/UPDATE/DELETE művelet frissíti az indexeket is.
Az indexek folyamatos töredezettsége miatt időszakos REBUILD vagy REORGANIZE szükséges lehet.

Főbb index típusok és alkalmazásuk

1️⃣ B-tree (Balanced Tree) Index – Az általánosan használt index

🔹 Mikor érdemes használni?
✔ Gyakori keresésekhez (WHERE feltételeknél, pl. WHERE customer_id = 1001)
✔ Rendezett visszakeresésekhez (ORDER BY)
✔ OLTP rendszereknél → gyors egyedi rekordkeresés

🔹 Előnyök:
✅ Gyors keresés és szűrés, mert logaritmikus időben (O(log n)) működik
✅ Hatékony az egyedi és kis számú találatokat visszaadó lekérdezéseknél

🔹 Hátrányok:
❌ Nagy frissítési és törlési műveleteknél (UPDATE, DELETE) lassíthatja a rendszert
❌ Töredezetté válhat, ezért rendszeres karbantartásra van szükség

📌 Példa Oracle-ben B-tree index létrehozására:

CREATE INDEX idx_customer_name ON customers (customer_name);

2️⃣ Bitmap Index – OLAP rendszerekhez optimalizálva

🔹 Mikor érdemes használni?
✔ Olyan oszlopokra, amelyekben kevés egyedi érték található (pl. nem, státusz, országkód)
✔ Nagy méretű adattárházakban (OLAP)
✔ Több feltételes keresés (AND, OR) esetén, például ha több oszlopon is szűrünk egy lekérdezésben

🔹 Előnyök:
✅ Kevés helyet foglal, mert a bitmátrix tömöríti az adatokat
✅ Több szűrő egyidejű alkalmazásánál (AND, OR, NOT) kiemelkedően hatékony
✅ Adattárházakban gyors, mert a rekordok frissítése ritka

🔹 Hátrányok:
❌ Nem hatékony gyakran frissített oszlopok esetén, mert minden változásnál az egész bitmátrixot frissíteni kell
❌ Nem ideális OLTP rendszerekhez, mert lassítja az adatkezelést

📌 Példa Oracle-ben Bitmap index létrehozására:


CREATE BITMAP INDEX idx_customer_gender ON customers (gender);


 Unique Index (Egyedi index) – Egyedi értékek garantálására
🔹 Mikor érdemes használni?
✔ Egyedi kulcsokra (PRIMARY KEY, UNIQUE CONSTRAINT)
✔ Keresések gyorsítására olyan oszlopoknál, ahol minden érték egyedi (pl. email, social_security_number)
🔹 Előnyök:
✅ Biztosítja az egyediséget
✅ Gyors keresés egyedi értékek esetén
🔹 Hátrányok:
❌ Csak akkor használható, ha az oszlop valóban egyedi értékeket tartalmaz
📌 Példa Oracle-ben Unique index létrehozására:
CREATE UNIQUE INDEX idx_customer_email ON customers (email);

4️⃣ Composite (Összetett) Index – Több oszlopos index
🔹 Mikor érdemes használni?
✔ Ha több oszlopot gyakran együtt használunk a keresésben
✔ Ha az oszlopok között van erős korreláció, pl. last_name, first_name
🔹 Előnyök:
✅ Kevesebb index szükséges, mert több oszlopot lefed
✅ Gyorsabb lehet az egyedi rekordok keresése
🔹 Hátrányok:
❌ Csak az indexben balról-jobbra szereplő oszlopokat használja hatékonyan
❌ Túl sok oszlop esetén növeli az index méretét
📌 Példa Oracle-ben Composite index létrehozására:
CREATE INDEX idx_customer_name ON customers (last_name, first_name);


Full-Text Index – Szöveges keresések gyorsítására
🔹 Mikor érdemes használni?
✔ Nagy méretű szöveges adatok gyors keresésére (LIKE '%valami%' helyett)
✔ Dokumentumok, cikkek, termékleírások keresésekor
🔹 Előnyök:
✅ Lényegesen gyorsabb, mint a hagyományos LIKE keresések
✅ Támogatja a részleges és homályos kereséseket
🔹 Hátrányok:
❌ Speciális konfigurációt igényel
❌ Több tárhelyet foglalhat, mint egy hagyományos index
📌 Példa Oracle-ben Full-Text Index létrehozására:
CREATE INDEX idx_product_desc ON products (description) INDEXTYPE IS CTXSYS.CONTEXT;


Összegzés: Melyik indexet mikor használjuk?
Index típusa Ideális felhasználás OLTP vagy OLAP?
B-tree Index Gyors keresés, egyedi rekordok OLTP & OLAP
Bitmap Index Kevés egyedi érték, adattárház OLAP
Unique Index Egyedi oszlopok (PRIMARY KEY, UNIQUE) OLTP & OLAP
Composite Index Több oszlop együttes keresése OLTP & OLAP
Full-Text Index Szöveges adatok keresése 


Tömörített (ZIP) táblák az adatbázisokban
A tömörített táblák az adatbázis teljesítményoptimalizálásának egyik fontos eszközei. Ezek az Oracle és más modern adatbázis-kezelők által kínált lehetőségek, amelyek csökkentik a tárhelyigényt, optimalizálják az I/O műveleteket, és bizonyos esetekben még a lekérdezések teljesítményét is javíthatják.
1. A tömörített táblák jellemzői
🔹 Csökkentett tárolási méret: Az adatok kisebb helyet foglalnak a tárolórendszerben.
🔹 Kevesebb I/O művelet: Mivel az adatok tömörítettek, kevesebb adatblokkot kell beolvasni egy-egy lekérdezés során.
🔹 Jobb teljesítmény OLAP rendszerekben: Különösen adattárházaknál és csak olvasható adatok esetén hatékony.
🔹 Korlátozott DML teljesítmény: A tömörített táblák frissítése és módosítása több CPU-erőforrást igényelhet.
2. Oracle tömörítési módok
1️⃣ BASIC (Row Store Compression - OLTP)
Célja: Tárolóhely csökkentése OLTP rendszerekben.
Hatása: Az ismétlődő értékeket blokkszinten tömöríti.
Ajánlott felhasználás: OLTP rendszerekben, ahol fontos a kompromisszum a helytakarékosság és a DML teljesítmény között.
📌 Példa Oracle-ben:
CREATE TABLE sales (
    id NUMBER,
    product_name VARCHAR2(100),
    amount NUMBER
) COMPRESS FOR OLTP;


2️⃣ OLTP Advanced Compression (Row Store Advanced Compression)
Célja: Hatékonyabb tömörítés, mint a BASIC módszer.
Hatása: Az adatok tömörítése DML műveletek során is megtörténik.
Ajánlott felhasználás: OLTP rendszerek, ha a tárolási méret csökkentése kritikus.
📌 Példa:
CREATE TABLE orders (
    order_id NUMBER,
    customer_name VARCHAR2(100),
    total_amount NUMBER
) COMPRESS FOR ALL OPERATIONS;


3️⃣ Hybrid Columnar Compression (HCC) – Csak Exadata rendszereken
Célja: Maximális tömörítés adattárházakban és csak olvasható adatok esetén.
Hatása: Az adatok oszloporientált módon kerülnek tömörítésre, így nagy mértékben csökkenti a tárolási méretet és gyorsítja az OLAP lekérdezéseket.
Ajánlott felhasználás: Adattárházak, historikus adatok, ritkán módosított táblák.
📌 Példa:
CREATE TABLE customer_data (
    id NUMBER,
    name VARCHAR2(200),
    purchase_history CLOB
) COMPRESS FOR QUERY HIGH;



4️⃣ Indexek tömörítése
Célja: Az indexek méretének csökkentése.
Hatása: Kevésbé változó adatok esetén az indexek kevesebb helyet foglalnak.
📌 Példa:

CREATE INDEX idx_sales ON sales (product_name) COMPRESS 2;



Előnyök és hátrányok
Előnyök Hátrányok
Tárhely megtakarítás – akár 50-90%-os helycsökkentés CPU-terhelés növekedhet a tömörítés/dekompresszió miatt
Gyorsabb OLAP lekérdezések (pl. aggregációk) DML műveletek (INSERT, UPDATE, DELETE) lassulhatnak
Kevesebb I/O művelet – mivel kevesebb adatblokkot kell olvasni Nem minden verzió támogatja (HCC csak Exadata-n érhető el)


 Mikor érdemes tömörített táblát használni?
✅ Ha az adattárolás költsége kritikus → Nagy adatmennyiségek tárolásakor jelentős helymegtakarítás érhető el.
✅ Ha az adatritkán változik → Pl. historikus adatok, naplófájlok, archívumok.
✅ Ha adattárházat üzemeltetünk → HCC használata OLAP rendszerekben gyorsabb lekérdezéseket eredményez.
❌ Nem ajánlott, ha gyakori frissítésre van szükség → OLTP rendszerek esetén az INSERT/UPDATE/DELETE műveletek lassulhatnak.

Index típusa	Ideális felhasználás	OLTP vagy OLAP?
B-tree Index	Gyors keresés, egyedi rekordok	OLTP & OLAP
Bitmap Index	Kevés egyedi érték, adattárház	OLAP
Unique Index	Egyedi oszlopok (`PRIMARY KEY`, `UNIQUE`)	OLTP & OLAP
Composite Index	Több oszlop együttes keresése	OLTP & OLAP
Full-Text Index	Szöveges adatok keresése

Előnyök	Hátrányok
Tárhely megtakarítás – akár 50-90%-os helycsökkentés	CPU-terhelés növekedhet a tömörítés/dekompresszió miatt
Gyorsabb OLAP lekérdezések (pl. aggregációk)	DML műveletek (INSERT, UPDATE, DELETE) lassulhatnak
Kevesebb I/O művelet – mivel kevesebb adatblokkot kell olvasni	Nem minden verzió támogatja (HCC csak Exadata-n érhető el)

IT, BI, DWH, DM, AI