Procesamiento y Optimización de ConsultasBases de Datos 2 - Tecnologo de Informatica

BD2 - Programación

Outline

1 Proceso de Optimización

2 Optimización por Heurísticas

3 Optimización por Costos

BD2 - Programación

Outline

1 Proceso de Optimización

2 Optimización por Heurísticas

3 Optimización por Costos

BD2 - Programación

¿Cómo se resuelven las consultas?

BD2 - Programación

Estrategias usuales de los optimizadores

Proceso detalla de optimización.Optimización Heurística

Basada en equivalencia de las expresiones del álgebra yciertas estrategias básicas para limitar el tamaño de losresultados.

Optimización por CostosBasada en estimaciones y datos del catálogo que permitenseleccionar un mejor plan de acceso.

BD2 - Programación

Proceso de Optimización

BD2 - Programación

Resumen del Proceso de Optimización

Generación del Algebra (Árbol Canónico)Generación de planes lógicos (Optimización Heurística)

Implica la aplicación de determinadas estrategias(heurísticas) y consultas al catálogo para tamaños de lasrelaciones para transformar el árbol original.

Generación de planes físicos (Optimización por Costos)Implica asociar a cada operación de los planes lógicosgenerados una o más implementaciones.Queé implementación depende de las estructuras de datosdisponibles.

Selección del Plan final (Optimización por Costos)Implica la evaluación de los planes físicos generados enbase a las cantidades de operaciones de I/O que realizacada algoritmo.

BD2 - Programación

Outline

1 Proceso de Optimización

2 Optimización por Heurísticas

3 Optimización por Costos

BD2 - Programación

Optimización por Heurísticas

Cambiar la consulta original por otra equivalente de formade minimizar los resultados intermedio.Pueden existir varias alternativas.Se basa en aplicar equivalencias de los operadores delálgebra de forma que:

Las selecciones y las proyecciones se apliquen lo antesposible.Las operaciones de selección y join que son másrestrictivas se apliquen antes que las demás.

BD2 - Programación

Reglas de equivalencia de expreiones

σp1∧p2 (R) = σp1 (σp2 (R)) Cascada de

σp1 (σp2 (R)) = σp2 (σp1 (R)) Conmutativa de la selección

πan (πak ...an (R)) = πan (R))

πa1...an (σp (R)) = σp (πa1...an (R)) [si p sólo contienea1 . . . an]

σp (R × E) = R onp E Equivalencia join - producto y selección

R onp E = E onp R Conmutativa del join

(R onp E) onp S = R onp (E onp S) Asociativa del join

σp (R × E) = (σp (R))× E [si p sólo contiene atributos deR]

πan ∪ ak (R × E) = πan (R)× πak (E) [si an es de R y ak esde E ]

BD2 - Programación

Reglas de equivalencia de expreiones

R ∪ E = E ∪ R Conmutativa de la unión

R ∩ E = E ∩ R Conmutativa de la intersección

R ∪ (E ∪ D) = (R ∪ E) ∪ D Asociativa de la unión

R ∩ (E ∩ D) = (R ∩ E) ∩ D Conmutativa de la intersección

σp (R ∪ E) = σp (R) ∪ σp (E)

σp (R ∩ E) = σp (R) ∩ σp (E)

σp (R − E) = σp (R)− σp (E)Distributivas

πan (R ∪ E) = πan (R) ∪ πan (E)

BD2 - Programación

HeurísticasReglas para reducir los tamaños intermedios

1 Cambiar las selecciones conjuntivas por una “cascada” deselecciones simples.

2 Mover las selecciones lo más abajo que se pueda en elárbol.

3 Reacomodar las hojas del árbol de modo que lasselecciones más restrictivas son ejecutadas antes,evitando productos cartesianos.

4 Cambiar secuencias de selecciones y productos por Join.

5 Mover las proyecciones lo más abajo posible en el árbol,agregando las proyecciones que sean necesarias.

BD2 - Programación

Ejemplo de optimización heurísticas

BD2 - Programación

Ejemplo

Esquema:

Consulta:

SELECT E.APELLIDOFROM EMPLEADO AS E, TRABAJA_EN AS T, PROYECTO AS PWHERE P.NombreProyecto=’Acuario’ AND

P.NumProyecto=T.NumProy AND T.DniEmpleado=E.Dni ANDE.FechaNac>’31−DIC−57’

BD2 - Programación

Ejemplo

