Materialized Views - Query Rewrite Not Working 
Author Message
 Materialized Views - Query Rewrite Not Working
Hello,

I have two tables and each has a materialized view. If I select an
aggregated value from either table, it's materialized view is used and query
rewrite works fine.

These tables are of similar structure and I also have a view sitting on top
of them, which "union all"s them together.

If I select the same aggregated value from this view, there is no query
rewrite.

Any ideas?

Cheers,
AB.



Mon, 21 Feb 2005 13:23:36 GMT
 Materialized Views - Query Rewrite Not Working

There was no query rewrite, because the optimizer is not yet smart
enough to see through it. Of course you can always create another MV
that calculates aggregates on the UNION view of the two tables, query
rewrite will kick in after that.

SQL> select * from mvqr1;

        C1 C2         C3
---------- -- ----------
         1 a           1
         2 a           2
         3 a           3
         4 b           1
         5 b           2
         6 c           1
         7 c           7
         8 c           3

8 rows selected.

SQL> select * from mvqr2;

        C1 C2         C3
---------- -- ----------
         1 a          11
         2 a          12
         3 a          13
         4 b          11
         5 b          12
         6 c          11
         7 c          17
         8 c          13

8 rows selected.

SQL> create or replace view mv_view as select * from mvqr1 union all
select * from mvqr2;

View created.

SQL> create materialized view mv_mvqr1
  2  build immediate
  3  enable query rewrite
  4  as select c2, sum(c3) from mvqr1 group by c2;

Materialized view created.

SQL>
SQL> create materialized view mv_mvqr2
  2  build immediate
  3  enable query rewrite
  4  as select c2, sum(c3) from mvqr2 group by c2;

Materialized view created.

-- Query rewrite
SQL> set autotrace on
SQL> select c2, sum(c3) from mvqr1 group by c2;

C2    SUM(C3)
-- ----------
a           6
b           3
c          11

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=523
          2)

   1    0   TABLE ACCESS (FULL) OF 'MV_MVQR1' (Cost=2 Card=327 Bytes=5
          232)

-- NO query rewrite
SQL> select c2, sum(c3) from mv_view group by c2;

C2    SUM(C3)
-- ----------
a          42
b          26
c          52

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=26)
   1    0   SORT (GROUP BY) (Cost=4 Card=13 Bytes=26)
   2    1     VIEW OF 'MV_VIEW' (Cost=2 Card=335 Bytes=670)
   3    2       UNION-ALL (PARTITION)
   4    3         TABLE ACCESS (FULL) OF 'MVQR1' (Cost=2 Card=8 Bytes=
          24)

   5    3         TABLE ACCESS (FULL) OF 'MVQR2' (Cost=2 Card=327 Byte
          s=5232)

SQL> set autotrace off
SQL> create materialized view mv_mvqr3
  2  build immediate
  3  enable query rewrite
  4  as select c2, sum(c3) from mv_view group by c2;

Materialized view created.

-- Query rewrite
SQL> set autotrace on
SQL> select c2, sum(c3) from mv_view group by c2;

C2    SUM(C3)
-- ----------
a          42
b          26
c          52

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=523
          2)

   1    0   TABLE ACCESS (FULL) OF 'MV_MVQR3' (Cost=2 Card=327 Bytes=5
          232)

Quote:

> Hello,

> I have two tables and each has a materialized view. If I select an
> aggregated value from either table, it's materialized view is used and query
> rewrite works fine.

> These tables are of similar structure and I also have a view sitting on top
> of them, which "union all"s them together.

> If I select the same aggregated value from this view, there is no query
> rewrite.

> Any ideas?

> Cheers,
> AB.



Tue, 22 Feb 2005 06:19:09 GMT
 Materialized Views - Query Rewrite Not Working
Thanks, I'll give this a go.


Quote:
> There was no query rewrite, because the optimizer is not yet smart
> enough to see through it. Of course you can always create another MV
> that calculates aggregates on the UNION view of the two tables, query
> rewrite will kick in after that.

> SQL> select * from mvqr1;

>         C1 C2         C3
> ---------- -- ----------
>          1 a           1
>          2 a           2
>          3 a           3
>          4 b           1
>          5 b           2
>          6 c           1
>          7 c           7
>          8 c           3

> 8 rows selected.

> SQL> select * from mvqr2;

>         C1 C2         C3
> ---------- -- ----------
>          1 a          11
>          2 a          12
>          3 a          13
>          4 b          11
>          5 b          12
>          6 c          11
>          7 c          17
>          8 c          13

> 8 rows selected.

> SQL> create or replace view mv_view as select * from mvqr1 union all
> select * from mvqr2;

> View created.

> SQL> create materialized view mv_mvqr1
>   2  build immediate
>   3  enable query rewrite
>   4  as select c2, sum(c3) from mvqr1 group by c2;

