Forzare l'uso di un INDICE in una query - HINT

Il seguente comando forza l'Optimizer ha utilizzare l'indice PK_SERVIZIO  della tabella servizi.

L'HINT quindi forza l'optimizer ad usare un altro piano di esecuzione.

Non è detto che se è presente un HINT l'Optimizer lo usa perchè valuta se Hint ha senso.

SELECT
/* + INDEX (ser PK_SERVIZIO) */
ut.id_utente,
ser.id_servizio
................................
FROM
servizi ser,
utenti ut,
...................

Questo è un esempio di come l'uso di PARALLEL migliora le performance di una query, e abbassa i costi

/* Another example with multiple joins, groups etc. But with no hint*/

SELECT customers.cust_first_name, customers.cust_last_name, 

  MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)

FROM sales, customers

WHERE sales.cust_id=customers.cust_id

GROUP BY customers.cust_first_name, customers.cust_last_name;

 

/* Performance increase when performing parallel execution hint*/

SELECT /*+ PARALLEL(4) */ customers.cust_first_name, customers.cust_last_name, 

  MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)

FROM sales, customers

WHERE sales.cust_id=customers.cust_id

GROUP BY customers.cust_first_name, customers.cust_last_name;

If a table has an alias, we cannot use the table name directly. We need to use the alias instead of table names. Otherwise it will not work.

SELECT /*+ FULL(employees) */ employee_id, last_name

 FROM employees e

 WHERE last_name LIKE 'A%';

The optimizer does not perform the hint because We need to write the table alias first, to make it correct. 

The hint must be like this : /*+ INDEX(e EMP_EMP_ID_PK) */

SELECT /*+ INDEX(EMP_EMP_ID_PK) */ employee_id, last_name, department_id

  FROM employees e

  where e.employee_id > 120

  and last_name like 'A%';

Why this hint usage is not efficient?

SELECT /*+ INDEX(e EMP_EMP_ID_PK) */ employee_id, last_name, department_id

  FROM employees e

  where e.employee_id > 120

  and last_name like 'A%';

This index is not very selective for the employee_id > 120 predicate. 

Instead, last_name like 'A%' is more selective because there are few people starting with A. 

So, if we wouldn't force the optimizer to use EMP_ID_PK index, it would use the EMP_NAME_IX index with index range scan, 

so that it would perform much better.  

What the hint used in the following query will force the optimizer?

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id

  FROM employees

  WHERE department_id = 20;

Estare  il miglior piano di esecuzione sulle prime 10 righe.