We can enable the pre-requisite condition #2 by altering session parameters to allow or enable query rewrite:
- Pre-requisite 2.a) to enable query rewrite (the preferred value is TRUSTED):
ALTER SESSION SET query_rewrite_integrity = TRUSTED ;
- Pre-requisite 2.b) to enable query rewrite (the preferred value is TRUE)
ALTER SESSION SET query_rewrite_enabled=TRUE;
Or we could use this setting below:
ALTER SESSION SET query_rewrite_enabled=FORCE;
If we wanted to make these changes global in scope for the instance and / or database, or if we wanted to persist these changes permanently, we would issue an ALTER SYSTEM command instead. Please be aware that an ALTER SYSTEM command would change these settings for the entire instance (and possibly for the database), so please consult with your DBA before issuing any command that affects all other users and sessions of that database instance.
Now, let’s verify the pre-requisite condition #2:
select name, value from v$parameter
where name like 'query_rewrite_%'
order by name asc;
The query result is depicted in Figure 12 below:
Figure 12 – Values of parameters for query rewrite.
Now, let’s enable the pre-requisite condition #3 by creating the materialized view with query rewrite enabled, as shown below:
create materialized view sales_mv_tk
build immediate
refresh on demand
enable query rewrite
as
select customer_hierarchy_tk.zip_code,
time_hierarchy_tk.mmyyyy,
sum(sales_tk.sales_amount) sales_amount
from sales_tk, time_hierarchy_tk, customer_hierarchy_tk
where sales_tk.trans_date = time_hierarchy_tk.day
and sales_tk.cust_id = customer_hierarchy_tk.cust_id
group by customer_hierarchy_tk.zip_code, time_hierarchy_tk.mmyyyy;
The SQL DDL statement above aggregates fact data at the first level in each dimension, which is immediately above the grain level of that dimension. As shown previously in Figure 5 and Figure 9 on Page 2 of this article, those dimension levels are ZIP_CODE and MMYYYY.
Let’s see how the optimizer chooses to process the following SQL query, with the new session parameters we had previously set:
set serveroutput on size 900000;
declare
v_code NUMBER;
v_errm VARCHAR2(4000);
sql_stmt varchar2(500) :=
'select customer_hierarchy_tk.region,
time_hierarchy_tk.yyyy,
sum(sales_tk.sales_amount) sales_amount
from sales_tk, time_hierarchy_tk, customer_hierarchy_tk
where sales_tk.trans_date = time_hierarchy_tk.day
and sales_tk.cust_id = customer_hierarchy_tk.cust_id
group by customer_hierarchy_tk.region, time_hierarchy_tk.yyyy';
begin
dbms_output.enable;
sys.xrw(mv_list => null, command_list => 'REWRITTEN_TXT', querytxt => sql_stmt);
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1 , 3999);
DBMS_OUTPUT.PUT_LINE('Exception: ERRCODE=' || v_code || ' ERRMSG=' || v_errm);
DBMS_OUTPUT.PUT_LINE('Call Stack=[' || DBMS_UTILITY.FORMAT_ERROR_STACK || ']');
DBMS_OUTPUT.PUT_LINE('Error Stack=[' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ']');
ROLLBACK;
RAISE;
end;
We can see in Figure 13 below, the results of query rewrite for the SQL statement listed above:
Figure 13
We can see in Figure 14 below, the comparison of the original SQL query vs the rewritten SQL query, with some explanations regarding how data aggregation has changed after query rewrite:
Figure 14
Comparison of execution costs with query rewrite enabled vs disabled
In Figure 15 below, we can see a comparison of query execution costs before and after query rewrite, as displayed in a GUI database access tool such as SQL Developer:
Figure 15
In Figure 16 below, we can see a comparison of query execution costs before and after query rewrite, as displayed by SQL Plus:
Figure 16
Summary of findings for this case study
- Total estimated cost improves by 70 times when the query is rewritten to MV
- Estimated IO cost improves by 100 times when the query is rewritten to MV
- The number of physical disk IO reads improves by 18 times when the query is rewritten to MV (2942 vs 156 physical reads from the IO system, as measured at query execution time)
- The storage size of the aggregate MV created for query rewrite is significantly less than the storage size of the fact table at the detail level, as shown in Figure 17 below for our case study. Sizeof(MV) / Sizeof(fact table) = 0.26%
Figure 17
Benefits of query rewrite to materialized views
- Significantly improved query response time
- Significantly reduced number of IO reads from disk
- Users can write their queries against the “detail tables” (see Page 1), and the oracle optimizer will automatically rewrite those queries to use the materialized views
- Users do not need to be aware of the materialized views (MVs)
- The query rewrite mechanism in the oracle server is transparent
- One materialized view can be used to report at different levels of aggregation
- Materialized views can be added or dropped just like indexes without invalidating the SQL in the application code
- The storage size of the aggregate MV(s) created for query rewrite is significantly less that the storage size of the original fact table(s), thus allowing for faster and fewer IO operations which are needed to generate the results