PL-SQLDeveloper használat alapok 3

PL-SQLDeveloper használat alapok 3 

Most megmutatom a teljes képet, hogyan lehet automatizáltan indexeket javasolni Oracle adatbázisban, és kiértékelni, mikor nem éri meg indexet létrehozni.

Célunk:

1. PL/SQL script, ami:

   • Felderíti a gyakran használt, de nem indexelt mezőket

   • Megvizsgálja azok kardinalitását

   • Javaslatot ad: érdemes-e indexelni

---

✅ 1. Index javasló PL/SQL script

---sql
SET SERVEROUTPUT ON
DECLARE
    CURSOR c_targets IS
        SELECT 
            col.owner,
            col.table_name,
            col.column_name,
            col.data_type,
            stat.num_distinct,
            stat.density
        FROM 
            dba_tab_columns col
        JOIN 
            dba_tab_col_statistics stat
            ON col.owner = stat.owner
            AND col.table_name = stat.table_name
            AND col.column_name = stat.column_name
        LEFT JOIN 
            dba_ind_columns ind 
            ON col.owner = ind.table_owner 
           AND col.table_name = ind.table_name 
           AND col.column_name = ind.column_name
        WHERE 
            col.owner NOT IN ('SYS', 'SYSTEM')
            AND ind.column_name IS NULL -- nincs index
            AND stat.num_distinct IS NOT NULL
            AND stat.num_distinct > 10 -- kiszűri pl. státusz mezőket
        ORDER BY stat.num_distinct DESC;

BEGIN
    DBMS_OUTPUT.PUT_LINE('--- INDEX JAVASLATOK ---');

    FOR rec IN c_targets LOOP
        IF rec.density < 0.01 THEN  -- Jó szűrő mező, kis sűrűség
            DBMS_OUTPUT.PUT_LINE(
                'JAVASLAT: ' || rec.owner || '.' || rec.table_name || '.' || rec.column_name ||
                ' → CREATE INDEX ix_' || LOWER(rec.table_name) || '_' || LOWER(rec.column_name) ||
                ' ON ' || rec.owner || '.' || rec.table_name || '(' || rec.column_name || ');'
            );
        ELSE
            DBMS_OUTPUT.PUT_LINE(
                'NEM JAVASOLT: ' || rec.owner || '.' || rec.table_name || '.' || rec.column_name ||
                ' → Túl magas density (' || rec.density || ') → gyenge szűrés'
            );
        END IF;
    END LOOP;
END;
/

---

📊 Példa output:

---sql

--- INDEX JAVASLATOK ---
JAVASLAT: HR.UGYFEL.UGYFEL_NEV → CREATE INDEX ix_ugyfel_ugyfel_nev ON HR.UGYFEL(UGYFEL_NEV);
NEM JAVASOLT: HR.UGYFEL.STATUSZ → Túl magas density (0.33) → gyenge szűrés
JAVASLAT: SALES.RENDELES.RENDELES_ID → CREATE INDEX ix_rendeles_rendeles_id ON SALES.RENDELES(RENDELES_ID);

---

🎯 Mikor nem éri meg indexelni?

Feltétel	Miért nem hasznos?
NUM_DISTINCT < 10	Alacsony változatosság → gyenge szűrés
DENSITY > 0.1	Rossz szelektivitás
A mező ritkán szerepel WHERE-ben	Fölösleges index karbantartási költség
Függvény használat a mezőn (pl. UPPER)	Függvény miatt nem tud indexet használni

---

🧠 Tippek tuninghoz:

• Histogramos statisztikák segíthetnek ritkán használt értékek gyorsabb eléréséhez:

  ---sql
  EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'UGYFEL', METHOD_OPT => 'FOR ALL COLUMNS SIZE 254');
  

• Materialized View + Fast Refresh lehetőség ismétlődő, lassú lekérdezésekre.

• Oracle SQL Tuning Advisor is adhat javaslatokat:

  ---sql
  EXEC DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => 'SELECT * FROM ugyfel WHERE ugyfel_nev = ''Teszt''',
    user_name => 'HR',
    scope => 'COMPREHENSIVE',
    time_limit => 60,
    task_name => 'ugyfel_nev_tuning'
  );
  EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'ugyfel_nev_tuning');
  SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('ugyfel_nev_tuning') FROM DUAL;
  

---

✅ 1. Konkrét tábla mezőit vizsgáló indexjavasló script

Ez a script csak egy adott tábla mezőit vizsgálja, pl. UGYFEL táblát, és javasol indexet, ha érdemes.

🔧 Beállítható paraméterek:

