In large datawarehouse systems queries take long time to execute, generally. To solve these problems Oracle has improved its queries and tools. One of them is materialized views as called MVs. Main idea of this MVs are to pre calculate or join tables. Data has formed and stored inside MVs in database, like an object such as a table.
Suppose that you want to get average of a table that has two millions of records. As you predict, it is a long query. When you create a MVs to store averages, it will be very esay to read from MV instead of calculating all two millions of data. When a modification has performed on that table, MV has to update itself. This is a very comon use of MVs in Oracle.
Query rewrite takes place on calling this precalculated average. When you specify on create materialized view statement to enable query rewrite, before execution, it will be checked whether it can be rewrited. If so, instead of calculating again, precalculated table -MV- will selected.
For joining you may create a materialized view log to access the tables records.
To make more clear, there is demonstration below:
SQL> DROP TABLE base_table;
DROP TABLE base_table
ORA-00942: table or view does not exist
SQL> CREATE TABLE base_table AS
2 SELECT MOD(rownum, 20) id, round(dbms_random. VALUE(1000, 100000)) tot
3 FROM all_objects, (SELECT * FROM all_tab_cols WHERE ROWNUM < 4);
Table created
Executed in 47,703 seconds
SQL> DROP TABLE lookup_table;
DROP TABLE lookup_table
ORA-00942: table or view does not exist
SQL> CREATE TABLE lookup_table AS
2 SELECT MOD( rownum, 20) id, object_type nam FROM all_objects WHERE rownum < 20 ;
Table created
Executed in 0,672 seconds
Inserting without MV with "REFRESH FAST ON COMMIT"
SQL> INSERT INTO base_table
2 SELECT MOD(rownum, 20) id, round(dbms_random.VALUE(1000, 100000)) tot
3 FROM all_objects;
22109 rows inserted
Executed in 49,032 seconds
SQL> commit;
Commit complete
Executed in 0,016 seconds
Without MV a query
SQL> SELECT l.nam, AVG(b.tot)
2 FROM base_table b, lookup_table l
3 WHERE b.id = l.id
4 GROUP BY l.nam;
NAM AVG(B.TOT)
------------------- ----------
CLUSTER 50766,7546
INDEX 50567,1634
TABLE 50658,3567
Executed in 0,984 seconds
SQL> CREATE MATERIALIZED VIEW LOG ON base_table WITH SEQUENCE, ROWID
2 (id,tot)
3 INCLUDING NEW VALUES;
Materialized view log created
Executed in 0,5 seconds
SQL> CREATE MATERIALIZED VIEW LOG ON lookup_table WITH SEQUENCE, ROWID
2 (id,nam)
3 INCLUDING NEW VALUES;
Materialized view log created
Executed in 0,156 seconds
SQL> DROP MATERIALIZED VIEW base_lookup_mv;
DROP MATERIALIZED VIEW base_lookup_mv
ORA-12003: materialized view "SYSADM"."BASE_LOOKUP_MV" does not exist
Creating MV with given options
SQL> CREATE MATERIALIZED VIEW base_lookup_mv
2 PARALLEL
3 BUILD IMMEDIATE
4 REFRESH FAST ON COMMIT
5 ENABLE QUERY REWRITE AS
6 SELECT l.nam
7 ,COUNT(b.tot) count_tot
8 ,SUM(b.tot) sum_tot
9 ,AVG(b.tot) avg_tot
10 FROM base_table b, lookup_table l
11 WHERE b.id = l.id
12 GROUP BY l.nam;
Materialized view created
Executed in 3,297 seconds
Selecting v$parameter view to see values of query_rewrite:
SQL> SELECT name, value FROM v$parameter WHERE name like 'query_rewrite_%';
NAME VALUE
-------------------------------------------------------------------------------- -----------------
query_rewrite_enabled TRUE
query_rewrite_integrity enforced
Executed in 0,031 seconds
Query With MV
SQL> SELECT l.nam, AVG(b.tot)
2 FROM base_table b, lookup_table l
3 WHERE b.id = l.id
4 GROUP BY l.nam;
NAM AVG(B.TOT)
------------------- ----------
INDEX 50567,1634
TABLE 50658,3567
CLUSTER 50766,7546
Executed in 0,422 seconds
Inserting with MV
SQL> INSERT INTO base_table
2 SELECT MOD(rownum, 20 ) id, round(dbms_random.VALUE(1000, 100000)) tot
3 FROM all_objects;
22114 rows inserted
Executed in 71,829 seconds
SQL> commit ;
Commit complete
Executed in 8,672 seconds
As you see, queries become faster but inserts become slower. As a result, for OLTP based systems it is not a good idea to use MVs. With 3 times executions performance results (in ms) are below:
Normal Insert : 60
MatViw Insert : 70
Normal Select : 1,015
MatViw Select : 0.557
Lets look at execution plans:
SQL> explain plan set statement_id = 'menn' for
2 SELECT l.nam, AVG(b.tot)
3 FROM base_table b, lookup_table l
4 WHERE b.id = l.id
5 GROUP BY l.nam;
Explained
SQL> @E:\oracle\product\10.2.0\db_2\rdbms\admin\utlxpls.sql menn
Cannot SET MARKUP
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2191832405
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 72 | 2
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 3 | 72 | 2
| 3 | PX BLOCK ITERATOR | | 3 | 72 | 2
| 4 | MAT_VIEW REWRITE ACCESS FULL| BASE_LOOKUP_MV | 3 | 72 | 2
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
15 rows selected
SQL> DROP MATERIALIZED VIEW base_lookup_mv;
Materialized view dropped
SQL>
SQL> explain plan set statement_id = 'menn' for
2 SELECT l.nam, AVG(b.tot)
3 FROM base_table b, lookup_table l
4 WHERE b.id = l.id
5 GROUP BY l.nam;
Explained
SQL> @E:\oracle\product\10.2.0\db_2\rdbms\admin\utlxpls.sql menn
Cannot SET MARKUP
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1936153920
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107K| 5268K| 110 (46)| 00:00:
| 1 | SORT GROUP BY | | 107K| 5268K| 110 (46)| 00:00:
|* 2 | HASH JOIN | | 107K| 5268K| 73 (18)| 00:00:
| 3 | TABLE ACCESS FULL| LOOKUP_TABLE | 19 | 456 | 3 (0)| 00:00:
| 4 | TABLE ACCESS FULL| BASE_TABLE | 113K| 2883K| 64 (11)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."ID"="L"."ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected
SQL>
As it is seen with MV although you write the same statement, Oracle rewrites the query to use MV.
Suppose that you want to get average of a table that has two millions of records. As you predict, it is a long query. When you create a MVs to store averages, it will be very esay to read from MV instead of calculating all two millions of data. When a modification has performed on that table, MV has to update itself. This is a very comon use of MVs in Oracle.
Query rewrite takes place on calling this precalculated average. When you specify on create materialized view statement to enable query rewrite, before execution, it will be checked whether it can be rewrited. If so, instead of calculating again, precalculated table -MV- will selected.
For joining you may create a materialized view log to access the tables records.
To make more clear, there is demonstration below:
SQL> DROP TABLE base_table;
DROP TABLE base_table
ORA-00942: table or view does not exist
SQL> CREATE TABLE base_table AS
2 SELECT MOD(rownum, 20) id, round(dbms_random. VALUE(1000, 100000)) tot
3 FROM all_objects, (SELECT * FROM all_tab_cols WHERE ROWNUM < 4);
Table created
Executed in 47,703 seconds
SQL> DROP TABLE lookup_table;
DROP TABLE lookup_table
ORA-00942: table or view does not exist
SQL> CREATE TABLE lookup_table AS
2 SELECT MOD( rownum, 20) id, object_type nam FROM all_objects WHERE rownum < 20 ;
Table created
Executed in 0,672 seconds
Inserting without MV with "REFRESH FAST ON COMMIT"
SQL> INSERT INTO base_table
2 SELECT MOD(rownum, 20) id, round(dbms_random.VALUE(1000, 100000)) tot
3 FROM all_objects;
22109 rows inserted
Executed in 49,032 seconds
SQL> commit;
Commit complete
Executed in 0,016 seconds
Without MV a query
SQL> SELECT l.nam, AVG(b.tot)
2 FROM base_table b, lookup_table l
3 WHERE b.id = l.id
4 GROUP BY l.nam;
NAM AVG(B.TOT)
------------------- ----------
CLUSTER 50766,7546
INDEX 50567,1634
TABLE 50658,3567
Executed in 0,984 seconds
SQL> CREATE MATERIALIZED VIEW LOG ON base_table WITH SEQUENCE, ROWID
2 (id,tot)
3 INCLUDING NEW VALUES;
Materialized view log created
Executed in 0,5 seconds
SQL> CREATE MATERIALIZED VIEW LOG ON lookup_table WITH SEQUENCE, ROWID
2 (id,nam)
3 INCLUDING NEW VALUES;
Materialized view log created
Executed in 0,156 seconds
SQL> DROP MATERIALIZED VIEW base_lookup_mv;
DROP MATERIALIZED VIEW base_lookup_mv
ORA-12003: materialized view "SYSADM"."BASE_LOOKUP_MV" does not exist
Creating MV with given options
SQL> CREATE MATERIALIZED VIEW base_lookup_mv
2 PARALLEL
3 BUILD IMMEDIATE
4 REFRESH FAST ON COMMIT
5 ENABLE QUERY REWRITE AS
6 SELECT l.nam
7 ,COUNT(b.tot) count_tot
8 ,SUM(b.tot) sum_tot
9 ,AVG(b.tot) avg_tot
10 FROM base_table b, lookup_table l
11 WHERE b.id = l.id
12 GROUP BY l.nam;
Materialized view created
Executed in 3,297 seconds
Selecting v$parameter view to see values of query_rewrite:
SQL> SELECT name, value FROM v$parameter WHERE name like 'query_rewrite_%';
NAME VALUE
-------------------------------------------------------------------------------- -----------------
query_rewrite_enabled TRUE
query_rewrite_integrity enforced
Executed in 0,031 seconds
Query With MV
SQL> SELECT l.nam, AVG(b.tot)
2 FROM base_table b, lookup_table l
3 WHERE b.id = l.id
4 GROUP BY l.nam;
NAM AVG(B.TOT)
------------------- ----------
INDEX 50567,1634
TABLE 50658,3567
CLUSTER 50766,7546
Executed in 0,422 seconds
Inserting with MV
SQL> INSERT INTO base_table
2 SELECT MOD(rownum, 20 ) id, round(dbms_random.VALUE(1000, 100000)) tot
3 FROM all_objects;
22114 rows inserted
Executed in 71,829 seconds
SQL> commit ;
Commit complete
Executed in 8,672 seconds
As you see, queries become faster but inserts become slower. As a result, for OLTP based systems it is not a good idea to use MVs. With 3 times executions performance results (in ms) are below:
Normal Insert : 60
MatViw Insert : 70
Normal Select : 1,015
MatViw Select : 0.557
Lets look at execution plans:
SQL> explain plan set statement_id = 'menn' for
2 SELECT l.nam, AVG(b.tot)
3 FROM base_table b, lookup_table l
4 WHERE b.id = l.id
5 GROUP BY l.nam;
Explained
SQL> @E:\oracle\product\10.2.0\db_2\rdbms\admin\utlxpls.sql menn
Cannot SET MARKUP
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2191832405
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 72 | 2
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 3 | 72 | 2
| 3 | PX BLOCK ITERATOR | | 3 | 72 | 2
| 4 | MAT_VIEW REWRITE ACCESS FULL| BASE_LOOKUP_MV | 3 | 72 | 2
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
15 rows selected
SQL> DROP MATERIALIZED VIEW base_lookup_mv;
Materialized view dropped
SQL>
SQL> explain plan set statement_id = 'menn' for
2 SELECT l.nam, AVG(b.tot)
3 FROM base_table b, lookup_table l
4 WHERE b.id = l.id
5 GROUP BY l.nam;
Explained
SQL> @E:\oracle\product\10.2.0\db_2\rdbms\admin\utlxpls.sql menn
Cannot SET MARKUP
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1936153920
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107K| 5268K| 110 (46)| 00:00:
| 1 | SORT GROUP BY | | 107K| 5268K| 110 (46)| 00:00:
|* 2 | HASH JOIN | | 107K| 5268K| 73 (18)| 00:00:
| 3 | TABLE ACCESS FULL| LOOKUP_TABLE | 19 | 456 | 3 (0)| 00:00:
| 4 | TABLE ACCESS FULL| BASE_TABLE | 113K| 2883K| 64 (11)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."ID"="L"."ID")
Note
-----
- dynamic sampling used for this statement
20 rows selected
SQL>
As it is seen with MV although you write the same statement, Oracle rewrites the query to use MV.
No comments:
Post a Comment