de Martin LE TARNEC.

Llave primaria


Se parte con el ejemplo más común para el recorrido de la llave primaria con un filtro where. Para los ejemplos de este capítulo, se usará la tabla EMPLOYEES con la siguiente definición:

CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   date_of_birth DATE           NOT NULL,
   phone_number  VARCHAR2(1000) NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
)

La base de datos crea automáticamente un índice sobre la llave primaria. Eso quiere decir que existe un índice sobre la columna EMPLOYEE_ID, aunque no se especifique create index en la sentencia.

Sugerencia

Apéndice C, “Example Schema contiene contienen scripts para rellenar la tabla EMPLOYEES con datos. Puedes usarlos para probar los ejemplos en tu propio entorno.

Para comprender el texto; basta con saber que la tabla tiene 1.000 registros.

La siguiente sentencia usa una llave primaria para devolver los apellidos de los empleados.

SELECT first_name, last_name
  FROM employees
 WHERE employee_id = 123

El filtro where no coincide con múltiples registros porque la restricción de la llave primaria asegura la unicidad de los valores dentro de la columna EMPLOYEE_ID. La base de datos no necesita seguir los nodos hojas del índice; le basta con recorrer el árbol del índice. Se puede usar el llamado plan de ejecución para comprobarlo:

DB2

El siguiente plan de ejecución se generó con la vista last_explained disponible en elapéndice.

Explain Plan
-------------------------------------------------------
ID | Operation             |                Rows | Cost
 1 | RETURN                |                     |   13
 2 |  FETCH EMPLOYEES      |    1 of 1 (100.00%) |   13
 3 |   IXSCAN EMPLOYEES_PK | 1 of 1000 (   .10%) |    6

Predicate Information
 3 - START (Q1.EMPLOYEE_ID = +00123.)
      STOP (Q1.EMPLOYEE_ID = +00123.)

La operación IXSCAN es parecida a INDEX [RANGE|UNIQUE] SCAN. Dada la ejecución del comando, no se puede decidir si se está usando un escaneo único o un escaneo por rango. La operación FETCH es similar alTABLE ACCESS BY INDEX ROWID.

MySQL

+----+-----------+-------+---------+---------+------+-------+
| id | table     | type  | key     | key_len | rows | Extra |
+----+-----------+-------+---------+---------+------+-------+
|  1 | employees | const | PRIMARY | 5       |    1 |       |
+----+-----------+-------+---------+---------+------+-------+

El tipo const es el equivalente MySQL del INDEX UNIQUE SCAN de Oracle.

Oracle

---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |    2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    2 |
|*2 |  INDEX UNIQUE SCAN         | EMPLOYEES_PK |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPLOYEE_ID"=123)

PostgreSQL

                QUERY PLAN
-------------------------------------------
 Index Scan using employees_pk on employees 
   (cost=0.00..8.27 rows=1 width=14)
   Index Cond: (employee_id = 123::numeric)

La operación de PostgreSQL Index Scan combina INDEX [UNIQUE/RANGE] SCAN y la operación TABLE ACCESS BY INDEX ROWID de la base de datos Oracle. Desde el plan de ejecución, no es visible si el acceso del índice puede potencialmente devolver uno o más registros.

SQL Server

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:employees_pk,
   |               SEEK:employees.employee_id=@1
   |            ORDERED FORWARD)
   |--RID Lookup(OBJECT:employees,
                   SEEK:Bmk1000=Bmk1000
                 LOOKUP ORDERED FORWARD)

Las operaciones de SQL Server INDEX SEEK y RID Lookup son similares a las operaciones de Oracle INDEX RANGE SCAN y TABLE ACCESS BY ROWID respectivamente. En comparación con la base de datos Oracle, SQL Server muestra explícitamente el JOIN Nested Loops para combinar los datos del índice y de la tabla.

El plan de ejecución Oracle muestra la operación INDEX UNIQUE SCAN, la operación que sólo necesita recorrer el árbol del índice. Hace uso de la escalabilidad logarítmica del índice para encontrar la entrada rápidamente, independientemente del tamaño de la tabla.

Sugerencia

El plan de ejecución (a veces plan de la sentencia) muestra lo que la base de datos toma como etapas para ejecutar la sentencia SQL. El Apéndice A explica cómo obtener y cómo leer el plan de ejecución en otras bases de datos.

Después de tener acceso al índice, la base de datos debe hacer una etapa más para devolver los datos seleccionados (FIRST_NAME, LAST_NAME) desde el almacenamiento de la tabla: la operación TABLE ACCESS BY INDEX ROWID. Esta operación podría ser un cuello de botella como se ha explicado en Índices lentos, Sección I aunque aquí no existe ningún riesgo porque se realiza un INDEX UNIQUE SCAN. Esta operación no puede devolver más de un registro así que no podrá hacer más de un acceso a la tabla. Eso significa que los ingredientes para una sentencia lenta no se dan en la operación INDEX UNIQUE SCAN.

Si te gusta mi manera de explicar, te encantará mi libro.

Llaves primarias sin un índice único

Una llave primaria no necesita necesariamente un índice único, se puede usar un índice no único. En este caso, la base de datos Oracle no puede usar INDEX UNIQUE SCAN y en su lugar, utilizará la operación INDEX RANGE SCAN. Sin embargo, la restricción mantiene la unicidad de las entradas por lo que el recorrido del índice no podrá devolver más de un valor.

Una de las razones para usar un índice no único es cuando se va a trabajar con una llave primaria con restricciones diferidas. Al contrario de las restricciones normales, las diferidas son validadas durante la ejecución de la sentencia. La base de datos pospone la validación de las restricciones diferidas hasta que se haga commit en la transacción. Las restricciones diferidas se requieren cuando se insertan datos en las tablas con dependencias circulares.

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia