Materialized Views: DB can help to implement a MV

Materialized view are extensively used to improve the performance or to store ready reference de-normalized data in database. It must be de-normalized up to a trade line where we can benefit for the performance and also DB should not have issues with MV refresh.
From 10g advisory framework, we can take advice in what best way we can implement the MV. we can also confirm what capability any MV have.
Following I am presenting a hands-on to implement above description:
Let start from scratch:

$ sqlplus hr/hr

SQL> create materialized view vv as select department_id,count(*),sum(salary),avg(salary),max(salary) from employees group by department_id;

Materialized view created.

Let us check whether MV can be used by optimizer to enhance the performance or not:

SQL> select department_id,sum(salary) from employees group by department_id;

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 77 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
639 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed

Answer is NO

We can cross verify its property from below view

SQL> desc user_mviews
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
MVIEW_NAME NOT NULL VARCHAR2(30)
CONTAINER_NAME NOT NULL VARCHAR2(30)
QUERY LONG
QUERY_LEN NUMBER(38)
UPDATABLE VARCHAR2(1)
UPDATE_LOG VARCHAR2(30)
MASTER_ROLLBACK_SEG VARCHAR2(30)
MASTER_LINK VARCHAR2(128)
REWRITE_ENABLED VARCHAR2(1)
REWRITE_CAPABILITY VARCHAR2(9)
REFRESH_MODE VARCHAR2(6)
REFRESH_METHOD VARCHAR2(8)
BUILD_MODE VARCHAR2(9)
FAST_REFRESHABLE VARCHAR2(18)
LAST_REFRESH_TYPE VARCHAR2(8)
LAST_REFRESH_DATE DATE
STALENESS VARCHAR2(19)
AFTER_FAST_REFRESH VARCHAR2(19)
UNKNOWN_PREBUILT VARCHAR2(1)
UNKNOWN_PLSQL_FUNC VARCHAR2(1)
UNKNOWN_EXTERNAL_TABLE VARCHAR2(1)
UNKNOWN_CONSIDER_FRESH VARCHAR2(1)
UNKNOWN_IMPORT VARCHAR2(1)
UNKNOWN_TRUSTED_FD VARCHAR2(1)
COMPILE_STATE VARCHAR2(19)
USE_NO_INDEX VARCHAR2(1)
STALE_SINCE DATE

As i have only one MV in my schema, I can query like ...

SQL> select rewrite_enabled from user_mviews;

R
-
N

Now we enable query rewrite option of MV so that optimizer can do query transformation before it start to probe for execution plan. we will check out th plan as well...

SQL> alter materialized view vv enable query rewrite;

Materialized view altered.

SQL> select department_id,sum(salary) from employees group by department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1835011786

--------------------------------------------------------------------------------
-----

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
-----

| 0 | SELECT STATEMENT | | 12 | 84 | 3 (0)| 00:00
:01 |

| 1 | MAT_VIEW REWRITE ACCESS FULL| VV | 12 | 84 | 3 (0)| 00:00
:01 |

--------------------------------------------------------------------------------
-----

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
639 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed

Above plan had verified that MV is used by optimizer to facilitate the user query.

Now we will submit our initial code to get back best code from advisory.

========================================================================
Generate MV Code
========================================================================

SQL> variable v1 varchar2(50)
SQL> exec :v1:='MYY';

PL/SQL procedure successfully completed.

SQL> conn / as sysdba
Connected.
SQL> grant advisor to hr;

Grant succeeded.

SQL> conn hr/hr
Connected.

SQL> exec DBMS_ADVISOR.TUNE_MVIEW ( :v1,'create materialized view mym as select department_id,count(*),sum(salary),avg(salary),max(salary) from employees group by department_id');

PL/SQL procedure successfully completed.


SQL> variable v2 varchar2(4000)
SQL> exec :v2:=DBMS_ADVISOR.GET_TASK_SCRIPT(:v1);

PL/SQL procedure successfully completed.

SQL> print v2;

V2
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 10.2.0.3.0 - Production
Rem
Rem Username: HR
Rem Task: MYY
Rem Execution date:
Rem

CREATE MATERIALIZED VIEW LOG ON
"HR"."EMPLOYEES"
WITH ROWID, SEQUENCE("SALARY","DEPARTMENT_ID")
INCLUDING NEW VALUES;

V2
--------------------------------------------------------------------------------

ALTER MATERIALIZED VIEW LOG FORCE ON
"HR"."EMPLOYEES"
ADD ROWID, SEQUENCE("SALARY","DEPARTMENT_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW HR.MYM
REFRESH FAST WITH ROWID
DISABLE QUERY REWRITE
AS SELECT HR.EMPLOYEES.DEPARTMENT_ID C1, MAX("HR"."EMPLOYEES"."SALARY") M1,
SUM("HR"."EMPLOYEES"."SALARY")

V2
--------------------------------------------------------------------------------
M2, COUNT("HR"."EMPLOYEES"."SALARY") M3, COUNT(*) M4 FROM HR.EMPLOYEES
GROUP BY HR.EMPLOYEES.DEPARTMENT_ID;

SQL>
==============================================================================
MV Capability
========================================================================


SQL> @?/rdbms/admin/utlxmv

Table created.

SQL> desc mv_capabilities_table
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
MVOWNER VARCHAR2(30)
MVNAME VARCHAR2(30)
CAPABILITY_NAME VARCHAR2(30)
POSSIBLE CHAR(1)
RELATED_TEXT VARCHAR2(2000)
RELATED_NUM NUMBER
MSGNO NUMBER(38)
MSGTXT VARCHAR2(2000)
SEQ NUMBER

SQL> exec dbms_mview.explain_mview('vv','shail');

PL/SQL procedure successfully completed.

SQL> select CAPABILITY_NAME,POSSIBLE,RELATED_TEXT,MSGTXT from mv_capabilities_table;

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

What I learned about Oracle

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today