Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Materialized View to Subset Data, Is This Possible

Re: Materialized View to Subset Data, Is This Possible

From: Niko <nick_wakefield_at_hotmail.com>
Date: 22 Jan 2003 10:15:06 -0800
Message-ID: <9da94cd1.0301221015.69c5d179@posting.google.com>


That basically was my question.

I have a table with 100 million rows which cannot be partitioned by the failed flag. The failed flag has a bitmap index but only 10% of the events are failed.

The table is a partitioned and sub-partitioned by other fields for several good reasons.

When I run a query like

select * from events_fact where failed = 1

The execution time is too long, I am required to execute my query in 5 seconds, and I wanted to create a materialized view to hold this subset. Then create othyer indexes and a different partitioning strategy which would fit the queries better.

Now in 8i I tried this and query rewrite was not happening so I was wondering if 9i solved this.

It looks like it does. :-)

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0301212154.ceeb7d4_at_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 Wed Jan 22 2003 - 12:15:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US