| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: materialized views (snapshots)...
In article <9jq453$2idr$1_at_oak.cise.ufl.edu>, "Joachim says...
>
>We are using Oracle Enterprise Edition 8.1.6 on Solaris and Win 2K. I have
>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
>
>
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_at_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_at_TKYTE816> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
65742 rows created.
tkyte_at_TKYTE816> commit;
Commit complete.
tkyte_at_TKYTE816> insert /*+ APPEND */ into my_all_objects
2 select * from my_all_objects;
131484 rows created.
tkyte_at_TKYTE816> commit;
Commit complete.
tkyte_at_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_at_TKYTE816> set autotrace on tkyte_at_TKYTE816> set timing on tkyte_at_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_at_TKYTE816> set timing off tkyte_at_TKYTE816> set autotrace traceonly tkyte_at_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_at_TKYTE816> grant query rewrite to tkyte; Grant succeeded.
tkyte_at_TKYTE816> alter session set query_rewrite_enabled=true; Session altered.
tkyte_at_TKYTE816> alter session set query_rewrite_integrity=enforced; Session altered.
tkyte_at_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_at_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_at_TKYTE816> set timing on
tkyte_at_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_at_TKYTE816> set timing off
tkyte_at_TKYTE816> set autotrace traceonly
tkyte_at_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_at_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_at_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_at_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_at_TKYTE816> set timing on
tkyte_at_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_at_TKYTE816> set timing off
tkyte_at_TKYTE816> set autotrace traceonly
tkyte_at_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_at_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_at_TKYTE816> set timing on
tkyte_at_TKYTE816> select count(*)
2 from my_all_objects
3 where owner = 'New Owner';
COUNT(*)
1
Elapsed: 00:00:00.00
tkyte_at_TKYTE816> set timing off
tkyte_at_TKYTE816> set autotrace traceonly
tkyte_at_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_at_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 (tkyte@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 CorpReceived on Thu Jul 26 2001 - 18:59:33 CDT
 |
 |