Suele pasarnos que cuando queremos crear Vistas Materializadas, suelen haber errores y no logramos crearlas. O bien, logramos crearlas pero no suelen estar optimizadas para las capacidades que nosotros queremos. Para analizar y optimizar nuestras vistas materializadas, vamos a utilizar el paquete DBMS_MVIEW.EXPLAIN_MVIEW y la tabla MV_CAPABILITIES_TABLE.
Veamos un ejemplo:
Primero vamos a crear las tablas con los datos que vamos a utilizar para crear nuestra vista materializada:
SQL_10gR2> CREATE TABLE test1 AS
2 SELECT level id, level*level total
3 FROM dual
4 CONNECT BY level <= 10 ;
Table created.
SQL_10gR2> CREATE TABLE test2 AS
2 SELECT level id, 'nom_'||level nom
3 FROM dual
4 CONNECT BY level <= 19 ;
Table created.
SQL_10gR2> INSERT INTO test2 VALUES(19,'nom_19') ;
1 row created.
SQL_10gR2> COMMIT ;
Commit complete.
SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST1') ;
PL/SQL procedure successfully completed.
SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2') ;
PL/SQL procedure successfully completed.
Los datos quedan de la siguiente manera en las tablas:
SQL_10gR2> SELECT *
2 FROM test1 ;
ID TOTAL
---------- ----------
1 1
2 4
3 9
4 16
5 25
6 36
7 49
8 64
9 81
10 100
10 rows selected.
SQL_10gR2> SELECT *
2 FROM test2 ;
ID NOM
---------- ----------------
1 nom_1
2 nom_2
3 nom_3
4 nom_4
5 nom_5
6 nom_6
7 nom_7
8 nom_8
9 nom_9
10 nom_10
11 nom_11
12 nom_12
13 nom_13
14 nom_14
15 nom_15
16 nom_16
17 nom_17
18 nom_18
19 nom_19
19 nom_19
20 rows selected.
Bien, supongamos que queremos crear una vista materializada con la opción de REFRESH FAST ON DEMAND sobre la siguiente consulta:
SQL_10gR2> SELECT test2.id,
2 test2.nom,
3 SUM(test1.total) sum_total
4 FROM test1,
5 test2
6 WHERE test1.id = test2.id(+)
7 GROUP BY test2.id,
8 test2.nom
9 ORDER BY id ;
ID NOM SUM_TOTAL
---------- ---------- ----------
1 nom_1 1
2 nom_2 4
3 nom_3 9
4 nom_4 16
5 nom_5 25
6 nom_6 36
7 nom_7 49
8 nom_8 64
9 nom_9 81
10 nom_10 100
10 rows selected.
Antes de crear la vista materializada, vamos a crear las tablas de Log para el refresh fast:
SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
2 WITH ROWID, SEQUENCE (total)
3 INCLUDING NEW VALUES ;
Materialized view log created.
SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test2
2 WITH ROWID, SEQUENCE (id,nom)
3 INCLUDING NEW VALUES ;
Materialized view log created.
Ahora creamos la vista materializada:
SQL_10gR2> CREATE MATERIALIZED VIEW TEST_MV
2 NOCOMPRESS
3 LOGGING
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FAST ON DEMAND
7 USING DEFAULT LOCAL ROLLBACK SEGMENT
8 DISABLE QUERY REWRITE
9 AS
10 SELECT test2.id,
11 test2.nom,
12 SUM(test1.total) sum_total
13 FROM test1,
14 test2
15 WHERE test1.id = test2.id(+)
16 GROUP BY test2.id,
17 test2.nom
18 ORDER BY id ;
FROM test1,
*
ERROR at line 13:
ORA-12015: cannot create a fast refresh materialized view from a complex query
Observamos que no podemos crear la vista materializada usando fast refresh sobre nuestra consulta. Pero cómo identificamos cuales son los errores por los cuales no podemos crear la vista materializada?
Lo que vamos a utilizar es el procedimiento EXPLAIN_MVIEW del paquete DBMS_MVIEW. Lo ejecutamos colocando como parámetro del procedimiento la consulta que vamos a utilizar en la vista materializada:
SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total
8 FROM test1,
9 test2
10 WHERE test1.id = test2.id(+)
11 GROUP BY test2.id,
12 test2.nom
13 ORDER BY id '
14 ) ;
15
16 END ;
17 /
BEGIN
*
ERROR at line 1:
ORA-30377: table SQL_10GR2.MV_CAPABILITIES_TABLE not found
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XRWMV", line 23
ORA-06512: at "SYS.DBMS_XRWMV", line 43
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3007
El error que estamos viendo se debe a que no tenemos que creada en nuestro esquema la tabla MV_CAPABILITIES_TABLE que se encuentra en $ORACLE_HOME/rdbms/admin (el script se llama utlxmv.sql) que utiliza el procedimiento para guardar la información resultante de la ejecución.
Creamos la tabla MV_CAPABILITIES_TABLE:
SQL_10gR2> @$ORACLE_HOME/rdbms/admin/utlxmv.sql
Table created.
Ejecutamos nuevamente el procedimiento:
SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total
8 FROM test1,
9 test2
10 WHERE test1.id = test2.id(+)
11 GROUP BY test2.id,
12 test2.nom
13 ORDER BY id '
14 ) ;
15
16 END ;
17 /
PL/SQL procedure successfully completed.
Ahora consultamos la tabla:
SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;
CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table
PCT_TABLE N TEST2 relation is not a partitioned
table
REFRESH_FAST_AFTER_INSERT N outer join in mv
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables
PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table
PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table
16 rows selected.
Lo que nos muestra esta consulta es lo siguiente:
- Un listado de todas las capacidades de la vista materializada (columna CAPABILITY_NAME).
- Por cada capacidad, nos muestra si es posible o no (columna POSSIBLE con valores 'Y' o 'N').
- Si la capacidad no es posible, nos muestra porque no es posible (columna MSGTXT).
Como nosotros queremos crear la vista materializada con la opción de REFRESH FAST ON DEMAND, vemos la columna CAPABILITY_NAME y observamos los valores de la columna MSGTXT para lo que tiene que ver con REFRESH_FAST_%.
Podemos ver que en REFRESH_FAST_AFTER_INSERT vemos que dice "outer join in mv". Como nosotros tenemos un outer join en la vista materializada, no podemos realizar refresh fast porque existen ciertas restricciones que debemos cumplir y que en éste ejemplo no estamos cumpliendo. Una de esas condiciones dice que las columnas que contienen el outer join en la condición de join de la consulta, tienen que tener creadas constraints primary key o índices unique key.
En la TABLA2 de nuestro ejemplo, tenemos un valor duplicado (el ID 19). Veamos qué sucede si eliminamos uno de los ID 19 y creamos una primary key en la tabla TEST2 por el campo ID:
SQL_10gR2> DELETE FROM test2
2 WHERE id = 19
3 AND rownum = 1 ;
1 row deleted.
SQL_10gR2> COMMIT ;
Commit complete.
SQL_10gR2> ALTER TABLE test2 ADD CONSTRAINT id_2_pk PRIMARY KEY (id) ;
Table altered.
SQL_10gR2> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST2', cascade => true) ;
PL/SQL procedure successfully completed.
SQL_10gR2> SELECT *
2 FROM test2 ;
ID NOM
---------- ----------
1 nom_1
2 nom_2
3 nom_3
4 nom_4
5 nom_5
6 nom_6
7 nom_7
8 nom_8
9 nom_9
10 nom_10
11 nom_11
12 nom_12
13 nom_13
14 nom_14
15 nom_15
16 nom_16
17 nom_17
18 nom_18
19 nom_19
19 rows selected.
Bien, ejecutamos nuevamente el procedimiento EXPLAIN_MVIEW:
SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;
Table truncated.
SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total
8 FROM test1,
9 test2
10 WHERE test1.id = test2.id(+)
11 GROUP BY test2.id,
12 test2.nom
13 ORDER BY id '
14 ) ;
15
16 END ;
17 /
PL/SQL procedure successfully completed.
Consultamos nuevamente la tabla:
SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;
CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table
PCT_TABLE N TEST2 relation is not a partitioned
table
REFRESH_FAST_AFTER_INSERT N SQL_10GR2. mv log does not have all neces
TEST1 sary columns
REFRESH_FAST_AFTER_ONETAB_DML N SUM_TOTAL SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the
select list
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables
PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table
PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table
19 rows selected.
Podemos observar que el error anterior ya no aparece más. En REFRESH_FAST_AFTER_ONETAB_DML vemos el mensaje "COUNT(*) is not present in the select list". Esto nos está diciendo que debemos agregar a la consulta un COUNT(*).
Vamos a agregar el COUNT(*) en la consulta y realizar el mismo procedimiento de antes:
SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;
Table truncated.
SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total,
8 COUNT(*) cnt
9 FROM test1,
10 test2
11 WHERE test1.id = test2.id(+)
12 GROUP BY test2.id,
13 test2.nom
14 ORDER BY id '
15 ) ;
16
17 END ;
18 /
PL/SQL procedure successfully completed.
SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;
CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table
PCT_TABLE N TEST2 relation is not a partitioned
table
REFRESH_FAST_AFTER_INSERT N SQL_10GR2. mv log does not have all neces
TEST1 sary columns
REFRESH_FAST_AFTER_ONETAB_DML N SUM_TOTAL SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables
PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table
PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table
18 rows selected.
Podemos observar que el error anterior ya no aparece más. Ahora en REFRESH_FAST_AFTER_ONETAB_DML vemos "SUM(expr) without COUNT(expr)". Como estamos utilizando la función SUM, debemos agregar un COUNT(expr) a la consulta:
SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;
Table truncated.
SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total,
8 COUNT(*) cnt,
9 COUNT(test1.total) cnt_sum
10 FROM test1,
11 test2
12 WHERE test1.id = test2.id(+)
13 GROUP BY test2.id,
14 test2.nom
15 ORDER BY id'
16 ) ;
17
18 END ;
19 /
PL/SQL procedure successfully completed.
SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;
CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table
PCT_TABLE N TEST2 relation is not a partitioned
table
REFRESH_FAST_AFTER_INSERT N SQL_10GR2. mv log does not have all neces
TEST1 sary columns
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAS
T_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAS
T_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables
PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table
PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table
16 rows selected.
Podemos observar que el error anterior ya no aparece más. El último error que nos falta solucionar para poder crear la vista materializada con refresh fast es el que aparece en REFRESH_FAST_AFTER_INSERT con el mensaje "mv log does not have all necessary columns". Al parecer creamos de forma incorrecta la tabla de Logs de la tabla TEST1 y nos está diciendo que tenemos que agregar a la tabla de Log todas las columnas que aparecen en nuestra consulta.
Lo que vamos a hacer es dropear la tabla de Log que habíamos creado y recrearla agregando una columna:
SQL_10gR2> DROP MATERIALIZED VIEW LOG on test1 ;
Materialized view log dropped.
SQL_10gR2> CREATE MATERIALIZED VIEW LOG ON test1
2 WITH ROWID, SEQUENCE (id,total)
3 INCLUDING NEW VALUES ;
Materialized view log created.
SQL_10gR2> TRUNCATE TABLE mv_capabilities_table ;
Table truncated.
SQL_10gR2> BEGIN
2
3 DBMS_MVIEW.EXPLAIN_MVIEW
4 (
5 'SELECT test2.id,
6 test2.nom,
7 SUM(test1.total) sum_total,
8 COUNT(*) cnt,
9 COUNT(test1.total) cnt_sum
10 FROM test1,
11 test2
12 WHERE test1.id = test2.id(+)
13 GROUP BY test2.id,
14 test2.nom
15 ORDER BY id'
16 ) ;
17
18 END ;
19 /
PL/SQL procedure successfully completed.
SQL_10gR2> SELECT capability_name, possible, related_text, msgtxt
2 FROM mv_capabilities_table ;
CAPABILITY_NAME P RELATED_TE MSGTXT
------------------------------ - ---------- ------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE N TEST1 relation is not a partitioned
table
PCT_TABLE N TEST2 relation is not a partitioned
table
REFRESH_FAST_AFTER_INSERT Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML Y
REFRESH_FAST_PCT N PCT is not possible on any of
the detail tables in the mater
ialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N general rewrite is not possibl
e or PCT is not possible on an
y of the detail tables
PCT_TABLE_REWRITE N TEST1 relation is not a partitioned
table
PCT_TABLE_REWRITE N TEST2 relation is not a partitioned
table
16 rows selected.
Buenísimo! Al parecer ya estamos en condiciones de crear la vista materializada utilizando la opción de REFRESH FAST ON DEMAND. Veamos...
ihubowner@ihub1t> CREATE MATERIALIZED VIEW TEST_MV
2 NOCOMPRESS
3 LOGGING
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FAST ON DEMAND
7 USING DEFAULT LOCAL ROLLBACK SEGMENT
8 DISABLE QUERY REWRITE
9 AS
10 SELECT test2.id,
11 test2.nom,
12 SUM(test1.total) sum_total,
13 COUNT(*) cnt,
14 COUNT(test1.total) cnt_sum
15 FROM test1,
16 test2
17 WHERE test1.id = test2.id(+)
18 GROUP BY test2.id,
19 test2.nom
20 ORDER BY id ;
Materialized view created.