• p_owner = séma neve

• p_table = tábla neve

---sql
SET SERVEROUTPUT ON
DECLARE
    p_owner VARCHAR2(30) := 'HR';
    p_table VARCHAR2(30) := 'UGYFEL';
BEGIN
    FOR rec IN (
        SELECT 
            col.column_name,
            stat.num_distinct,
            stat.density,
            stat.num_nulls
        FROM 
            dba_tab_columns col
        JOIN 
            dba_tab_col_statistics stat
            ON col.owner = stat.owner
           AND col.table_name = stat.table_name
           AND col.column_name = stat.column_name
        LEFT JOIN 
            dba_ind_columns ind
            ON col.owner = ind.table_owner
           AND col.table_name = ind.table_name
           AND col.column_name = ind.column_name
        WHERE 
            col.owner = p_owner
            AND col.table_name = p_table
            AND ind.column_name IS NULL
    ) LOOP
        IF rec.num_distinct > 10 AND rec.density < 0.01 THEN
            DBMS_OUTPUT.PUT_LINE(
                '-- JAVASLAT: CREATE INDEX ix_' || LOWER(p_table) || '_' || LOWER(rec.column_name) ||
                ' ON ' || p_owner || '.' || p_table || '(' || rec.column_name || ');'
            );
        ELSE
            DBMS_OUTPUT.PUT_LINE('-- NEM JAVASOLT: ' || rec.column_name || 
                ' (kardinalitás: ' || rec.num_distinct || ', density: ' || rec.density || ')');
        END IF;
    END LOOP;
END;
/

---

✅ 2. Automatikus CREATE INDEX generálás fájlba (spool)

Ez a megoldás egy SQL fájlba írja azokat az indexeket, amiket érdemes lenne létrehozni. SQL*Plus vagy SQL Developer Script Runner futtatásához ideális.

🔄 Lépések:

1. Hozd létre az alábbi scriptet (pl. index_generator.sql)

2. Futtasd SQL*Plusból vagy SQL Developer script módban

---sql
SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 500
SET PAGESIZE 0
SPOOL index_javaslatok.sql

SELECT 
    'CREATE INDEX ix_' || LOWER(table_name) || '_' || LOWER(column_name) ||
    ' ON ' || owner || '.' || table_name || '(' || column_name || ');'
FROM 
    dba_tab_col_statistics
WHERE 
    num_distinct > 10
    AND density < 0.01
    AND (owner, table_name, column_name) NOT IN (
        SELECT table_owner, table_name, column_name FROM dba_ind_columns
    )
    AND owner NOT IN ('SYS', 'SYSTEM');

SPOOL OFF

💡 Ez létrehoz egy index_javaslatok.sql fájlt, amit bármikor futtathatsz.

---

✅ 3. Oracle SQL Developerhez készült riportformátum

Ez a változat egy „kézi” riport a SQL Developerben való futtatáshoz. A kimenetet Excelbe, CSV-be exportálhatod. Részletes és áttekinthető.

---sql
SELECT 
    col.owner,
    col.table_name,
    col.column_name,
    col.data_type,
    stat.num_distinct,
    stat.density,
    stat.num_nulls,
    CASE
        WHEN ind.column_name IS NOT NULL THEN '✓ Indexelt'
        WHEN stat.num_distinct > 10 AND stat.density < 0.01 THEN '🔍 Index javasolt'
        ELSE '✘ Nem javasolt'
    END AS index_javaslat
FROM 
    dba_tab_columns col
JOIN 
    dba_tab_col_statistics stat
    ON col.owner = stat.owner
   AND col.table_name = stat.table_name
   AND col.column_name = stat.column_name
LEFT JOIN 
    dba_ind_columns ind
    ON col.owner = ind.table_owner
   AND col.table_name = ind.table_name
   AND col.column_name = ind.column_name
WHERE 
    col.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 
    index_javaslat DESC, stat.num_distinct DESC;

🔍 Kimenet tartalmaz:

• Séma, tábla, mező neve

• Típus, kardinalitás, sűrűség, null értékek

• Index státusz javaslat

💡 Futtasd SQL Developerben → jobb klikk → Exportálás → Excel/CSV

---

🚀 Összegzés

Verzió	Cél	Használat
1. PL/SQL script (konkrét tábla)	Részletes elemzés egy táblára	SQL Developer belső használat
2. Spool fájl (indexek generálása)	CREATE INDEX fájl előállítás	SQL*Plus/script
3. Riport SQL Developerhez	Vizualizált riport, exportálható	Kézi értékeléshez ideális

---