Re: Materialized View to Subset Data, Is This Possible

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 21 Jan 2003 21:54:31 -0800
Message-ID: <130ba93a.0301212154.ceeb7d4@posting.google.com>

Don't quite understand your question. Is this what you are asking:

When the query is run, will query rewrite be activated and the materialized view MV_TEST used (even though an additional condition is added to the where clause)?

If this is the question, then the answer is yes, in 9i at least. An example:

SQL> select * from tyu;

C1 C2 C3
-- --- ----------

G  XX           1
O  XX           1
Q  XX           1
a  XX           2
b  XX           2
c  XX           2

d ZZ
e ZZ
f ZZ
g YY
h YY

11 rows selected.

SQL> drop materialized view tyu_mv;

Materialized view dropped.

SQL> create materialized view tyu_mv
  2 build immediate
  3 enable query rewrite
  4 as select * from tyu where c2='XX';

Materialized view created.

SQL> set autotrace on
SQL> select * from tyu where c2='XX' and c3=1;

C1 C2 C3
-- --- ----------

G  XX           1
O  XX           1
Q  XX           1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=57)    1 0 TABLE ACCESS (FULL) OF 'TYU_MV' (Cost=2 Card=3 Bytes=57)

As you can see, the materilaized view tyu_mv is used even though the table tyu is specified in the query.

• Jusung Yang

nick_wakefield_at_hotmail.com (Niko) wrote in message news:<9da94cd1.0301210955.343e7747_at_posting.google.com>...
> Materialized Views to Subset Data
>
> Is it possible to create a materialized view to pre-subset the data
> and query rewrite to use this.
>
> e.g
> CREATE MATERIALIZED VIEW MV_TEST
> REFRESH FAST ON COMMIT
> ENABLE QUERY REWRITE
> AS
> select * from sales where region = 'WEST'
>
> Then if I run the following query
>
> select * from sales where region = 'WEST' and PRODUCT = 'TIRES'
>
> The materilized view will use the subset.
>
> If not is this possible in 9i.
Received on Tue Jan 21 2003 - 23:54:31 CST