Materialized views gives you an opportunity to store precomputed data for faster access and better performance.
There are two types of materialized views: aggregate materialized views and only join materialized views.
Materialized views are managed with DBMS_MVIEW package, you can also use that package to manually refresh materialized views.
With creation of materialized views, depending on the type two or three objects are defined.
Materialized view object and container table for the data are always created for both aggregate and only joins mviews. If you create aggregate materialized view index is also created.
Before materialized views if you wanted to improve performance of your summaries you had to use CTAS and run your query against that table.
SQL> create table demo_tmv1 as 2 select sum(s.amount_sold) t1, count(s.amount_sold) t2, c.cust_id 3 from sales s, customers c 4 where s.cust_id = c.cust_id 5 group by c.cust_id; Table created.
But, CTAS are very hard to maintenance, the DBA will have to manually refresh the table plus the developers will have to change the application code in order to use that table. This is where materialized view are great option and you can benefit from query rewrite mechanism of oracle, where optimizer rewrites user query to use the materialized view if it decides that will benefits from it. Before creating materialized view you can estimate the size and bytes for the mview using ESTIMATE_MVIEW_SIZE procedure. In this example I’m using SH scheme.
SQL> variable v_num_rows number; SQL> variable v_num_bytes number; SQL> SQL> begin 2 dbms_mview.estimate_mview_size('stmt1', 3 'select sum(s.amount_sold),count(s.amount_sold),c.cust_id 4 from sales s, customers c 5 where s.cust_id = c.cust_id 6 group by c.cust_id',:v_num_rows,:v_num_bytes); 7 end; 8 / PL/SQL procedure successfully completed. SQL> print :v_num_rows; V_NUM_ROWS ---------- 7059 SQL> print :v_num_bytes; V_NUM_BYTES ----------- 465894 SQL>
When you create materialized view you can specify storage options, when the mview should be populated (immediate, or deferred), how to refresh data and enable/disable query rewrite.
If you specify BUILD DEFERRED in create statement, the mview will not populate until the next first complete refresh. You can query BUILD_MODE column from DBA_MVIEWS table to check build mode for specific materialized view.
SQL> create materialized view demo_mv1 2 refresh force on demand 3 enable query rewrite 4 as 5 select sum(s.amount_sold), count(s.amount_sold), c.cust_id 6 from sales s, customers c 7 where c.cust_id = s.cust_id 8 group by c.cust_id; Materialized view created.
If you already have created table as summary you can just register that table as materialized view with ON PREBUILT TABLE statement. This is good option because user-defined materialized view can be very big and recreating materialized view from scratch will take a lot of time and performance. Now, you can register that table as materialized view and you are good to go.
SQL> create materialized view demo_tmv1 2 enable query rewrite 3 as 4 select sum(s.amount_sold) t1, count(s.amount_sold) t2, c.cust_id 5 from sales s, customers c 6 where s.cust_id = c.cust_id 7 group by c.cust_id; from sales s, customers c * ERROR at line 5: ORA-00955: name is already used by an existing object
I received ORA-00955 error because the name is already used, that is because I didn’t specify ON PREBUILT TABLE.
Let’s try again.
SQL> create materialized view demo_tmv1 2 on prebuilt table 3 enable query rewrite 4 as 5 select sum(s.amount_sold) t1, count(s.amount_sold) t2, c.cust_id 6 from sales s, customers c 7 where s.cust_id = c.cust_id 8 group by c.cust_id; Materialized view created.
Let’s check information in DBA_MVIEWS.
SQL> select owner,mview_name,build_mode,rewrite_enabled 2 from dba_mviews 3 where mview_name = 'DEMO_TMV1'; OWNER MVIEW_NAME BUILD_MOD R ------------------------------ ------------------------------ --------- - SH DEMO_TMV1 PREBUILT Y
If I drop the materialized view created with ON PREBUILT TABLE the detail table won’t be dropped.
SQL> drop materialized view demo_tmv1; Materialized view dropped. SQL> select count(*) from demo_tmv1; COUNT(*) ---------- 7060
If you want to benefit from query rewrite mechanism just include ENABLE_REWRITE in create statement, but be careful when specifying column alias.
If you specify column alias in the query of create materialized view statement and later you run query against details table without those column alias you won’t use query rewrite.
As a solution to this is to specify column alias as list in create statement of the materialized view.
SQL> SQL> create materialized view demo_mv3 2 (sum_amount,cnt_amount,customer_id) 3 enable query rewrite 4 as 5 select sum(s.amount_sold), count(s.amount_sold), c.cust_id 6 from sales s, customers c 7 where s.cust_id = c.cust_id 8 group by c.cust_id; Materialized view created.