Oracle Hints
- modo de optimización
- Modo de acceso

Oracle Hints

Los hints son consejos que se le dan al optimizador SQL de Oracle para que elabore el plan de ejecución de una sentencia DML según nosotros le indicamos.

Gracias a estos hints se puede, por ejemplo, indicar al optimizador SQL de orale que trabaje de un modo u otro.

Los hints se incorporan en forma de comentario y deben ir justo detras del comando principal.

ex

SELECT /*+ HINT */ ...

modo de optimización

ALL_ROWS Fuerza a que se utilice el optimizador basado en costes y optimiza el plan de ejecución de la sentencia DML para que devuelva todas las filas en el menor tiempo posible. Es la opción por defecto del optimizador basado en costes y es la solución apropiada para procesos en masa e informes, en los que son necesarias todas las filas para empezar a trabajar con ellas.

SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

FIRST_ROWS(n) También fuerza a que se utilice el optimizador basado en costes y optimiza el plan de ejecución de la sentencia DML para que devuelva las “n” primeras filas en el menor tiempo posible. Esto es idóneo para procesos iterativos y bucles, en los que podemos ir trabajando con las primeras filas mientras se recuperan el resto de resultados. Obviamente este hint no será considerado por el optimizador si se utilizan funciones de grupo como MAX, SUM, AVG, etc.

SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;

CHOOSE Fuerza a que se utilice el optimizador basado en costes. Este optimizador construye los planes de ejecución basándose en las estadísticas almacenadas en el diccionario de datos. Tiene en consideración el número de lectura lógicas (el factor más importante), la utilización de la CPU junto con los accesos a disco y a memoria, y el uso de la red (cuando los datos residen en diferentes servidores). Una de las ventajas de utilizar el optimizador basado en costes es que Oracle lo está mejorándolo continuamente.

SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

RULE Fuerza a que se utilice el optimizador basado en normas. En desuso.

SELECT /*+ RULE */
employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

Modo de acceso

Hints para determinar el modo de acceso:

FULL Especifica elegir la lectura completa de la tabla para aceder a los datos sin pasar por indices. Aconsejable cuando se van a recuperar la mitad + n de registros de la tabla

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM employees e 
  WHERE last_name LIKE :b1;

ROWID Especifica elegir la lectura de la tabla por el rowid.

SELECT /*+ROWID(employees)*/ *

  FROM employees 
  WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;

CLUSTER Especifica escoger una búsqueda por cluster para acceder a la tabla. Se aplica a objetos clusterizados.

SELECT /*+ CLUSTER */
  employees.last_name, department_id
  FROM employees, departments
  WHERE department_id = 10 
  AND employees.department_id = departments.department_id;

HASH Especifica hacer una búsqueda a traves de un hash para acceder a la tabla especificada. Se aplica sólo a tablas almacenadas en un cluster.

INDEX Especifica escoger un indice para buscar en la tabla. Puedes especificar el indice para dominios, B-tree, bitmaps y bitmaps con indices. Aunque Oracle recomienda usar INDEX_COMBINE mejor que INDEX para indices de bitmants porque es mas versatil

Vamos a considerar una consulta que recoja el nombre, altura y peso de los pacientes varones de un hospital:

SELECT name, height, weight
 FROM patients
 WHERE sex = 'm';

Imaginemos que hay un indice en la columna sex que contiene los valores de m y f. Si hay igual numero de hombre(m) que de mujeres(f) en el hospital entonces la consulta devuelve un porcentaje bastante grande de lineas y un full scan de la tabla es lo más rápido. De hecho un full scan es lo más rápido si la colsulta devuelve a partir de la mitad + 1 de las lineas de la tabla. En cambio. Si hay pocos hombres en la tabla, la búsqueda a traves de un índice es más rápida que no recorrerse toda la tabla.

Como que en una situación normal en un campo donde hay sólo dos valores posibles el optimizador de Oracle buscará por toda la tabla por defecto en vez de usar el indice para ello. Por lo que, si sabes que el numero de resultados es reducido es buena idea forzar a que use en índice para recuperarlos

SELECT /*+ INDEX(patients sex_index) usa el índice porque YO se que hay pocos hombres */ name, height, weight
FROM patients
WHERE sex = 'm';

INDEX_ASC Especifica usar un indice para la tabla especificada y ademas hacerlo en orden ascendente de los valores indexados

INDEX_JOIN Indica al optimizador usar un index join para acceder a los datos.

The INDEX_JOIN hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.

index_join_hint::= Text description of index_join_hint.gif follows Text description of the illustration index_join_hint.gif

where:

table specifies the name or alias of the table associated with the index to be scanned.
index specifies an index on which an index scan is to be performed.

For example, the following query uses an index join to access the employee_id and department_id columns, both of which are indexed in the employees table.

SELECT /*+index_join(employees emp_emp_id_pk emp_department_ix)*/

  employee_id, department_id 
  FROM employees 
  WHERE department_id > 50;

INDEX_DESC

The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, then Oracle scans the index entries in descending order of their indexed values. In a partitioned index, the results are in descending order within each partition.

index_desc_hint::= Text description of index_desc_hint.gif follows Text description of the illustration index_desc_hint.gif

Each parameter serves the same purpose as in the INDEX hint. For example:

SELECT /*+ INDEX_DESC(a ord_order_date_ix) */

     a.order_date, a.promotion_id, a.order_id
FROM orders a

WHERE a.order_date = :b1;

INDEX_FFS

The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.

index_ffs_hint::= Text description of index_ffs_hint.gif follows Text description of the illustration index_ffs_hint.gif

For example:

SELECT /*+ INDEX_FFS ( o order_pk ) */ COUNT(*) FROM order_items l, orders o WHERE l.order_id > 50

   AND l.order_id = o.order_id;