Reescritura automática de consultas - ejemplos complejos

Estos ejemplos pueden ayudarle a comprender cómo Db2 aplica la reescritura automática de consultas para evitar agregaciones costosas y uniones en tablas de hechos grandes.

En los siguientes ejemplos se utiliza un caso de ejemplo en el que un depósito de datos tiene un esquema en estrella. El esquema en estrella representa los datos de una aplicación simplificada de tarjeta de crédito, tal como se muestra en la siguiente figura.

Comenzar descripción de figura. Las tablas de un esquema en estrella de varios hechos de ejemplo. Las flechas representan relaciones clave. Este concepto se describe en el siguiente párrafo. Finalizar descripción de figura. — Figura 1. Esquema de estrella multifactorial. En esta aplicación simplificada de tarjeta de crédito, las tablas de hechos TRANSITEM y TRANS forman el concentrador del esquema en estrella. El esquema también contiene cuatro dimensiones: producto, ubicación, cuenta y tiempo.

El depósito de datos registra las transacciones que se realizan con tarjetas de crédito. Cada transacción consta de un conjunto de elementos que se compran a la vez. En el centro del depósito de datos hay dos grandes tablas de hechos. TRANS registra el conjunto de transacciones de compra con tarjeta de crédito. TRANSITEM registra la información sobre los elementos comprados. Juntas, estas dos tablas de hechos constituyen el concentrador del esquema en estrella. Este esquema en estrella es de varios hechos porque contiene estas dos tablas de hechos. Las tablas de hechos se actualizan continuamente para cada nueva transacción con tarjeta de crédito.

Además de las dos tablas de hechos, el esquema contiene cuatro dimensiones que describen transacciones: producto, ubicación, cuenta y tiempo.

La dimensión producto consta de dos tablas normalizadas, PGROUP y PLINE, que representan el grupo de productos y la línea de productos.
La dimensión ubicación consta de una sola tabla no normalizada, LOC, que contiene ciudad, estado y país.
La dimensión cuenta consta de dos tablas normalizadas, ACCT y CUST, que representan la cuenta y el cliente.
La dimensión tiempo consta de la tabla TRANS que contiene día, mes y año.

Los analistas de este tipo de aplicación de tarjeta de crédito suelen estar interesados en la agregación de los datos de ventas. Sus consultas suelen realizar uniones de una o más tablas de dimensiones con tablas de hechos. Las tablas de hechos contienen bastantes más filas que las tablas de dimensiones y las consultas complicadas en las que intervienen grandes tablas de hechos suelen resultar caras. En muchos casos, puede utilizar una tabla de consulta materializada para resumen y almacenar la información procedente de las tablas de hechos. Utilizar de tablas de consulta materializadas puede ayudarle a evitar agregaciones costosas y uniones contra tablas de hechos grandes. Finalizar la información de la interfaz de programación específica del programa.

Ejemplo 1

Iniciar la información de la interfaz de programación de uso general. Un analista envía la siguiente consulta para contar el número de transacciones que se realizan en Estados Unidos para cada tarjeta de crédito. El analista solicita los resultados agrupados por cuenta de tarjeta de crédito, estado y año:

UserQ1
------
SELECT T.ACCTID, L.STATE, T.YEAR, COUNT(*) AS CNT
  FROM TRANS T, LOC L
  WHERE T.LOCID = L.ID AND
        L.COUNTRY = 'USA'
  GROUP BY T.ACCTID, L.STATE, T.YEAR;

Supongamos que tenemos la siguiente sentencia CREATE TABLE creada en una tabla de consulta materializada llamada TRANSCNT:

CREATE TABLE TRANSCNT AS
  (SELECT ACCTID, LOCID, YEAR, COUNT(*) AS CNT
     FROM TRANS
     GROUP BY ACCTID, LOCID, YEAR )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED;

Si habilita la reescritura automática de consultas, Db2 puede reescribir UserQ1 como NewQ1. NewQ1 accede a la tabla de consulta materializada TRANSCNT en lugar de a la tabla de hechos TRANS.

NewQ1
-----
SELECT A.ACCTID, L.STATE, A.YEAR, SUM(A.CNT) AS CNT
  FROM TRANSCNT A, LOC L
  WHERE A.LOCID = L.ID     AND
        L.COUNTRY = 'USA'
  GROUP BY A.ACCTID, L.STATE, A.YEAR;

Db2 puede utilizar la reescritura de consultas en este caso por las razones siguientes:

La tabla TRANS es común a UserQ1 y a TRANSCNT.
Db2 puede derivar las columnas del resultado de la consulta de TRANSCNT.
GROUP BY en la consulta solicita que los datos se agrupen a un nivel superior al nivel de la definición de TRANSCNT.

Puesto que los clientes suelen realizar varios cientos de transacciones al año, la mayoría en la misma ciudad, TRANSCNT es unas cien veces menor que TRANS. Por lo tanto, reescribir UserQ1 en una consulta que utiliza TRANSCNT en lugar de TRANS mejora significativamente el tiempo de respuesta.

Finalizar la información de la interfaz de programación de uso general.

Ejemplo 2

Iniciar la información de la interfaz de programación de uso general.

Supongamos que un analista desea encontrar el número de televisores, con un precio superior a 100 y un descuento superior a 0,1, que fueron adquiridos por cada cuenta de tarjeta de crédito. El analista envía la siguiente consulta:

UserQ2
------
SELECT T.ID, TI.QUANTITY * TI.PRICE * (1 - TI.DISCOUNT) AS AMT
  FROM TRANSITEM TI, TRANS T, PGROUP PG
  WHERE TI.TRANSID = T.ID  AND
        TI.PGID = PG.ID    AND
        TI.PRICE > 100     AND
        TI.DISCOUNT > 0.1  AND
        PG.NAME = 'TV';

