Learnt something new in Oracle today, it’s all about Query
rewrite capabilities and Explain Rewrite functionality. Let’s begin with a
demo.
rajesh@ORA10G> create table t1
2 as
3 select * from dba_users;
Table created.
rajesh@ORA10G>
rajesh@ORA10G> alter table t1
2 add constraint t1_pk
3 primary key(username);
Table altered.
rajesh@ORA10G> create table t2
2 as
3 select b.*
4 from dba_objects b,
5 dba_users a
6 where a.username = b.owner;
Table created.
rajesh@ORA10G> alter table t2
2 add constraint t2_fk
3 foreign key(owner)
4 references t1;
Table altered.
rajesh@ORA10G> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
rajesh@ORA10G> exec dbms_stats.gather_table_stats(user,'T2');
PL/SQL procedure successfully completed.
rajesh@ORA10G>
Table T1 and T2 got created along with necessary primary key
and foreign keys, table T2 in this context is referred to as Key-Preserved table, since each row
from T2 joins at most only once with T1, next we define our materialized views.
rajesh@ORA10G> create materialized view t1_mv
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select owner,object_type,count(*),
7 sum(object_id),count(object_id),
8 min(object_id), max(object_id),
9 avg(object_id),
grouping_id(owner,object_type) grp
10 from t1, t2
11 where t1.username = t2.owner
12 group by cube(owner,object_type) ;
Materialized view created.
rajesh@ORA10G>
rajesh@ORA10G> exec dbms_stats.gather_table_stats(user,'T1_MV');
PL/SQL procedure successfully completed.
rajesh@ORA10G>
Now for the Validation part,
rajesh@ORA10G> set autotrace traceonly explain
rajesh@ORA10G> select owner,count(*)
2 from t1, t2
3 where t1.username = t2.owner
4 and t1.username ='SCOTT'
5 group by owner;
Execution Plan
----------------------------------------------------------
Plan hash value: 2390704265
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
3 | 39 | 3
(0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE
ACCESS FULL| T1_MV | 3 | 39 |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1_MV"."OWNER"='SCOTT'
AND "T1_MV"."GRP"=1)
rajesh@ORA10G> select object_type,sum(object_id)
2 from t1, t2
3 where t1.username = t2.owner
4 group by object_type
5 having sum(object_id) > 500;
Execution Plan
----------------------------------------------------------
Plan hash value: 2390704265
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
104 | 1872 | 3
(0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE
ACCESS FULL| T1_MV | 104 | 1872 |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("T1_MV"."GRP"=2 AND
"T1_MV"."SUM(OBJECT_ID)">500)
rajesh@ORA10G> set autotrace off
Queries are transparently rewritten using materialized views,
we are good so far. But when we had the
queries in this way – rewrite doesn’t happen
rajesh@ORA10G> set autotrace traceonly explain
rajesh@ORA10G>
rajesh@ORA10G> select owner,count(*)
2 from
t2
3 group by owner
4 having count(*) > 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2529584183
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
2 | 10 | 127
(4)| 00:00:02 |
|* 1 | FILTER | |
| | | |
| 2 | HASH GROUP BY |
| 2 | 10 |
127 (4)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T2 | 39115 |
190K| 124 (1)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter(COUNT(*)>100)
rajesh@ORA10G> set autotrace off
So how to find why the rewrites are not happening in this particular
case, even though we require data only
from our Key persevered tables, Enter
into the explain_rewrite method added up in dbms_mview API from Oracle 10g
database.
rajesh@ORA10G>
rajesh@ORA10G> create or replace function
explain_rewrite(p_qry varchar2)
2 return sys.RewriteArrayType
3 pipelined
4 as
5 pragma autonomous_transaction;
6 l_data sys.RewriteArrayType :=
sys.RewriteArrayType();
7 begin
8
dbms_mview.explain_rewrite(p_qry,null,l_data);
9 for x in ( select * from table(l_data) )
10 loop
11 pipe row(
sys.RewriteMessage(x.mv_owner ,
12 x.mv_name ,
13 x.sequence ,
14 x.query_text ,
15 x.query_block_no ,
16 x.rewritten_text ,
17 x.message ,
18 x.pass ,
19 x.mv_in_msg ,
20 x.measure_in_msg ,
21 x.join_back_tbl ,
22 x.join_back_col ,
23 x.original_cost ,
24 x.rewritten_cost ,
25 x.flags ,
26 x.reserved1 ,
27 x.reserved2 ));
28 end loop;
29 return;
30 end explain_rewrite;
31 /
Function created.
rajesh@ORA10G>
To find the details, just invoke this function by passing our query
string as parameter.
rajesh@ORA10G> @printtbl ' select
query_text,rewritten_text,message,pass,join_back_tbl,mv_in_msg from table
(explain_rewrite("select
owner,count(*) from t2 group by owner
having count(*) > 100 ")) '
QUERY_TEXT
: "select owner,count(*) from
t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT : "select owner,count(*) from t2 group by owner having count(*) > 100
"
MESSAGE
: "QSM-01150: query did not rewrite"
PASS
: "NO"
JOIN_BACK_TBL
: ""
MV_IN_MSG
: ""
-----------------
QUERY_TEXT
: "select owner,count(*) from
t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT
: "select owner,count(*) from
t2 group by owner having count(*) > 100 "
MESSAGE
: "QSM-01110: query rewrite not possible
with materialized view T1_MV because it contains a join between tables (T2 and
T1) that is not present in the query and that potentially eliminates rows needed by the
query"
PASS
: "YES"
JOIN_BACK_TBL
: "T2 and T1"
MV_IN_MSG
: "T1_MV"
-----------------
PL/SQL procedure successfully completed.
rajesh@ORA10G>
From the highlighted output, optimizer suspect that certain rows are
about to be eliminated
rajesh@ORA10G> desc t2
Name Null? Type
-----------------
-------- -------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
rajesh@ORA10G>
The only case where certain rows from Child table get eliminated from
join is only by means of Orphan child records (by having NULL values in foreign
key columns), the moment we realized this and redefine a NOT NULL constraint on
foreign key columns, We are back to support query rewrites.
rajesh@ORA10G> alter
table t2 modify owner not null;
Table altered.
rajesh@ORA10G> @printtbl ' select
query_text,rewritten_text,message,pass,join_back_tbl,mv_in_msg from table
(explain_rewrite("select owner,count(*) from t2 group by owner having count(*) > 100
")) '
QUERY_TEXT
: "select owner,count(*) from
t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT
: "SELECT T1_MV.OWNER OWNER,T1_MV.COUNT(*) COUNT(*) FROM
RAJESH.T1_MV T1_MV WHERE T1_MV.GRP=1 AND T1_
MV.COUNT(*)>100"
MESSAGE
: "QSM-01151: query was rewritten"
PASS
: "NO"
JOIN_BACK_TBL
: ""
MV_IN_MSG
: ""
-----------------
QUERY_TEXT
: "select owner,count(*) from
t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT : "SELECT T1_MV.OWNER
OWNER,T1_MV.COUNT(*) COUNT(*) FROM RAJESH.T1_MV T1_MV WHERE T1_MV.GRP=1 AND T1_
MV.COUNT(*)>100"
MESSAGE
: "QSM-01033: query rewritten with
materialized view, T1_MV"
PASS
: "YES"
JOIN_BACK_TBL
: ""
MV_IN_MSG
: "T1_MV"
-----------------
QUERY_TEXT
: "select owner,count(*) from
t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT
: "select owner,count(*) from t2 group by owner having count(*) > 100
"
MESSAGE
: "QSM-01082: Joining materialized view, T1_MV, with table, T2, not
possible"
PASS
: "YES"
JOIN_BACK_TBL
: "T2"
MV_IN_MSG
: "T1_MV"
-----------------
QUERY_TEXT
: "select owner,count(*) from
t2 group by owner having count(*) > 100 "
REWRITTEN_TEXT
: "select owner,count(*) from
t2 group by owner having count(*) > 100 "
MESSAGE : "QSM-01102: materialized view,
T1_MV, requires join back to table, T2, on column, OWNER"
PASS
: "YES"
JOIN_BACK_TBL
: "T2"
MV_IN_MSG
: "T1_MV"
-----------------
PL/SQL procedure successfully completed.
rajesh@ORA10G>
rajesh@ORA10G> set autotrace traceonly explain
rajesh@ORA10G>
rajesh@ORA10G> select owner,count(*)
2 from
t2
3 group by owner
4 having count(*) > 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 2390704265
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
105 | 1365 | 3
(0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE
ACCESS FULL| T1_MV | 105 | 1365 |
3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter("T1_MV"."GRP"=1 AND
"T1_MV"."COUNT(*)">100)
rajesh@ORA10G>
rajesh@ORA10G> set autotrace off
The dreaded NOT NULL constraint strikes again!
No comments:
Post a Comment