Aggregate IOT fast refresh materialized view
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 27 May 2004 01:33:24 +0100 (BST)
Hi all,
Has anyone implemented a fast refresh single table aggregate materialized view
which is stored as
IOT? Don't everyone rush in at once... :-)
The v9 docs suggests its not possible, yet we've been testing with no problems.
Just curious if
anyone actually has such a thing in production. OSS are quoting the
documentation as "not
possible" being equivalent to "not supported".
Example (from 9204)
SQL> create table my_table
2 as select * From all_objects
3 where rownum < 1000;
Table created.
SQL> alter table my_table add primary key ( owner, object_id);
Table altered.
SQL> CREATE MATERIALIZED VIEW LOG ON my_table
2 WITH SEQUENCE, ROWID (owner, object_id) INCLUDING NEW VALUES;
Materialized view log created.
SQL> create table my_iot_mv ( owner, child_cnt, constraint my_iot_pk primary
key ( owner) )
2 organization index as
3 SELECT owner,
4 count(*) child_cnt
5 from my_table
6 group by owner;
Table created.
SQL> CREATE MATERIALIZED VIEW my_iot_mv
2 on prebuilt table
3 REFRESH FAST ON COMMIT
4 disable QUERY REWRITE
5 AS
6 SELECT owner,
7 count(*) child_cnt
8 from my_table
9 group by owner;
Materialized view created.
(Aside: If anyone can come up with a working syntax based on 'build immediate'
as opposed to 'on
prebuilt table', I'm all ears)
Cheers
Connor
=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx
Coming Soon! "Oracle Insight - Tales of the OakTable"
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
------------------------------------------------------------
____________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Aggregate IOT fast refresh materialized view