> Materialized view created.

> SQL>
> SQL> create materialized view mv_mvqr2
>   2  build immediate
>   3  enable query rewrite
>   4  as select c2, sum(c3) from mvqr2 group by c2;

> Materialized view created.

> -- Query rewrite
> SQL> set autotrace on
> SQL> select c2, sum(c3) from mvqr1 group by c2;

> C2    SUM(C3)
> -- ----------
> a           6
> b           3
> c          11

> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=523
>           2)

>    1    0   TABLE ACCESS (FULL) OF 'MV_MVQR1' (Cost=2 Card=327 Bytes=5
>           232)

> -- NO query rewrite
> SQL> select c2, sum(c3) from mv_view group by c2;

> C2    SUM(C3)
> -- ----------
> a          42
> b          26
> c          52

> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=26)
>    1    0   SORT (GROUP BY) (Cost=4 Card=13 Bytes=26)
>    2    1     VIEW OF 'MV_VIEW' (Cost=2 Card=335 Bytes=670)
>    3    2       UNION-ALL (PARTITION)
>    4    3         TABLE ACCESS (FULL) OF 'MVQR1' (Cost=2 Card=8 Bytes=
>           24)

>    5    3         TABLE ACCESS (FULL) OF 'MVQR2' (Cost=2 Card=327 Byte
>           s=5232)

> SQL> set autotrace off
> SQL> create materialized view mv_mvqr3
>   2  build immediate
>   3  enable query rewrite
>   4  as select c2, sum(c3) from mv_view group by c2;

> Materialized view created.

> -- Query rewrite
> SQL> set autotrace on
> SQL> select c2, sum(c3) from mv_view group by c2;

> C2    SUM(C3)
> -- ----------
> a          42
> b          26
> c          52

> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=523
>           2)

>    1    0   TABLE ACCESS (FULL) OF 'MV_MVQR3' (Cost=2 Card=327 Bytes=5
>           232)




- Show quoted text -

Quote:
> > Hello,

> > I have two tables and each has a materialized view. If I select an
> > aggregated value from either table, it's materialized view is used and
query
> > rewrite works fine.

> > These tables are of similar structure and I also have a view sitting on
top
> > of them, which "union all"s them together.

> > If I select the same aggregated value from this view, there is no query
> > rewrite.

> > Any ideas?

> > Cheers,
> > AB.



Tue, 22 Feb 2005 13:41:27 GMT
 Materialized Views - Query Rewrite Not Working
Hello,

Yes, this suggestion does work.

Unfortunately, it does not allow fast refresh. These table contain several
billion rows so fast refresh is a must.

Thanks for the suggestion.
AB.


Quote:
> There was no query rewrite, because the optimizer is not yet smart
> enough to see through it. Of course you can always create another MV
> that calculates aggregates on the UNION view of the two tables, query
> rewrite will kick in after that.

> SQL> select * from mvqr1;

>         C1 C2         C3
> ---------- -- ----------
>          1 a           1
>          2 a           2
>          3 a           3
>          4 b           1
>          5 b           2
>          6 c           1
>          7 c           7
>          8 c           3

> 8 rows selected.

> SQL> select * from mvqr2;

>         C1 C2         C3
> ---------- -- ----------
>          1 a          11
>          2 a          12
>          3 a          13
>          4 b          11
>          5 b          12
>          6 c          11
>          7 c          17
>          8 c          13

> 8 rows selected.

> SQL> create or replace view mv_view as select * from mvqr1 union all
> select * from mvqr2;

> View created.

> SQL> create materialized view mv_mvqr1
>   2  build immediate
>   3  enable query rewrite
>   4  as select c2, sum(c3) from mvqr1 group by c2;

> Materialized view created.

> SQL>
> SQL> create materialized view mv_mvqr2
>   2  build immediate
>   3  enable query rewrite
>   4  as select c2, sum(c3) from mvqr2 group by c2;

> Materialized view created.

> -- Query rewrite
> SQL> set autotrace on
> SQL> select c2, sum(c3) from mvqr1 group by c2;

> C2    SUM(C3)
> -- ----------
> a           6
> b           3
> c          11

> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=523
>           2)

>    1    0   TABLE ACCESS (FULL) OF 'MV_MVQR1' (Cost=2 Card=327 Bytes=5
>           232)

> -- NO query rewrite
> SQL> select c2, sum(c3) from mv_view group by c2;

> C2    SUM(C3)
> -- ----------
> a          42
> b          26
> c          52

> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=26)
>    1    0   SORT (GROUP BY) (Cost=4 Card=13 Bytes=26)
>    2    1     VIEW OF 'MV_VIEW' (Cost=2 Card=335 Bytes=670)
>    3    2       UNION-ALL (PARTITION)
>    4    3         TABLE ACCESS (FULL) OF 'MVQR1' (Cost=2 Card=8 Bytes=
>           24)