πApellido

σNombreProyecto=′Acuario′∧NumProyecto=NumProy

∧DniEmpleado=Dni∧ FechaNac>′31−DIC−57′

×

PROYECTO×

TRABAJA_ENEMPLEADO

πApellido

σNumProyecto=NumProy

×

σNombreProyecto=′Acuario′

PROYECTO

σDniEmpleado=Dni

×

TRABAJA_ENσFechaNac>′31−DIC−57′

EMPLEADO

1, 2

BD2 - Programación

Ejemplo

πApellido

σDniEmpleado=Dni

×

σFechaNac>′31−DIC−57′

EMPLEADO

σNumProyecto=NumProy

×

TRABAJA_ENσNombreProyecto=′Acuario′

PROYECTO

πApellido

onDniEmpleado=Dni

σFechaNac>′31−DIC−57′

EMPLEADO

onNumProyecto=NumProy

TRABAJA_ENσNombreProyecto=′Acuario′

PROYECTO

3 4

BD2 - Programación

Ejemplo

πApellido

onDniEmpleado=Dni

πDni,Apellido

σFechaNac>′31−DIC−57′

EMPLEADO

πDniEmpleado

onNumProyecto=NumProy

πDniEmpleado,NumProy

TRABAJA_EN

πNumProyecto

σNombreProyecto=′Acuario′

PROYECTO

5

BD2 - Programación

Outline

1 Proceso de Optimización

2 Optimización por Heurísticas

3 Optimización por Costos

BD2 - Programación

Optimización por Costos

Plan FísicoLe asocia a cada operador del álgebra que aparece en unplan lógico, una implementación.Como se pueden considerar diferentes implementacionespara cada operador, entonces un mismo plan lógico puedeoriginar diferentes planes físicos.Es necesario estimar el costo (cantidad de operaciones deI/O) de los diferentes planes que se generen y elegir el decosto mínimo.

Para evaular el costo, es necesario considerar ciertosparámetros que tienen influencia en el cálculo de lacantidad de operaciones de I/O.

BD2 - Programación

Parámetros para la Estimación de Costos y TamañosNombre Definción Notación FórmulaTamaño oCardinalidad deuna Relación T

Cantidad de Registros r , nT -

Tamaño delRegistro de unaRelación T

Cantidad de Byes de un registro R, RT -

Cantidad deBloques para unaRelación T

Cantidad de bloques necesariospara almacenar los registros deuna relación

b, bT -

Factor deBloqueo para unaRelación o índiceT

Cantidad de registros que entranen un bloque (blocking factor)

bfr , bfrT bbytes delbloque/bytes delregistroc

Cantidad deniveles de uníndice

Cantidad de niveles de un índice(la fórmula depende del tipo)

x , xT logk (nT ) + 1(para un B+ con kpunteros pornodo sobre clave)

Cantidad devalores distintosdel atributo A enla tabla T

Cantidad de valores distintos quetiene un atributo en una tabla

d ,V (A,T ),NDV (A,T )

nT (para unatributo clave)

BD2 - Programación

Parámetros para la Estimación de Costos y Tamaños

Nombre Definción Notación FórmulaSelectividadde unaselección

Fracción que indica cuántosregistros se debenseleccionar con respecto ala tabla original

sl, sl (σc (T )) 1/V (A,T ) (si lacondición es unaigualdad por el atributoA y se asumedistribución uniforme)

Selectividadde un join

Fracción que indica cuántosregistros se debenseleccionar con respecto alproducto cartesiano original

js, js (R onc S) 1/Min (V (A,R) ,V (A,S))(si es el join natural deR y S por el atributo A)

Cardinalidadde unaselección

Cantidad de registros en elresultado de una selección

s, T (σc (R)) nR ∗ sl (σc (R))

Cardinalidadde un join

Cantidad de registros en elresultado de un join

j, T (R onc S) nR ∗ nS ∗ js (R onc S)

- Información del tipo decada índice (si es primario,o arbol B+, etc.)

- -

BD2 - Programación

Implementaciones de los operadores

A cada operador de un plan lógico se le asigna unaimplementación.Luego hay que estimar el costo de todo el plan basándoseen los costos de cada algoritmo.Es importante la estrategia de implementación:

Pipelined: hay operadores que se ejecutansimultáneamente y pueden pasarse los resultados amedida que se generan. No necesita grabar los resultadointermedios.No Pipelined: los operadores se ejecutan secuencialmentey es necesario grabar resultados intermedios.

