SQL tips&tricks

Deși două declarații SQL pot produce același rezultat, Oracle poate procesa mai repede o interogare decât o alta. Pentru a verifica puteți utiliza rezultatul planului de execuție pentru a compara costurile celor două declarații și de a determina care este mai eficient. Mai jos sunt câteva sfaturi care vă pot ajuta în redactarea interogărilor eficiente.

1. întotdeauna folosiți alias-uri și prefixe

Dacă mai mult de o tabelă este implicată într-o clauză FROM, este o bună practică ca fiecare nume de coloană să fie calificată folosind un pseudonim al tabelei pentru a vedea cu ușurință care coloană aparține cărei tabele. Alias-urile sunt mai scurte decât denumirile complete ale tabelei, astfel interogarea este mai ușor de citit, de înțeles iar timpul de parsare este redus.

2. Utilizați IN în loc de EXISTS.

Un truc simplu pentru a crește viteza de interogare este clauza EXISTS care să înlocuiască clauza IN. Metoda IN este mai rapidă decât EXISTS pentru că nu verifică rânduri inutile în comparație.

inițial:

SELECT *
FROM employees e
WHERE  Exists
  (SELECT d.department_id
  FROM departments d
  WHERE  e.department_id = d.department_id
  and d.location_id     =1400
  )

mai eficient:

SELECT *
FROM employees e
WHERE e.department_id IN
  (SELECT d.department_id
  FROM departments d
  WHERE d.location_id     =1400
  )

3. Utilizați JOIN în loc de EXISTS.

SELECT *
FROM employees e
WHERE  Exists
  (SELECT d.department_id
  FROM departments d
  WHERE  e.department_id = d.department_id
  and d.location_id     =1400
  )

Pentru a îmbunătăți performanta puteți utiliza:

SELECT *
       FROM   employees e, departments d
       WHERE  e.department_id = d.department_id
       AND    d.location_id     =1400;

4. Utilizați EXISTS în loc de DISTINCT.

inițial:

SELECT DISTINCT d.department_name , d.manager_id
       FROM   employees e, departments d
       WHERE  e.department_id = d.department_id;

Pentru a îmbunătăți performanta puteți utiliza:

SELECT  d.department_name , d.manager_id
       FROM     departments d
       WHERE EXISTS (SELECT 'X'
                        FROM    employees e
                        WHERE   e.department_id = d.department_id);

5. condiții simplificate

Niciodată nu folosiți o funcție (ori un calcul) pe o coloană indexată, doar dacă sunteți sigur că există Index Function Based (apărut din Oracle 8i). Dacă se folosește o funcție sau un calcul, pe o coloană indexată, optimizer-ul nu va folosi index-ul. Încercați să folosiți alternative. De asemenea concatenarea stringurilor || va duce la nefolosirea indecșilor.

în loc de:

SELECT * FROM employees WHERE SUBSTR (first_name, 1,3) = 'Dav';

folosiți:

SELECT * FROM   employees WHERE first_name LIKE 'Dav%';

alt exemplu:

SELECT *
         FROM   employees
         WHERE salary*12 > 24000;

Chiar dacă vom crea un index pentru „salary”, indexul nu va fi folosit,
asta în cazul în care nu folosim un index calculat. In acest caz mai
eficient putem rescrie:

SELECT *
          FROM employees
          WHERE salary > 24000/12;

6. Niciodată nu utilizați NOT pentru o coloană indexată.

în loc de:

SELECT * FROM employees WHERE NOT salary = 0;

Notă: Index-ul ne poate spune ce se află într-o tabelă nu și ce nu este într-o tabelă.

vă sugerez:

SELECT * FROM employees WHERE salary > 0;

7. Utilizați aceeași convenție pentru toate interogările.
Oracle va adaugă codul SQL sau PL/SQL în memorie și îl va refolosi dacă același cod va fi executat din nou. Totuși trebuie să reținem că:

 SELECT * FROM EMPLOYEES

este diferit de:

 SELECT * FROM employees

Chiar dacă exista spații diferite în declarație, căutarea va eșua. Presupunând că declarația nu are un plan de execuție în cache, trebuie să fie „parsată” (en. parsed) înainte de execuție.

SELECT * FROM v$sql WHERE upper(sql_text) like 'SELECT * FROM EMPLOYEES'

SQL_FULLTEXT                                                          SQL_ID     
-------------------------------------------------------------------------------- -------------
SELECT * FROM EMPLOYEES                                   av3b8yvd58zjp
SELECT * FROM employees                                        4959aapufrm1k

9. Nu folosiți clauza HAVING în locul clauzei WHERE

Filtrele cu clauza HAVING selectează înregistrările numai după ce au fost preluate. Clauza WHERE ajută la reducerea costurilor generale în sortarea, grupare, etc. Clauza HAVING ar trebui să fie utilizată numai atunci când coloanele cu operațiuni de rezumat aplicate sunt restricționate de clauza.

exemplu:

SELECT d.department_name, AVG (e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name
HAVING department_name != 'RESEAECH'
AND department_name != 'SALES';

vă sugerez:

SELECT d.department_name, AVG (e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND department_name != 'RESEAECH'
AND department_name != 'SALES'
GROUP BY d.department_name;

10. Evitați multiplele subinterogări unde este posibil

inițial:

Update jobs set min_salary = (select min (salary) from employees),
                    max_salary = (select max(salary) from employees);  

mai eficient:

UPDATE jobs set (min_salary, max_salary) =  (SELECT min (salary), max (salary) FROM employees)

11. Utilizați IN în loc de OR

inițial:

SELECT *
    FROM departments
    WHERE location_id = 1500 or location_id=1800 or location_id = 2400

mai eficient:

SELECT *
    FROM departments
    WHERE location_id in (1500,1800,2400)

12. Utilizați BETWEEN în loc de IN.
BETWEEN este foarte util pentru filtrarea valorilor într-un interval specific.
Acesta este mult mai rapid decât tastarea fiecărei valori din intervalul IN.

inițial:

SELECT *
    FROM departments
    WHERE location_id IN (1500,1600,1700)

mai eficient:

SELECT *
    FROM departments
    WHERE location_id BETWEEN 1500 and 1700

13. Utilizați UNION ALL în loc de UNION

Problema este că în UNION ALL, Oracle găsește toate rândurile de calificate și apoi le deduplica.