>    5    3         TABLE ACCESS (FULL) OF 'MVQR2' (Cost=2 Card=327 Byte
>           s=5232)

> SQL> set autotrace off
> SQL> create materialized view mv_mvqr3
>   2  build immediate
>   3  enable query rewrite
>   4  as select c2, sum(c3) from mv_view group by c2;

> Materialized view created.

> -- Query rewrite
> SQL> set autotrace on
> SQL> select c2, sum(c3) from mv_view group by c2;

> C2    SUM(C3)
> -- ----------
> a          42
> b          26
> c          52

> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=523
>           2)

>    1    0   TABLE ACCESS (FULL) OF 'MV_MVQR3' (Cost=2 Card=327 Bytes=5
>           232)




- Show quoted text -

Quote:
> > Hello,

> > I have two tables and each has a materialized view. If I select an
> > aggregated value from either table, it's materialized view is used and
query
> > rewrite works fine.

> > These tables are of similar structure and I also have a view sitting on
top
> > of them, which "union all"s them together.

> > If I select the same aggregated value from this view, there is no query
> > rewrite.

> > Any ideas?

> > Cheers,
> > AB.



Wed, 23 Feb 2005 15:07:14 GMT
 Materialized Views - Query Rewrite Not Working
Well, maybe something else would work for you.

1. Build a materialized view that unions the 2 individual MVs on the 2
tables. Again you are using UNION ALL so you have to do a complete
refresh. But since 2 two MVs are aggregated views of the based table
and should be relatively small, a complete refresh of this MV maybe
OK. You then, instead of query against the union view on the two
tables, do a simple query against this MV and forget about all the
QUERY REWRITE stuff.

2. If you insist on querying against the union view and want to see
the QUERY REWRITE in action, try this.
a. Build an union MV based on the 2 individual MVs as stated above.
b. Build a table and populate it with aggreagted data from the union
MV.
c. Build an MV with prebuilt table clause based on the table from 2.
d. Run your query against the 2-table union view normally.
e. Schedule a job to refresh data in the prebuilt data by pulling data
from the union MV.

The aggregated data is all there in the prebuilt table. It just
depends on whether the CBO is smart enough to use it. If it QUERY
REWRITE still doe snot kick in, use the /*+ REWRITE */ hint in your
query. It has to work.

Quote:

> Hello,

> Yes, this suggestion does work.

> Unfortunately, it does not allow fast refresh. These table contain several
> billion rows so fast refresh is a must.

> Thanks for the suggestion.
> AB.



Thu, 24 Feb 2005 02:34:11 GMT
 Materialized Views - Query Rewrite Not Working
Unfortunately, I did not mention that I was on 8i.

You can not have query rewrite with a "union all" MV, it's a 9i thing.


Quote:
> Well, maybe something else would work for you.

> 1. Build a materialized view that unions the 2 individual MVs on the 2
> tables. Again you are using UNION ALL so you have to do a complete
> refresh. But since 2 two MVs are aggregated views of the based table
> and should be relatively small, a complete refresh of this MV maybe
> OK. You then, instead of query against the union view on the two
> tables, do a simple query against this MV and forget about all the
> QUERY REWRITE stuff.

> 2. If you insist on querying against the union view and want to see
> the QUERY REWRITE in action, try this.
> a. Build an union MV based on the 2 individual MVs as stated above.
> b. Build a table and populate it with aggreagted data from the union
> MV.
> c. Build an MV with prebuilt table clause based on the table from 2.
> d. Run your query against the 2-table union view normally.
> e. Schedule a job to refresh data in the prebuilt data by pulling data
> from the union MV.

> The aggregated data is all there in the prebuilt table. It just
> depends on whether the CBO is smart enough to use it. If it QUERY
> REWRITE still doe snot kick in, use the /*+ REWRITE */ hint in your
> query. It has to work.




- Show quoted text -

Quote:
> > Hello,

> > Yes, this suggestion does work.

> > Unfortunately, it does not allow fast refresh. These table contain
several
> > billion rows so fast refresh is a must.

> > Thanks for the suggestion.
> > AB.



Thu, 03 Mar 2005 14:09:21 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Query rewrite doesn't work on materialized view

2. Use materialized view's query rewrite

3. joinback in query rewrite, Oracle Materialized Views

4. Can the optimizer rewrite a query using materialized views

5. Materialized view auto refresh not working

6. Materialized View fast refresh is not fast

7. materialized views not exported

8. materialized view not refreshing

9. Materialized view not refreshing

10. ORA-12053: this is not a valid nested materialized view

11. query rewrite on normal views

12. Query on View does not work


 
Powered by phpBB® Forum Software