Oracle adatbázis lekérdezés készítés elvei

Oracle adatbázis lekérdezés készítés elvei

🔍 Hogyan deríthetem ki egy tábla indexeit?

A következő lekérdezések segítenek feltérképezni egy tábla indexeit:

1. Indexek lekérdezése:

--- sql
SELECT index_name, uniqueness, status
FROM user_indexes
WHERE table_name = 'TÁBLA_NÉV';

2. Index oszlopainak lekérdezése:

--sql
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'TÁBLA_NÉV'
ORDER BY index_name, column_position;

---

🎯 Mely mezőket érdemes keresésre használni?

1. Indexszel ellátott mezők – ezek a legjobb jelöltek a WHERE feltételekhez.

2. Szelektivitás – ha egy oszlop sok különböző értéket tartalmaz (pl. személyi szám), jó jelölt indexhez. Ha kevés értéket (pl. "ország = 'HU'"), akkor kevésbé hatékony.

3. Használati gyakoriság – ha egy oszlop gyakran szerepel szűrésre vagy JOIN feltételekben, akkor érdemes indexelni.

---

⚙️ Tippek gyors lekérdezéshez (optimalizálás):

1. Használj EXPLAIN PLAN vagy AUTOTRACE: Ezekkel megnézheted, hogyan hajtódik végre a lekérdezés.

   ---sql
   EXPLAIN PLAN FOR
   SELECT * FROM employees WHERE department_id = 10;
   
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
   

2. Kerüld a függvényeket az indexelt oszlopokon: Ez megakadályozza az index használatát.

   • 🛑: WHERE UPPER(name) = 'JOHN'

   • ✅: WHERE name = 'John' (ha van index a name mezőn)

3. Használj bővített statisztikákat (gather stats): Oracle optimalizáló jobban dönt, ha friss statisztikák vannak.

   ---sql
   EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TÁBLA_NÉV');

4. Index típusok:

   • B-Tree index: általános esetekre jó (egyenlőség, tartománykeresés).

   • Bitmap index: kevés különböző érték esetén jó (pl. „nem”, „ország”).

   • Composite index: több mezőt tartalmaz, a sorrend fontos!

---

🧠 Jó gyakorlatok

• Törekedj minél szelektívebb szűrésre.

• JOIN-okat mindig indexelt mezőkön végezd.

• Használj aliasokat és legyen jól olvasható a lekérdezés.

• Ne kérj le felesleges oszlopokat (SELECT * helyett név szerint).

---

🗃️ Példatáblák:

1. CUSTOMERS (1 millió sor)

   ---sql
   customer_id (PK, indexed), name, country_code, created_date

2. ORDERS (10 millió sor)

   ---sql
   order_id (PK), customer_id (FK -> CUSTOMERS), order_date, status, total_amount

3. PRODUCTS (100 ezer sor)

   ---sql
   product_id (PK), name, category, price

4. ORDER_ITEMS (100 millió sor)

   ---sql
   
   order_item_id (PK), order_id (FK -> ORDERS), product_id (FK -> PRODUCTS), quantity, unit_price
   

---

🔍 Példa 1: Egyszerű lekérdezés ügyfelekre szűréssel

❌ Rossz megoldás:

---sql

SELECT *
FROM customers
WHERE UPPER(name) = 'JOHN SMITH';

• Miért rossz?

  • UPPER(name) miatt nem használható az index a name oszlopon.

  • SELECT * feleslegesen lekéri az összes oszlopot.

✅ Jó megoldás:

---sql
SELECT customer_id, name
FROM customers
WHERE name = 'John Smith';

• Miért jobb?

  • Index használható.

  • Csak szükséges oszlopokat kér le → kevesebb I/O.

---

🔍 Példa 2: Részletes jelentés vevői rendelésekből

❌ Rossz megoldás (JOIN + funkcióval + nincs index):

---sql
SELECT c.name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE TO_CHAR(o.order_date, 'YYYY-MM') = '2024-04';

• Miért lassú?

  • TO_CHAR megakadályozza az index használatát order_date mezőn.

  • Nagy táblák között történik a szűrés után.

✅ Jó megoldás:

---sql
SELECT c.name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN DATE '2024-04-01' AND DATE '2024-04-30';

• Előny:

  • Indexelhető a order_date.

  • Helyes szűrés dátumtartományon → gyorsabb.

---

🔍 Példa 3: Aggregált riport rendelés részletekkel

❌ Rossz megoldás (nem szelektív JOIN):

---sql
SELECT c.country_code, SUM(oi.quantity * oi.unit_price) as revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.country_code;

• Mi a gond?

  • Feleslegesen sok adatot kapcsol össze.

  • Ha country_code nincs indexelve, a GROUP BY lassú lehet.

✅ Jó megoldás (előszűrés és statisztikák):

sql
-- Index legyen a customers.country_code és orders.order_date mezőkön!
SELECT c.country_code, SUM(oi.quantity * oi.unit_price) as revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= SYSDATE - 365
GROUP BY c.country_code;

• Előnyök:

  • Év szintű előszűrés szűkíti az adathalmazt.

  • Indexek gyorsítják a kapcsolódást és szűrést.

---

⚡ Optimalizálás eszközökkel:

1. Explain Plan

---sql
EXPLAIN PLAN FOR
SELECT ... ;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Megmutatja, melyik művelet mennyibe kerül.

2. AUTOTRACE (SQL*Plus):

---sql
SET AUTOTRACE ON;
SELECT ... FROM ...;

3. Statisztikák frissítése:

---sql
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'ORDERS');

---

🧠 Összefoglaló jó gyakorlatok nagy táblákon:

Rossz gyakorlat	Jó gyakorlat
SELECT *	Csak szükséges oszlopokat kérj le
Függvény indexelt oszlopon	Kerüld a TO_CHAR, UPPER, stb.
Nincs szűrés nagy JOIN előtt	Szűrj minél korábban
Nincs index	Indexelj szűrésre és JOIN mezőkre
Elavult statisztikák	Használj DBMS_STATS.GATHER_* rutinokat
Nem használsz EXPLAIN PLAN-t	Mindig ellenőrizd a végrehajtási tervet