Aggregate IOT fast refresh materialized view

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Thu, 27 May 2004 01:33:24 +0100 (BST)
Message-ID: <20040527003324.27783.qmail@web25204.mail.ukl.yahoo.com>

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_at_yahoo.com

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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Wed May 26 2004 - 19:30:15 CDT