Imprimir
Dentro de este apartado se muestran una serie de ejemplos para explicar la forma de elegir el índice más apropiado. En función de las condiciones que se empleen, el índice a utilizar será diferente n cada caso. Téngase en cuenta, además, que si una instrucción SQL no incluye la cláusula «WHERE», el acceso a la tabla se realizará de forma secuencial.
La estructura de la tabla que vamos a utilizar en los siguientes ejemplos es:
create table provincias (provincia
smallint not null,
descripcion char(20) upshift,
prefijo smallint)
primary key (provincia);
Ejemplo 1:
select provincia, descripcion from provincias
En este caso, el acceso a la tabla «provincias» es secuencial, ya que al tener que leer toda la tabla resultará el procedimiento más rápido.
Ejemplo 2:
select provincia, descripcion from
provincias
where provincia > 5
En este caso, la columna «provincia» constituye la clave primaria de la tabla «provincias». Por lo tanto, al indicar una condición sobre dicha columna, el índice seleccionado para este acceso será automáticamente la clave primaria.
Ejemplo 3:
select provincia, descripcion from
provincias
where provincia > 5 or 1 = 0
Siguiendo con el ejemplo anterior, al añadir una condición OR cuya evaluación es falsa, el optimizador anula el acceso por dicha clave primaria.
Ejemplo 4:
select provincia, descripcion from
provincias
where descripcion matches "A*"
En este caso la condición MATCHES «A*» equivaldría a escribir «>=A».
La columna «descripcion» no es índice, por lo que el acceso a la tabla «provincias» se realizará igualmente de forma secuencial.
No obstante, si la columna «descripcion» fuese un índice simple utilizaría éste para el acceso. Asimismo, en el caso de que dicha columna fuese la primera de un índice compuesto, éste sería considerado igualmente para el proceso de optimización. Si existiesen ambos índices (simple y compuesto) se elegiría el simple, ya que su columna («descripcion» en este caso) compone la totalidad del índice, lo que no ocurriría con el otro índice.
En el caso de que la columna «descripcion» formase parte de un índice compuesto, pero no fuese la primera componente del mismo, éste sólo podría ser utilizado en el caso de que en la sentencia existiesen también condiciones sobre las componentes anteriores del índice.
Ejemplo 5:
select provincia, descripcion from
provincias
where descripcion matches "*A*"
En este ejemplo, tanto si la columna «descripcion» es o no índice, el acceso siempre será secuencial. Ello es debido a que la condición no es optimizable.
Para los ejemplos que siguen a continuación emplearemos la siguiente estructura de tabla:
create table pruebas (codigo smallint
not null,
descripcion char(20) not null,
direccion char(30),
provincia smallint)
primary key (codigo, descripcion);
create index i1_pruebas on pruebas (descripcion)
Ejemplo 6:
select codigo, descripcion from
pruebas
where codigo = 1
and descripcion > "valor"
En este ejemplo, el índice que se utilizará será la clave primaria, compuesta por las columnas «codigo» y «descripcion». La razón de elegir la clave primaria radica en que se hace referencia a ella e primer lugar mediante la condición «codigo=1».
Lo mismo sucedería si se cambiase el orden de las condiciones, es decir:
select codigo, descripcion from
pruebas
where descripcion > "valor"
and codigo = 1
La razón en este caso es que, aunque ambos índices son referenciados al mismo tiempo por medio de la condición: «descripción > ‘valor’» se elige la clave primaria (ver sección 4.1, párrafo 6).
Ejemplo 7:
Para forzar el uso del índice «i1_pruebas» en lugar de la clave primaria en el ejemplo anterior, es necesario obligar a que el optimizador obvie la condición por «codigo»:
select codigo, descripcion from
pruebas
where (codigo = 1 or 1 = 0)
and descripcion > "valor"
Ejemplo 8:
select codigo, descripcion from
pruebas
where codigo > 1
and descripcion = "valor"
En este ejemplo, la condición que emplea el operador igual («=») es la más restrictiva (hace referencia a todos los componentes del índice «i1_pruebas» por igual (ver sección 4.1, párrafo 4).
Los siguientes ejemplos toman como base este esquema de la tabla «pruebas1»:
create table pruebas1 (codigo smallint
not null,
empresa char(30) not null,
nombre char(20),
apellido1 char(20),
apellido2 char(20),
direccion char(30),
provincia smallint,
telefono char(7))
primary key (codigo, empresa);
create index i1_pruebas1 on pruebas1 (empresa);
create index i2_pruebas1 on pruebas1 (nombre, apellido1, apellido2);
Ejemplo 9:
select * from pruebas1
where empresa > ""
and codigo > 10
and nombre matches "A*"
En este ejemplo están referenciadas las primeras columnas de los tres índices por condiciones de «>» o «>=», por lo que los tres índices son igual de válidos. La primera condición («empresa > ""») referencia a dos índices (la clave primaria e «i1_pruebas1»). Se seleccionará la clave primaria (ver sección 4.1, párrafo 6).
Ejemplo 10:
select * from pruebas1
where empresa = "TransTOOLs"
and codigo > 10
and nombre = "Alfonso"
En este ejemplo los tres índices son válidos, ya que en cada uno de ellos se referencia su primera columna. El índice elegido será «i1_pruebas1» ya que todas las columnas que lo componen están condicionadas por «=».
Si se quiere forzar al optimizador para utilizar otro índice (la clave primaria) bastará con anular la posibilidad de optimizar por «i1_pruebas1»:
select * from pruebas1
where (empresa = "TransTOOLs" or 1 = 0)
and codigo > 10
and nombre = "Alfonso"
Si quisiéramos forzar al optimizador por el índice «i2_pruebas1»:
select * from pruebas1
where ((empresa = "TransTOOLs"
and codigo > 10) or 1 = 0)
and nombre = "Alfonso"
Ejemplo 11:
select * from pruebas1
where codigo > 1
and empresa > "TransTOOLs"
and nombre = "Manuel"
and apellido1 = "Ruiz"
and apellido2 = "Lopez"
En este caso, al estar referenciadas por el operador igual «=» las tres columnas del índice «i2_pruebas1», éste será el que se utilice.
Ejemplo 12:
select * from pruebas1
where nombre > "Manuel"
and apellido1 = "Ruiz"
and codigo > 1
and empresa >= "TransTOOLs"
En este ejemplo no existe ningún índice que tenga condicionadas todas sus columnas por el operador igual. El índice elegido es «i2_pruebas1», ya que es el primero referenciado.
Ejemplo 13:
update statistics;
select * from clientes, provincias, albaranes, lineas,articulos,
proveedores, provincias provpr
where clientes.provincia = provincias.provincia
and clientes.cliente = albaranes.cliente
and albaranes.albaran = lineas.albaran
and lineas.articulo = articulos.articulo
and articulos.proveedor = proveedores.proveedor
and proveedores.provincia = provpr.provincia
A continuación se comenta por bloques la información escrita por el optimizador en el fichero indicado en la variable de entorno «OUTOPT3» (ejemplo: «OUTOPT3=fichero»):
Sentence :
database ?
;
Sentence :
update statistics
;
Sentence :
select * from clientes, provincias, albaranes, lineas, articulos,
proveedores, provincias provpr
where clientes.provincia = provincias.provincia
and clientes.cliente = albaranes.cliente
and albaranes.albaran = lineas.albaran
and lineas.articulo = articulos.articulo
and articulos.proveedor = proveedores.proveedor
and proveedores.provincia = provpr.provincia
;
Table Order:
Index clientes.primary = (1) Cols: 1 Used 0 EQ 0
Index clientes.i2_cliente = (2) Cols: 1 Used 0 EQ 0
-Index Selected : None -
Index provincias.primary = (1) Cols: 1 Used 0 EQ 0
-Index Selected : None -
Index albaranes.i2_albaran = (2) Cols: 1 Used 0 EQ 0
Index albaranes.primary = (1) Cols: 1 Used 0 EQ 0
-Index Selected : None -
Index lineas.primary = (1, 2) Cols: 2 Used 0 EQ 0
Index lineas.alblin_ind = (1) Cols: 1 Used 0 EQ 0
Index lineas.i_artlineas =(3, 4) Cols: 2 Used 0 EQ 0
-Index Selected : None -
Index articulos.primary = (1, 2) Cols: 2 Used 0 EQ 0
Index articulos.i3_articulo = (1) Cols: 1 Used 0 EQ 0
Index articulos.i4_articulo = (2) Cols: 1 Used 0 EQ 0
Index articulos.i2_articulo = (3) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index proveedores.primary = (1) Cols: 1 Used 0 EQ 0
Index proveedores.i2_proveedor=(2) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index provpr.primary = (1) Cols: 1 Used 0 EQ 0
-Index Selected : None -
Comentario: Hasta este momento, el optimizador no puede determinar ningún índice que sea válido para acceder a ninguna de las tablas implicadas en la sentencia. El motivo es que todas las condicione incluidas en la sentencia se refieren únicamente a los JOINS establecidos entre las tablas. Al no encontrar ninguna condición del tipo «columna = valor», el optimizador no puede seleccionar aún ningún índice.
Compare Tables:
clientes (Keyparts 0 Unique, Rows 100)
provincias (Keyparts 0 Unique, Rows 50)<-(num rows)
Compare Tables:
provincias (Keyparts 0 Unique, Rows 50) <-
albaranes (Keyparts 0 Unique, Rows 50)
Compare Tables:
provincias (Keyparts 0 Unique, Rows 50) <-
lineas (Keyparts 0 Unique, Rows 512)
Compare Tables:
provincias (Keyparts 0 Unique, Rows 50) <-
articulos (Keyparts 0 Unique, Rows 194)
Compare Tables:
provincias (Keyparts 0 Unique, Rows 50) <-
proveedores (Keyparts 0 Unique, Rows 100)
Compare Tables:
provincias (Keyparts 0 Unique, Rows 50) <-
provpr (Keyparts 0 Unique, Rows 50)
- Table Selected : provincias -
Comentario: A continuación se comprueba cuál es la primera tabla que deberá ser accedida. En este caso la tabla seleccionada es «provincias», ya que es la que aparece en primer lugar en la cláusula FROM» de entre las que cuentan con el menor número de filas (50).
Index clientes.primary = (1) Cols:
1 Used 0 EQ 0
Index clientes.i2_cliente = (2) Cols: 1 Used 0 EQ 0
-Index Selected : None -
Index albaranes.i2_albaran = (2) Cols: 1 Used 0 EQ 0
Index albaranes.primary = (1) Cols: 1 Used 0 EQ 0
-Index Selected : None -
Index lineas.primary = (1, 2) Cols: 2 Used 0 EQ 0
Index lineas.alblin_ind = (1) Cols: 1 Used 0 EQ 0
Index lineas.i_artlineas = (3, 4) Cols: 2 Used 0 EQ 0
-Index Selected : None -
Index articulos.primary = (1, 2) Cols: 2 Used 0 EQ 0
Index articulos.i3_articulo = (1) Cols: 1 Used 0 EQ 0
Index articulos.i4_articulo = (2) Cols: 1 Used 0 EQ 0
Index articulos.i2_articulo = (3) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index proveedores.primary = (1) Cols: 1 Used 0 EQ 0
Index proveedores.i2_proveedor=(2) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index provpr.primary = (1) Cols: 1 Used 0 EQ 0
-Index Selected : None -
Comentario: Se vuelve a repetir el proceso de selección de índice para el resto de tablas (exceptuando la ya seleccionada -«provincias»-). En nuestro ejemplo, la única tabla que hace JOIN con «provincias» es «clientes», pero su columna de enlace no constituye un índice simple ni tampoco es la primera de un índice compuesto, por lo que el optimizador continúa sin poder seleccionar ningún índice.
Compare Tables:
clientes (Keyparts 0 Unique, Rows 100, (ATTR=VAL)) <-
albaranes (Keyparts 0 Unique, Rows 50)
Compare Tables:
clientes (Keyparts 0 Unique, Rows 100, (ATTR=VAL)) <-
lineas (Keyparts 0 Unique, Rows 512)
Compare Tables:
clientes (Keyparts 0 Unique, Rows 100, (ATTR=VAL)) <-
articulos (Keyparts 0 Unique, Rows 194)
Compare Tables:
clientes (Keyparts 0 Unique, Rows 100, (ATTR=VAL)) <-
proveedores (Keyparts 0 Unique, Rows 100)
Compare Tables:
clientes (Keyparts 0 Unique, Rows 100, (ATTR=VAL)) <-
provpr (Keyparts 0 Unique, Rows 50)
- Table Selected : clientes -
Comentario: El hecho de haber seleccionado ya la tabla de «provincias» ha permitido valorar la condición «clientes.provincia=provincias.provincia» como si fuese «clientes.provincia=VALOR». Éste es el motivo por el que la tabla de «clientes» es la seleccionada en esta fase («ATTR=VAL»).
Index albaranes.i2_albaran =
(2) Cols: 1 Used 0 EQ 0
Index albaranes.primary = (1) Cols: 1 Used 0 EQ 0
-Index Selected : i2_albaran -
Index lineas.primary = (1, 2) Cols: 2 Used 0 EQ
0
Index lineas.alblin_ind = (1) Cols: 1 Used 0 EQ 0
Index lineas.i_artlineas = (3, 4) Cols: 2 Used 0 EQ 0
-Index Selected : None -
Index articulos.primary = (1, 2) Cols: 2 Used 0 EQ 0
Index articulos.i3_articulo = (1) Cols: 1 Used 0 EQ 0
Index articulos.i4_articulo = (2) Cols: 1 Used 0 EQ 0
Index articulos.i2_articulo = (3) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index proveedores.primary = (1) Cols: 1 Used 0 EQ 0
Index proveedores.i2_proveedor=(2) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index provpr.primary = (1) Cols: 1 Used 0
EQ 0
-Index Selected : None -
Comentario: El hecho de haber seleccionado ya la tabla de «clientes» ha permitido valorar la condición «clientes.cliente=albaranes.cliente» como si fuese «albaranes.cliente=VALOR». Como la columna «cliente» de la tabla «albaranes» lleva asociado un índice («i2_albaran»), éste será seleccionado para acceder a la tabla.
Compare Tables:
albaranes (Keyparts 1 , Rows 50, (ATTR=VAL)) <-
lineas (Keyparts 0 Unique, Rows 512)
Compare Tables:
albaranes (Keyparts 1 , Rows 50, (ATTR=VAL)) <-
articulos (Keyparts 0 Unique, Rows 194)
Compare Tables:
albaranes (Keyparts 1 , Rows 50, (ATTR=VAL)) <-
proveedores (Keyparts 0 Unique, Rows 100)
Compare Tables:
albaranes (Keyparts 1 , Rows 50, (ATTR=VAL)) <-
provpr (Keyparts 0 Unique, Rows 50)
- Table Selected : albaranes -
Comentario: Por el mismo motivo que en el caso anterior, ahora ha sido posible seleccionar la tabla «albaranes».
Index lineas.primary =
(1, 2) Cols: 2 Used 0 EQ 0
Index lineas.alblin_ind = (1) Cols: 1 Used 0 EQ 0
Index lineas.i_artlineas = (3, 4) Cols: 2 Used 0 EQ 0
-Index Selected : alblin_ind -
Index articulos.primary = (1, 2) Cols: 2 Used 0 EQ 0
Index articulos.i3_articulo = (1) Cols: 1 Used 0 EQ 0
Index articulos.i4_articulo = (2) Cols: 1 Used 0 EQ 0
Index articulos.i2_articulo = (3) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index proveedores.primary = (1) Cols: 1 Used 0 EQ 0
Index proveedores.i2_proveedor=(2) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index provpr.primary = (1) Cols:
1 Used 0 EQ 0
-Index Selected : None -
Comentario: El hecho de haber seleccionado ya la tabla de «albaranes» ha permitido valorar la condición «albaranes.albaran=lineas.albaran» como si fuese «lineas.albaran=VALOR». Como la columna «albaran» de la tabla «lineas» lleva asociado un índice («alblin_ind»), éste será seleccionado para acceder a la tabla. Como se explica en la Sección 4.1, no se elige la clave primaria.
Compare Tables:
lineas (Keyparts 1 , Rows 512, (ATTR=VAL)) <-
articulos (Keyparts 0 Unique, Rows 194)
Compare Tables:
lineas (Keyparts 1 , Rows 512, (ATTR=VAL)) <-
proveedores (Keyparts 0 Unique, Rows 100)
Compare Tables:
lineas (Keyparts 1 , Rows 512, (ATTR=VAL)) <-
provpr (Keyparts 0 Unique, Rows 50)
- Table Selected : lineas -
Comentario: Por el mismo motivo que en el caso anterior, ahora ha sido posible seleccionar la tabla «lineas».
Index articulos.primary =
(1, 2) Cols: 2 Used 0 EQ 0
Index articulos.i3_articulo = (1) Cols: 1 Used 0 EQ 0
Index articulos.i4_articulo = (2) Cols: 1 Used 0 EQ 0
Index articulos.i2_articulo = (3) Cols: 1 Used 0 EQ 0
- Index Selected : i3_articulo -
Index proveedores.primary = (1) Cols: 1 Used 0 EQ 0
Index proveedores.i2_proveedor=(2) Cols: 1 Used 0 EQ 0
- Index Selected : None -
Index provpr.primary = (1) Cols:
1 Used 0 EQ 0
-Index Selected : None -
Comentario: El hecho de haber seleccionado ya la tabla de «lineas» ha permitido valorar la condición «lineas.articulo=articulos.articulo» como si fuese «articulos.articulo=VALOR». Como la columna «articulo» de la tabla «articulos» lleva asociado un índice («i3_articulo»), éste será seleccionado para acceder a la tabla. Como se explica en la Sección 4.1, no se elige la clave primaria.
Compare Tables:
articulos (Keyparts 1 , Rows 194, (ATTR=VAL)) <-
proveedores (Keyparts 0 Unique, Rows 100)
Compare Tables:
articulos (Keyparts 1 , Rows 194, (ATTR=VAL)) <-
provpr (Keyparts 0 Unique, Rows 50)
- Table Selected : articulos -
Comentario: Por el mismo motivo que en el caso anterior, ahora ha sido posible seleccionar la tabla «articulos».
Index proveedores.primary=(1) Cols:
1 Used 0 EQ 0
Index proveedores.i2_proveedor=(2)Cols:1Used 0 EQ 0
- Index Selected : primary -
Index provpr.primary = (1) Cols: 1 Used 0 EQ 0
-Index Selected : None -
Comentario: El hecho de haber seleccionado ya la tabla de «articulos» ha permitido valorar la condición «articulos.proveedor=proveedores.proveedor» como si fuese «proveedores.proveedor=VALOR». Como la columna «proveedor» de la tabla «proveedores» lleva asociado un índice (clave primaria), éste será seleccionado para acceder a la tabla. Como se explica en la Sección 4.1, ahora sí se elige la clave primaria.
Compare Tables:
proveedores (Keyparts 1 Unique, Rows 100, (ATTR=VAL)) <-
provpr (Keyparts 0 Unique, Rows 50)
- Table Selected : proveedores -
Comentario: Por el mismo motivo que en el caso anterior, ahora ha sido posible seleccionar la tabla «proveedores».
Index provpr.primary =
(1) Cols: 1 Used 0 EQ 0
-Index Selected : primary -
- Table Selected : provpr -
Comentario: El hecho de haber seleccionado ya la tabla de «proveedores» ha permitido valorar la condición «proveedores.provincia=provpr.provincia» como si fuese «provpr.provincia=VALOR». Como la columna «provincia» de la tabla «provpr» lleva asociado un índice (clave primaria), éste será seleccionado para acceder a la tabla.
Optimization Results : 7 Tables
Table [provincias] 0 Ranges
Table [clientes] 0 Ranges
Table [albaranes] 1 Ranges
Range[1] : key [start/length] ((4 / 4))
Table [lineas] 1 Ranges
Range[1] : key [start/length] ((0 / 4))
Table [articulos] 1 Ranges
Range[1] : key [start/length] ((0 / 2))
Table [proveedores] 1 Ranges
Range[1] : key [start/length] ((0 / 4))
Table [provpr] 1 Ranges
Range[1] : key [start/length] ((0 / 2))
Comentario: Por último, el optimizador escribe en el fichero un resumen del resultado de la optimización. Este resumen indica que la estrategia de acceso será la siguiente: Se accederá en primer lugar a la tabla «provincias» de forma secuencial («0 Ranges»); para cada fila leída se leerá toda la tabla «clientes» («0 Ranges»); por cada una de las filas válidas accederá a la tabla «albaranes» por un índice («1 Range»); por cada una de aquéllas accederá a la tabla «lineas» por un índice («1 Range»), etc.
Como se puede observar, la tabla «clientes» va a ser leída en su totalidad por cada fila de «provincias». Esto se podría evitar añadiendo una condición que fuerce al optimizador a seleccionar como primera tabla la tabla «clientes»:
select * from clientes, provincias,
albaranes,
lineas, articulos, proveedores, provincias provpr
where clientes.provincia = provincias.provincia
and clientes.cliente = albaranes.cliente
and albaranes.albaran = lineas.albaran
and lineas.articulo = articulos.articulo
and articulos.proveedor = proveedores.proveedor
and proveedores.provincia = provpr.provincia
and clientes.cliente > 0
En este caso el resultado final del optimizador sería:
Optimization Results : 7 Tables
Table [clientes] 1 Ranges
Range[1] : key [start/length] ((0 / 4))
Table [provincias] 1 Ranges
Range[1] : key [start/length] ((0 / 2))
Table [albaranes] 1 Ranges
Range[1] : key [start/length] ((4 / 4))
Table [lineas] 1 Ranges
Range[1] : key [start/length] ((0 / 4))
Table [articulos] 1 Ranges
Range[1] : key [start/length] ((0 / 2))
Table [proveedores] 1 Ranges
Range[1] : key [start/length] ((0 / 4))
Table [provpr] 1 Ranges
Range[1] : key [start/length] ((0 / 2))