Asumimos:Selección y Join (No Pipelined): Se debe considerar elcosto de grabar el resultado intermedio.Proyección (Pipelined): no hay costo intermedio.

BD2 - Programación

Implementaciones de los operadoresEstimaciones de Costos

En el costo consideramos sólo los accesos a disco:de lectura.de grabación.

Siempre se realizan las operaciones de a bloque (página)que pueden contener varios registros de índice o datos.Los costos de lectura dependen de la organización de losdatos.El costo de grabación siempre es el costo de grabar todoel resultado (R):

dnR/bfrRe donde bfrR = (cant. bytes bloques) / (cant. bytestupla)

Al ver los algoritmos consideramos las lecturas, pero en elcosto debemos agregar la grabación.

BD2 - Programación

Implementaciones de la selección σ (R)

Búsqueda linealRestricción de uso: ninguna.

Descripcón: leer cada registro y si cumple la condición sepone en el resultado.Costos de lectura:

Peor caso: bR (cantidad de bloques de la relación R)Promedio: bR/2

Búsqueda BinariaRestricción de uso: registros ordenados.

Descripcón: leer el bloque del medio y en función de lacondición leer el del medio de la primera o segunda mitad yasí hasta encontrarlo o no tener más bloques para leer.

Costos de lectura: log2 bR + ds/bfrRe − 1

BD2 - Programación

Implementaciones de la selección σ (R) con Índices

Primario o ClusterRestricción de uso: registros ordenados.Costos de lectura:

x + ds/bfrRe (x es la cantidad de niveles del índice)Si el índice es primario x+1 (sólo 1 bloque tiene el valorbuscado)

HashRestricción de uso: sólo para condiciones de igualdad.

Costos de lectura: 1 o 2 dependiendo del tipo de hash.

Secundario con B+Restricción de uso: ninguna

Costos de lectura: x + s (peor caso, asumiendo que cadaregistro está en un bloque distinto.

BD2 - Programación

Implementaciones del Join R onA=B S

Loop anidado por registrosRestricción de uso: ninguna

Descripcón: para cada registro de R acceder a todos losbloques de S y combinar ese registro de R con todos los deS.

Costos de lectura: bR + nR ∗ bS

Loop anidado por bloquesRestricción de uso: ninguna

Descripcón: para cada bloque de R combinar todos losregistros de ese bloque con todos los bloques de S.

Costos de lectura: bR + dbR/ (M

Cantidad de buffers

− 2)e ∗ bS

BD2 - Programación

Implementaciones del Join R onA=B S

Sort-Merge JoinRestricción de uso: las dos tablas deben tener losregistros ordenados. Si no es así hay que agregar loscostos de ordenación.Descripcón: recorrer R y S en paralelo combinando losregistros.Costos de lectura: bR + bSCostos de ordenación: 2 ∗ b ∗ (1 + log2 b)

Index Join (Single Loop)Restricción de uso: existencia de un índice para S.Descripcón: recorrer R y acceder por el índice a S.Costos de lectura: bR + (nR ∗ Z ) donde Z depende deltipo de índice,

secundario: Z = x + sS

cluster: Z = x + dsS/bfrSeprimario: Z = x + 1hash = h

BD2 - Programación

Implementacion de los Operadoresσc (R)

Algoritmo Costo Condición Organización

σc (R)

Búsqueda Lineal bR (peor caso)bR/2 (prom)

Todas –

Búsqueda Binaria log2 bR +ds/bfrRe − 1

Todas Ordenado

Índice Primario x + 1 Igualdad Ordenado

Hash 1 o 2 Igualdad –

Índice Primario x + (b/2) (prom) de orden Ordenado

Índice Cluster x + ds/bfrRe Todas Ordenado

Índice secundario B+ x + s (peor caso) Todas –

Grabación Intermedia s/bfrR Todas –

BD2 - Programación

Implementacion de los OperadoresR onc S

Algoritmo Costo Condición Organización

R onc S

Loop Anidado(registros)

bR + (nR ∗ bS) Todas –

Loop Anidado(bloque)

bR+d(bR/ (M − 2)e∗bS Todas –

Sort Merge bR + bS +costo ordenación

Todas Índice

Index join bR + nR ∗ Z Todas Índice

BD2 - Programación

Ejemplo de optimización por costos

BD2 - Programación