materialized views (snapshots)...
Joachim Hammer
so far unsuccessfully tried to get Oracle to use a materialized view when
answering a query that does not explicitly mention the view. Instead, the
query processor always uses the base tables specified in the from clause.
The queries are relatively simple: they select sales data at the year level
which is pre-computed in the MV but can also be obtained by selecting the
sales data at the days level from the base tables.
I am using the "create materialized view" syntax to specify the view, the
"create dimension" syntax to tell Oracle about the dimensions as well as the
hierarchies within the dimensions. Finally, I am also enabling query rewrite
(within the create materialized view as well as with the "alter session set
query_rewrite_enabled = true" command).
Has anybody been able to the query rewrite feature to work? Any input or
suggestions are greatly appreciated.
Joachim
Thomas Kyte
umm -- an example from you showing what you've done would be helpful. MV's
work, work pretty well. Yes people are using them, we used them heavily in our
TPC-D's.
Here is a quick and dirty example that was done in 816 (borrowed from my book,
part of the chapter on MV's)
tkyte@TKYTE816> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
tkyte@TKYTE816> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
65742 rows created.
tkyte@TKYTE816> commit;
Commit complete.
tkyte@TKYTE816> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
131484 rows created.
tkyte@TKYTE816> commit;
Commit complete.
tkyte@TKYTE816> analyze table my_all_objects compute statistics;
Table analyzed.
On my system, I have the Java option installed so the MY_ALL_OBJECTS table has
about 250,000 rows in it after the above – you may have to adjust the number of
times you UNION ALL and INSERT in order to achieve the same effect. Now, we’ll
execute a query against that table that shows of the number of objects owned by
each user. Initially, this will require a full scan of the large table we have
above:
tkyte@TKYTE816> set autotrace on
tkyte@TKYTE816> set timing on
tkyte@TKYTE816> select owner, count(*) from my_all_objects group by owner;
OWNER COUNT(*)
------------------------------ ----------
A 36
B 24
CTXSYS 2220
DBSNMP 48
DEMO 60
DEMO11 36
DEMO_DDL 108
MDSYS 2112
MV_USER 60
ORDPLUGINS 312
ORDSYS 2472
OUR_TYPES 12
OUTLN 60
PERFSTAT 636
PUBLIC 117972
SCHEDULER 36
SCOTT 84
SEAPARK 36
SYS 135648
SYSTEM 624
TESTING 276
TKYTE 12
TTS_USER 48
TYPES 36
24 rows selected.
Elapsed: 00:00:03.35
tkyte@TKYTE816> set timing off
tkyte@TKYTE816> set autotrace traceonly
tkyte@TKYTE816> select owner, count(*) from my_all_objects group by owner;
24 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2525 Card=24 Bytes=120)
1 0 SORT (GROUP BY) (Cost=2525 Card=24 Bytes=120)
2 1 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS' (Cost=547 Card=262968
Statistics
----------------------------------------------------------
0 recursive calls
27 db block gets
3608 consistent gets
3516 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
In order to get the aggregate count, we must count 250,000 plus records on over
3,600 blocks. Unfortunately, in our system, we ask this question frequently –
dozens of times every day. We are scanning almost 30meg of data. We could avoid
counting the details each and every time by creating a materialized view of the
data. The following demonstrates the basic steps needed to perform this
operation. We’ll discuss the GRANT and ALTER statements in the “How This Works”
section in more detail. In addition to the grants below, you might need the
CREATE MATERIALIZED VIEW privilege as well, depending on what roles you have
been granted and have enabled:
tkyte@TKYTE816> grant query rewrite to tkyte;
Grant succeeded.
tkyte@TKYTE816> alter session set query_rewrite_enabled=true;
Session altered.
tkyte@TKYTE816> alter session set query_rewrite_integrity=enforced;
Session altered.
tkyte@TKYTE816> create materialized view my_all_objects_aggs
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner, count(*)
7 from my_all_objects
8 group by owner
9 /
Materialized view created.
tkyte@TKYTE816> analyze table my_all_objects_aggs compute statistics;
Table analyzed.
Basically, what we've done is pre-calculate the object count and define this
summary information as a materialized view. We have asked that the view be
immediately built and populated with data. You'll note that we have also
specified REFRESH ON COMMIT and ENABLE QUERY REWRITE, but more on these in a
moment. Also notice that we may have created a materialized view – but when we
ANALYZE, we are analyzing a table. A materialized view creates a real table and
that table may be indexed, analyzed, and so on.
First, let's see the view in action by issuing the same query again (which is
also the query that we used to define the view itself):
tkyte@TKYTE816> set timing on
tkyte@TKYTE816> select owner, count(*)
2 from my_all_objects
3 group by owner;
OWNER COUNT(*)
------------------------------ ----------
A 36
B 24
…
TYPES 36
24 rows selected.
Elapsed: 00:00:00.10
tkyte@TKYTE816> set timing off
tkyte@TKYTE816> set autotrace traceonly
tkyte@TKYTE816> select owner, count(*)
2 from my_all_objects
3 group by owner;
24 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=24 Bytes=216)
1 0 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=1 Card=
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
7 consistent gets
0 physical reads
0 redo size
1483 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
tkyte@TKYTE816> set autotrace off
From 3,600 plus consistent gets (logical I/Os) to 12. No physical IO this time
around as the data was found in the cache – our buffer cache will be much more
efficient now as it has less to cache -- I could not even begin to cache the
previous queries working set, now I can. Notice how our query plan shows we are
now doing a full scan of the MY_ALL_OBJECTS_AGGS table, even though we queried
the detail table MY_ALL_OBJECTS. When the 'select owner, count(*)…' query is
issued, the database automatically directs it at our materialized view.
Let's take this a step further by adding a new row to the MY_ALL_OBJECTS table
and committing the change:
tkyte@TKYTE816> insert into my_all_objects
2 ( owner, object_name, object_type, object_id )
3 values
4 ( 'New Owner', 'New Name', 'New Type', 1111111 );
1 row created.
tkyte@TKYTE816> commit;
Commit complete.
Now, we issue effectively the same query again, but this time we're just looking
at our newly inserted row:
tkyte@TKYTE816> set timing on
tkyte@TKYTE816> select owner, count(*)
2 from my_all_objects
3 where owner = 'New Owner'
4 group by owner;
OWNER COUNT(*)
------------------------------ ----------
New Owner 1
Elapsed: 00:00:00.01
tkyte@TKYTE816> set timing off
tkyte@TKYTE816> set autotrace traceonly
tkyte@TKYTE816> select owner, count(*)
2 from my_all_objects
3 where owner = 'New Owner'
4 group by owner;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
1 0 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=1 Card=
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
6 consistent gets
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
tkyte@TKYTE816> set autotrace off
The analysis shows that we scanned the materialized view and found the new row.
By specifying REFRESH ON COMMIT in our original definition of the view, we
requested that Oracle maintain synchronization between the view and the details
– when we update the details, the summary will be maintained as well. It cannot
maintain synchronization in every case of an arbitrary materialized view – but
in the case of a single table summary (as we have) or joins with no aggregation,
it can.
Now, one last query:
tkyte@TKYTE816> set timing on
tkyte@TKYTE816> select count(*)
2 from my_all_objects
3 where owner = 'New Owner';
COUNT(*)
----------
1
Elapsed: 00:00:00.00
tkyte@TKYTE816> set timing off
tkyte@TKYTE816> set autotrace traceonly
tkyte@TKYTE816> select count(*)
2 from my_all_objects
3 where owner = 'New Owner';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'MY_ALL_OBJECTS_AGGS' (Cost=1 Car
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
5 consistent gets
0 physical reads
0 redo size
367 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
tkyte@TKYTE816> set autotrace off
We can see that Oracle is smart enough to use the view even when the query
appears to be slightly different. There was no GROUP BY clause here, yet the
database recognized that the materialized view could still be used. This is what
makes materialized views 'magical'. The end users do not have to be aware of
these summary tables, the database will realize for us that the answer already
exists and, as long as we enable query re-write (which we did), will
automatically rewrite the query to use them. This feature allows you to
immediately impact existing applications without changing a single query.
--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
Peter L
<jha...@cise.ufl.edu> wrote:
>
>Has anybody been able to the query rewrite feature to work? Any input or
>suggestions are greatly appreciated.
>
We didn't have any problems getting queries to rewrite. Have your
views gone stale? You can checkin user_mviews.staleness. If they are
stale, after dml on the underlying table, rewrite won't take place
unless you have set query_rewrite_integrity (?) to stale tolerated.
Also have you analyzed your mviews?