Folosirea hinturilor la generarea unui plan de execuţie

Hinturile sunt sugestii date de programator serverului pentru a forţa un anumit plan de execuţie. Ele pot fi folosite pentru a face o optimizare în situaţii extreme (pentru situaţii în care serverul nu alege cel mai bun plan de execuţie), sau dacă tipul de optimizare curent nu este optim pentru unele instrucţiuni.
Se recomandă ca hinturile să fie folosite cu atenţie şi doar când este absolut necesar. Dacă o instrucţiune SQL nu este performantă, atunci prima etapă constă în analiza schemei bazei de date, deoarece în majoritatea cazurilor performanţă scăzută nu este de la server. In continuare se poate analiza planul de execuţie şi de aici se poate deduce dacă este necesară crearea de indexuri, sau rescrierea instrucţiunii sql, sau folosirea unor hinturi.

Hinturile sunt de două tipuri:

Directive precizate în fişierele de configurare ale bazei de date (de ex. init.ora)
Directive precizate local pentru unele instrucţiuni SQL

In instrucţiunea sql un hint se dă ca un comentariu, după cuvântul cheie care precizează instrucţiunea. Dacă se specifică incorect un hint, atunci el este tratat ca un comentariu şi este ignorant, fără mesaje de avertisment. Cea mai bună cale de a vedea dacă un hint este utilizat este să se afişeze planul de execuţie.

Exemplu de precizare a unui hint:

SELECT /*+ FULL(tabel) */ expresie1,... FROM tabel WHERE conditie;

La o instrucţiune sql se pot folosi mai multe hinturi în paralel, separatorul fiind spaţiul. Specificarea unor hinturi care sunt în conflict va genera nefolosirea lor.

Dacă în instrucţiunea SQL tabelele au asociate aliasuri, atunci în precizarea hinturilor se vor folosi aceste aliasuri şi nu numele tabelelor.
In continuare se vor preciza câteva hinturi utile. O listă a hinturilor se poate consulta documentul Using Optimizer Hints sau Cost Control: Inside the Oracle Optimizer.

ALL_ROWS: este identic cu execuţia (de administrator) a instrucţiunii:
```
ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS
```
FIRST_ROWS(n), unde n este un întreg pozitiv: se foloseşte la obţinerea planului cât mai rapid (chiar dacă nu e optim). Este util când se doreşte obţinerea rapidă a primelor n înregistrări din interogare. El este ignorant în instrucţiuni UPDATE, DELETE, sau în instrucţiuni SQL cu GROUP BY, DISTINCT, INTERSECT, MINUS, UNION (aceste clauze au nevoie de toate înregistrările înainte de a fi executate).
CHOOSE: are acelaşi efect cu precizarea parametrului OPTIMIZER_MODE la CHOOSE. El determină folosirea optimizatorului bazat pe costuri dacă cel puţin un tabel referit de instrucţiunea SQL este analizat, iar dacă nici un tabel nu este analizat se va folosi optimizatorul bazat pe reguli.
RULE: solicită optimizatorul bazat pe reguli. Are acelaşi efect cu precizarea parametrului OPTIMIZER_MODE la RULE.
FULL(tabel): cere o scanare (parcurgere) a tabelului. Este util când costul folosirii indexului împreună cu a tabelului este mai mare decât numai a tabelului.
ROWID(tabel): cere accesul la un tabel prin folosirea unei referinţe la o înregistrare (poate fi furnizată de un index).
INDEX (tabel index1, index2, ...): cere folosirea unuia sau a mai multor indecşi pentru tabelul specificat.
NO_INDEX (tabel index1,index2, ... ): cere să nu se folosească unul sau a mai mulţi indecşi pentru tabelul specificat
INDEX_FFS(tabel index): cere să se utilizeze numai indexul, fără tabel. Este folosit doar dacă informaţia căutată se află în index.
ORDERED: este folosit dacă se doreşte ca operaţia de join dintre două tabele să fie efectuată cu tabelele în ordinea în care apar în clauza FROM.

Exemple:

Instrucţiunea:

select /*+ CHOOSE */ * from studenti where substr(cnp,2,2)='73';

are planul:

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   118 | 10974 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| STUDENTI  |   118 | 10974 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | STUDENTI1 |   118 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SUBSTR("CNP",2,2)='73')

Instrucţiunea:

select /*+ RULE */ * from studenti where substr(cnp,2,2)='73';

are planul:

--------------------------------------
| Id  | Operation         | Name     |
--------------------------------------
|   0 | SELECT STATEMENT  |          |
|*  1 |  TABLE ACCESS FULL| STUDENTI |
--------------------------------------

Instrucţiunea:

select /*+ INDEX(STUDENTI STUDENTI2) */ * from studenti where substr(cnp,2,2)='73' and substr(cnp,6,2)='13';

are planul:

----------------------------------------------------------------------------------------------
| Id  | Operation                        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |           |     4 |   372 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID     | STUDENTI  |     4 |   372 |     3   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |           |       |       |            |          |
|   3 |    BITMAP AND                    |           |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE    | STUDENTI2 |       |       |            |          |
|   5 |     BITMAP CONVERSION FROM ROWIDS|           |       |       |            |          |
|*  6 |      INDEX RANGE SCAN            | STUDENTI1 |   118 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(SUBSTR("CNP",6,2)='13')
   6 - access(SUBSTR("CNP",2,2)='73')

Instrucţiunea:

select /*+ FULL(DISCIPLINE) */ * from discipline where cod<'MB0001';

are planul:

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    52 |  5928 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DISCIPLINE |    52 |  5928 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Instrucţiunea:

select /*+ RULE */ * from discipline where cod<'MB0001';

are planul:

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| DISCIPLINE   |
|*  2 |   INDEX RANGE SCAN          | SYS_C0010290 |
----------------------------------------------------