One of the things I love about working with Oracle Database
technologies is that there’s just so much one can learn. I make it an active
goal of mine to try to learn something new at work each and every day, no
matter how big or small.
One of many good reason to use ANSI join syntax over an Oracle join
syntax is that it supports a clear distinction between join condition and
filter condition in the SQL statement.
However there is a strange behavior with Oracle database that
Materialized view designed for Query rewrite purpose should be defined using
Oracle join syntax only, otherwise query rewrites won’t work properly.
To illustrate this will create materialized view (one with ANSI and one
with Oracle join syntax) and execute different queries that should be able to
use Query rewrites.
Let’s create a couple of dimension and fact tables for a demo.
demo@ORA12C>
create table products as
2
select rownum as prod_id,
3
object_name as prod_name,
4
object_type as prod_category,
5
object_id as prod_category_id,
6
data_object_id as prod_subcategory_id,
7
round(dbms_random.value(1,2000)) as prod_min_price,
8
round(dbms_random.value(1,4000)) as prod_max_price,
9
'Total' as prod_total,
10
to_date('01/01/2010','mm/dd/yyyy')+rownum as effective_from,
11
add_months(to_date('01/01/2010','mm/dd/yyyy')+rownum,3) as effective_to,
12
status as prod_status
13 from
all_objects ;
Table created.
demo@ORA12C>
alter table products
2 add
constraint prod_pk
3
primary key(prod_id);
Table altered.
demo@ORA12C>
create table customers as
2
select rownum as cust_id,
3
object_name as cust_fname,
4
subobject_name as cust_lname,
5
decode(mod(rownum,2),0,'M','F') gender,
6
to_char(created,'YYYY') as cust_birth_year,
7
'Single' as cust_marital_status,
8
dbms_random.string('A',30) as cust_street_address,
9
dbms_random.value(1,10000) as cust_postal_code,
10
dbms_random.string('E',5) as cust_city,
11
dbms_random.string('C',5) as cust_state_province,
12
round(dbms_random.value(1,9999))||'-'||
13
round(dbms_random.value(1,999))||'-'||
14
round(dbms_random.value(1,999)) as cust_phone_no,
15
object_name||'@company.com' as cust_email,
16
'CustomerTotal' as cust_total,
17
to_date('01/01/2010','mm/dd/yyyy')+rownum as cust_effective_from,
18
add_months(to_date('01/01/2010','mm/dd/yyyy')+rownum,3) as
cust_effective_to
19 from
all_objects ;
Table created.
demo@ORA12C>
alter table customers
2 add
constraint cust_pk
3
primary key(cust_id);
Table altered.
demo@ORA12C>
create table sales as
2
select mod(rownum,10000)+1 as prod_id,
3
mod(rownum,1000)+1 as cust_id,
4
to_date('01-Jan-1998','dd-mon-yyyy')
5
+mod(rownum,4000) as time_id,
6
mod(rownum,10) as quantity_sold,
7
round(dbms_random.value(1,5000)) as amount_sold
8 from
big_table;
Table created.
demo@ORA12C>
alter table sales
2 add
constraint fk_sales_prod
3
foreign key(prod_id)
4
references products
5
modify prod_id not null;
Table altered.
demo@ORA12C>
alter table sales
2 add
constraint fk_sales_cust
3
foreign key(cust_id)
4
references customers
5
modify cust_id not null;
Table altered.
demo@ORA12C>
Build a materialized view using the following statement with ANSI join
syntax.
demo@ORA12C>
create materialized view sales_prod_cust_mv
2
build immediate
3
refresh on demand
4
enable query rewrite
5 as
6
select p.prod_id,p.prod_name,
7
c.cust_id,
8
sum(s.quantity_sold) as total_qty,
9
sum(s.amount_sold) as total_amt,
10
count(*) cnt1,
11
count(s.quantity_sold) cnt2,
12
count(s.amount_sold) cnt3
13 from
sales s
14 join
products p
15
on (s.prod_id = p.prod_id)
16 join
customers c
17
on (s.cust_id = c.cust_id)
18
group by p.prod_id,p.prod_name,c.cust_id;
Materialized
view created.
demo@ORA12C>
exec dbms_stats.gather_table_stats(user,'sales_prod_cust_mv');
PL/SQL
procedure successfully completed.
Now, run several queries, all of them written with ANSI join syntax.
The first query looks very similar to definition of materialized view, but
doesn’t contain all attributes – in this case query rewrite with partial text
match should be possible.
demo@ORA12C>
set autotrace traceonly explain
demo@ORA12C>
select p.prod_id,p.prod_name,
2
c.cust_id,
3
sum(s.quantity_sold) as total_qty,
4
sum(s.amount_sold) as total_amt
5 from
sales s
6 join
products p
7
on (s.prod_id = p.prod_id)
8 join
customers c
9
on (s.cust_id = c.cust_id)
10
group by p.prod_id,p.prod_name,c.cust_id;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2068648405
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19
(22)|
| 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV |
10000 | 19 (22)|
-------------------------------------------------------------------------------
The second query contains the additional attribute of the product
dimension, in this case, a general query rewrite with a join back on dimension
table should occur – unfortunately that is not the case, the explain plan shows
that Materialized view is not used for this query.
demo@ORA12C>
select p.prod_id,
2 p.prod_name,
3
p.prod_category,
4
sum(s.quantity_sold) as total_qty
5 from
sales s
6 join
products p
7
on (s.prod_id = p.prod_id)
8 join
customers c
9
on (s.cust_id = c.cust_id)
10
group by p.prod_id,
11
p.prod_name,
12
p.prod_category ;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2414848021
--------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 10000 | 4117 (45)| 00:00:01 |
| 1 |
HASH GROUP BY | | 10000 | 4117
(45)| 00:00:01 |
|* 2 |
HASH JOIN | | 10000 | 3942
(47)| 00:00:01 |
| 3 |
VIEW | VW_GBC_5 |
10000 | 3712 (48)| 00:00:01 |
| 4 |
HASH GROUP BY | | 10000 | 3712
(48)| 00:00:01 |
| 5 |
TABLE ACCESS FULL| SALES | 1000K|
1083 (34)| 00:00:01 |
| 6 |
TABLE ACCESS FULL | PRODUCTS |
72370 | 216 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
access("ITEM_1"="P"."PROD_ID")
The third query joins only one fact and one dimension table, but not
the CUSTOMERS table. In this case a materialized view delta join should be
performed. Because of lossless join to CUSTOMERS dimension, it is possible for
a query rewrite, even if that dimension table is not part of that query.
But the execution plan shows something else, again the materialized
view is not getting used.
demo@ORA12C>
select p.prod_name,
2
sum(s.quantity_sold)
3 from
products p
4 join
sales s
5 on (
s.prod_id = p.prod_id )
6
group by p.prod_name;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2414848021
--------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 10000 | 4093 (45)| 00:00:01 |
| 1 |
HASH GROUP BY | | 10000 | 4093
(45)| 00:00:01 |
|* 2 |
HASH JOIN | | 10000 | 3940
(47)| 00:00:01 |
| 3 |
VIEW | VW_GBC_5 |
10000 | 3712 (48)| 00:00:01 |
| 4 |
HASH GROUP BY | | 10000 | 3712
(48)| 00:00:01 |
| 5 |
TABLE ACCESS FULL| SALES | 1000K|
1083 (34)| 00:00:01 |
| 6 |
TABLE ACCESS FULL | PRODUCTS |
72370 | 213 (14)| 00:00:01 |
--------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
access("ITEM_1"="P"."PROD_ID")
Now let’s drop and rebuild the materialized view with Oracle join
syntax.
demo@ORA12C>
drop materialized view sales_prod_cust_mv;
Materialized
view dropped.
demo@ORA12C>
create materialized view sales_prod_cust_mv
2
build immediate
3
refresh on demand
4
enable query rewrite
5 as
6
select p.prod_id,p.prod_name,
7
c.cust_id,
8
sum(s.quantity_sold) as total_qty,
9
sum(s.amount_sold) as total_amt,
10
count(*) cnt1,
11
count(s.quantity_sold) cnt2,
12
count(s.amount_sold) cnt3
13 from
sales s , products p ,customers c
14
where s.prod_id = p.prod_id
15 and
s.cust_id = c.cust_id
16
group by p.prod_id,p.prod_name,c.cust_id;
Materialized
view created.
demo@ORA12C>
exec dbms_stats.gather_table_stats(user,'sales_prod_cust_mv');
PL/SQL
procedure successfully completed.
demo@ORA12C>
Now execute the queries again (both with ANSI and Oracle join syntax).
All queries were rewritten as expected.
demo@ORA12C>
set autotrace traceonly explain
demo@ORA12C>
select p.prod_id,p.prod_name,
2
c.cust_id,
3
sum(s.quantity_sold) as total_qty,
4
sum(s.amount_sold) as total_amt
5 from
sales s
6 join
products p
7
on (s.prod_id = p.prod_id)
8 join
customers c
9
on (s.cust_id = c.cust_id)
10
group by p.prod_id,p.prod_name,c.cust_id;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2068648405
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19
(22)|
| 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV |
10000 | 19 (22)|
--------------------------------------------------------------------------------
demo@ORA12C>
select p.prod_id,p.prod_name,
2
c.cust_id,
3
sum(s.quantity_sold) as total_qty,
4
sum(s.amount_sold) as total_amt
5 from
sales s , products p , customers c
6
where s.cust_id = c.cust_id
7 and
s.prod_id = p.prod_id
8
group by p.prod_id,p.prod_name,c.cust_id;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2068648405
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 19
(22)|
| 1 | MAT_VIEW REWRITE ACCESS FULL|
SALES_PROD_CUST_MV | 10000 |
19 (22)|
--------------------------------------------------------------------------------
demo@ORA12C>
select p.prod_id,
2
p.prod_name,
3
p.prod_category,
4
sum(s.quantity_sold) as total_qty
5 from
sales s
6 join
products p
7
on (s.prod_id = p.prod_id)
8 join
customers c
9
on (s.cust_id = c.cust_id)
10
group by p.prod_id,
11
p.prod_name,
12
p.prod_category ;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2235290080
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 402
(16)|
| 1 |
HASH GROUP BY
| | 10000
| 402
(16)|
|* 2 |
HASH JOIN
| | 10000 | 249
(21)|
| 3 |
MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV
| 10000 | 19 (22)|
| 4 |
TABLE ACCESS FULL |
PRODUCTS | 72370 | 216
(15)|
----------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
access("P"."PROD_ID"="SALES_PROD_CUST_MV"."PROD_ID")
Note
-----
- this is an adaptive plan
demo@ORA12C>
select p.prod_id,
2
p.prod_name,
3
p.prod_category,
4
sum(s.quantity_sold) as total_qty
5 from
sales s, products p, customers c
6
where s.prod_id = p.prod_id
7 and
s.cust_id = c.cust_id
8
group by p.prod_id,
9
p.prod_name,
10
p.prod_category ;
Execution Plan
----------------------------------------------------------
Plan hash
value: 2235290080
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 402
(16)|
| 1 |
HASH GROUP BY | | 10000 | 402
(16)|
|* 2 |
HASH JOIN
| | 10000
| 249
(21)|
| 3 |
MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV
| 10000 | 19 (22)|
| 4 |
TABLE ACCESS FULL | PRODUCTS | 72370 | 216
(15)|
----------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="SALES_PROD_CUST_MV"."PROD_ID")
Note
-----
- this is an adaptive plan
demo@ORA12C>
select p.prod_name,
2
sum(s.quantity_sold)
3 from
products p
4 join
sales s
5 on (
s.prod_id = p.prod_id )
6
group by p.prod_name;
Execution Plan
----------------------------------------------------------
Plan hash
value: 370084824
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7976 |
90 (18)|
| 1 |
HASH GROUP BY
| | 7976 |
90 (18)|
| 2 | MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV |
10000 | 19 (22)|
---------------------------------------------------------------------------------
demo@ORA12C>
select p.prod_name,
2
sum(s.quantity_sold)
3 from
products p , sales s
4 where
s.prod_id = p.prod_id
5
group by p.prod_name;
Execution Plan
----------------------------------------------------------
Plan hash
value: 370084824
---------------------------------------------------------------------------------
| Id | Operation
| Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7976 |
90 (18)|
| 1 |
HASH GROUP BY | | 7976 |
90 (18)|
| 2 | MAT_VIEW REWRITE ACCESS FULL| SALES_PROD_CUST_MV |
10000 | 19 (22)|
---------------------------------------------------------------------------------
demo@ORA12C>
set autotrace off
demo@ORA12C>
As you can see in this example, the join syntax used in the
Materialized view definition has an impact on the capabilities of query
rewrite.
If materialized view is written with ANSI join syntax, only full and
partial text matching is possible, but all the advanced query rewrite mechanism
do not work.
If materialized view is written with Oracle join syntax, then all types
of query rewrites works as expected.
So if materialized view is designed for Query rewrite purpose, stick
with Oracle join syntax only rather than ANSI join syntax.
No comments:
Post a Comment