Ejemplo - Query rewrite

El QUERY REWRITE es un facilidad que aporta oracle cuanto tenemos que hacer agrupamientos de datos muy grandes; Por ejemplo en un DataWarehouse. Es muy normal que nos pidan por ejemplo: “Agregado de las ventas por meses”.

En este caso, tendríamos que hacer un group by por meses, pero claro, en un DW pueden haber centenares de millones de registros!!!!

Query rewrite, nos facilita que el CBO detecte una Vista Materializada que ya tiene un agregado precalculado, y el CBO se de cuenta de que pej. es mas fácil sumar 12 meses ya calculados que 365 dias.

ALTER SYSTEM SET query_rewrite_integrity = 'STALE_TOLERATED' SCOPE = BOTH

Crear Usuario

CREATE USER DUMMY
IDENTIFIED BY dummy
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
 
GRANT CONNECT TO DUMMY;
GRANT RESOURCE TO DUMMY;
ALTER USER DUMMY DEFAULT ROLE ALL;
GRANT UNLIMITED TABLESPACE TO DUMMY;
 
--- Grants Otorgados ---  
 
GRANT QUERY REWRITE TO DUMMY;
 
GRANT CREATE MATERIALIZED VIEW TO DUMMY;
 
GRANT CREATE DIMENSION TO DUMMY;

EJEMPLO 1

Creación de las tablas de ejemplo

CREATE TABLE TEST ( tdate DATE,  DATA NUMBER(4), tno  VARCHAR2(10));
 
CREATE TABLE TIME 
(sdate 	    DATE,
 MONTH 	    NUMBER,
 month_name VARCHAR2(15),
 quarter    NUMBER,
 YEAR 	    NUMBER);

Introducir datos de ejemplo en la tablas

BEGIN
FOR i IN 1..159660 LOOP
	INSERT INTO TEST VALUES(TO_DATE('01-01-2000','dd-mm-yyyy') + MOD(i,365),
		MOD(i,9999),
		'AAAAAAAAAA');
END LOOP;
END;

DECLARE
l_sdate DATE;
BEGIN
l_sdate:=TO_DATE('01-01-2000','dd/mm/yyyy');
FOR i IN 1..366 LOOP
	INSERT INTO TIME(sdate,MONTH,month_name,quarter,YEAR)
	VALUES(l_sdate,
		TO_CHAR(l_sdate,'mm'),
		TO_CHAR(l_sdate,'mon'),
		CEIL(TO_NUMBER(TO_CHAR(l_sdate,'mm'))/3),
		TO_CHAR(l_sdate,'yyyy'));
	l_sdate := l_sdate + 1;
END LOOP;
END;

Creación de las vistas Materializadas

CREATE MATERIALIZED VIEW MV_TEST
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS
SELECT tno,SUM(DATA) DATA,t.MONTH
FROM TEST, TIME t
WHERE tdate=t.sdate
GROUP BY tno,t.MONTH;

CREATE MATERIALIZED VIEW MV_TEST
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT tno,SUM(DATA) DATA,t.MONTH
FROM TEST, TIME t
WHERE tdate=t.sdate
GROUP BY tno,t.MONTH;

CREATE MATERIALIZED VIEW MV_TEST
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT tno, SUM(DATA) DATA,t.MONTH,t.YEAR
FROM TEST, TIME t
WHERE tdate=t.sdate
GROUP BY tno,t.MONTH,t.YEAR;
</code >
----
 
ALTER SESSION SET query_rewrite_enabled = TRUE
 
*Chequear que Efectivamente el Query Rewrite lo hace correctamente
<code sql>
	SELECT SUM(DATA)
	FROM   TEST, TIME t
	WHERE  tdate=t.sdate;

Crear las dimensiones

En este caso probaremos que hace el rewrite en base al tiempo

CREATE DIMENSION dim_test
LEVEL sdate IS TIME.sdate
LEVEL MONTH IS TIME.MONTH
LEVEL qtr   IS TIME.quarter
LEVEL yr
	IS TIME.YEAR
HIERARCHY calendar_rollup (
sdate CHILD OF
MONTH CHILD OF
qtr CHILD OF yr)
ATTRIBUTE MONTH
DETERMINES month_name;

 Chequear que efectivamente el CBO ve más eficiente acceder al total de 
 Trimestres a traves de la VM de meses que sumando meses...

SELECT t.quarter, 
	SUM(DATA)
FROM TEST,
	TIME t
WHERE tdate=t.sdate
GROUP BY t.quarter

SELECT t.YEAR, 
	SUM(DATA)
FROM TEST,
	TIME t
WHERE tdate=t.sdate
GROUP BY t.YEAR

Otras pruebas sobre el mismo ejemplo

BEGIN
FOR i IN 1..1596600 LOOP
	INSERT INTO TEST VALUES(TO_DATE('01-01-2002','dd-mm-yyyy') + MOD(i,365),
		MOD(i,9999),
		'AAAAAAAAAA');
END LOOP;
END;
 
-------------
 
DECLARE
l_sdate DATE;
BEGIN
l_sdate:=TO_DATE('01-01-2002','dd/mm/yyyy');
FOR i IN 1..366 LOOP
	INSERT INTO TIME(sdate,MONTH,month_name,quarter,YEAR)
	VALUES(l_sdate,
		TO_CHAR(l_sdate,'mm'),
		TO_CHAR(l_sdate,'mon'),
		CEIL(TO_NUMBER(TO_CHAR(l_sdate,'mm'))/3),
		TO_CHAR(l_sdate,'yyyy'));
	l_sdate := l_sdate + 1;
END LOOP;
END;
 
-------
 
CREATE MATERIALIZED VIEW MV_TEST
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT tno, SUM(DATA) DATA,t.MONTH,t.YEAR
FROM TEST, TIME t
WHERE tdate=t.sdate
GROUP BY tno,t.MONTH,t.YEAR;
 
-------
 
SELECT t.YEAR,
	t.quarter, 
	SUM(DATA)
FROM TEST,
	TIME t
WHERE tdate=t.sdate
GROUP BY t.YEAR,t.quarter
 
--- 
 
SELECT t.YEAR, 
	SUM(DATA)
FROM TEST,
	TIME t
WHERE tdate=t.sdate
GROUP BY t.YEAR
 
---
 
SELECT t.quarter, 
	SUM(DATA)
FROM TEST,
	TIME t
WHERE tdate=t.sdate
GROUP BY t.quarter
 
---
 
SELECT SUM(DATA) FROM TEST
 
---