Si define la siguiente tabla de consulta materializada TRANSIAB, Db2 puede reescribir UserQ2 como NewQ2:

TRANSIAB
--------
CREATE TABLE TRANSIAB AS
(SELECT TI.TRANSID, TI.PRICE, TI.DISCOUNT, TI.PGID,
        L.COUNTRY, TI.PRICE * TI.QUANTITY as VALUE
   FROM TRANSITEM TI, TRANS T, LOC L
   WHERE TI.TRANSID = T.ID  AND
         T.LOCID = L.ID     AND
         TI.PRICE > 1       AND
         TI.DISCOUNT > 0.1)
   DATA INITIALLY DEFERRED
   REFRESH DEFERRED;


NewQ2
-----
SELECT A.TRANSID, A.VALUE * (1 - A.DISCOUNT) as AM
  FROM TRANSIAB A, PGROUP PG
  WHERE A.PGID = PG.ID     AND
        A.PRICE > 100      AND
        PG.NAME = 'TV';

Db2 puede reescribir UserQ2 como una nueva consulta que utiliza la tabla de consulta materializada TRANSIAB por las las razones siguientes:

Aunque el predicado T.LOCID = L.ID aparece solo en la tabla de consulta materializada, no da lugar a filas que Db2 podría descartar. La restricción de referencia entre las columnas TRANS.LOCID y LOC.ID hace que la unión entre TRANS y LOC en la definición de la tabla de consulta materializada no se pueda perder. La unión no se puede perder solo si la clave foránea de la restricción es NOT NULL.
Los predicados TI.TRANSID = T.ID y TI.DISCOUNT > 0.1 aparecen tanto en la consulta de usuario como en la sentencia fullselect de TRANSIAB.
El predicado fullselect TI.PRICE >1 de TRANSIAB adopta el predicado de la consulta de usuario TI.PRICE > 100 en UserQ2. Como el predicado de fullselect es más inclusivo que el predicado de consulta de usuario, Db2 puede calcular el predicado de consulta de usuario de TRANSIAB.
El predicado de la consulta de usuario PG.NAME = 'TV' hace referencia a una tabla que no está en el predicado de fullselect de TRANSIAB. Sin embargo, Db2 puede calcular el predicado de la tabla PGROUP. Un predicado como PG.NAME = 'TV' no descalifica otros predicados de una consulta para la reescritura automática de consultas. En este caso, PGROUP es una tabla de dimensiones relativamente pequeña, de modo que un predicado que hace referencia a la tabla no resulta especialmente caro.
Db2 puede derivar el resultado de la consulta de la definición de tabla de consulta materializada, incluso cuando la derivación no es fácilmente aparente:
- Db2 deriva T. ID en la consulta de T. TRANSID en fulleselect de TRANSIAB. Aunque estas dos columnas se originan en dos tablas distintas, son equivalentes debido al predicado T.TRANSID = T.ID. Db2 reconoce dicha equivalencia de columnas a través de predicados de unión. Por lo tanto, Db2 deriva T. ID de T. TRANSID, y la consulta es apta para la reescritura automática de consultas.
- Db2 deriva AMT en la consulta UserQ2 de DISCOUNT y VALUE en el fullselect de TRANSIAB.

Ejemplo 3

Iniciar la información de la interfaz de programación de uso general. Este ejemplo muestra cómo Db2 coincide con las funciones GROUP BY y las funciones agregadas entre la consulta de usuario y fullselect de la tabla de consulta materializada. Supongamos que un analista envía la siguiente consulta para buscar el valor medio de los elementos de las transacciones por año:

UserQ3
------
SELECT YEAR, AVG(QUANTITY * PRICE) AS AVGVAL
  FROM TRANSITEM TI, TRANS T
  WHERE TI.TRANSID = T.ID
  GROUP BY YEAR;

Si define la siguiente tabla de consulta materializada TRANSAVG, Db2 puede reescribir UserQ3 como NewQ3:

TRANSAVG
--------
CREATE TABLE TRANSAVG AS
  (SELECT T.YEAR, T.MONTH, SUM(QUANTITY * PRICE) AS TOTVAL, COUNT(*) AS CNT
     FROM TRANSITEM TI, TRANS T
     WHERE TI.TRANSID = T.ID
     GROUP BY T.YEAR, T.MONTH )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED;


NewQ3
-----
SELECT YEAR, CASE WHEN SUM(CNT) = 0 THEN NULL
                  ELSE SUM(TOTVAL)/SUM(CNT)
                  END AS AVGVAL
  FROM TRANSAVG
  GROUP BY YEAR;

Db2 puede reescribir UserQ3 como una nueva consulta que utiliza la tabla de consulta materializada TRANSAVG por las razones siguientes:

Db2 considera YEAR en la consulta de usuario y YEAR en el fullselect de la tabla de consulta materializada para que coincida exactamente.
Db2 puede derivar la función AVG en la consulta de usuario de la función SUM y la función COUNT en el fullselect de la tabla de consulta materializada.
La cláusula GROUP BY de la consulta NewQ3 solicita datos a un nivel superior al nivel de la definición de TRANSAVG.
Db2 puede calcular el promedio anual en la consulta del usuario utilizando las sumas mensuales y los recuentos de los elementos de transacción en TRANSAVG. Db2 obtiene los promedios anuales de las columnas CNT y TOTVAL de la tabla de consulta materializada mediante una expresión case.