>> i) unless you use a mv on a prebuilt object such as a table in a cluster or an IOT - sure........  (eg: you can get physical ordering if you need it)

Ah quite so, but you use clustering at the expense of partitioning, no?

SQL> create cluster obj (OBJECT_TYPE VARCHAR2(19),OWNER VARCHAR2(30))
  2  single table hashkeys 2000;

Cluster created.

SQL>
SQL> create table obj_t cluster obj (OBJECT_TYPE,OWNER)
  2  compress
  3  partition by range (object_name)
  4  (partition p1 values less than ('M'),
  5  partition p1 values less than (MAXVALUE))
  6  as select * from all_objects;
partition by range (object_name)
*
ERROR at line 3:
ORA-14026: PARTITION and CLUSTER clauses are mutually exclusive

... or is there an alternative method that would work for that? The maintenance and performance advantages of partitioning are a tough loss.

I was testing compression on a cluster, and noted that the server allowed the syntax to define the table as compressed but there didn't seem to be any effect ...

SQL> set feedback off
SQL>
SQL> drop table obj_t;
SQL> drop cluster obj;
SQL>
SQL> create cluster obj (OBJECT_TYPE VARCHAR2(19),OWNER VARCHAR2(30))
  2  single table hashkeys 2000;
SQL>
SQL> create table obj_t cluster obj (OBJECT_TYPE,OWNER)
  2  nocompress
  3  as select * from all_objects;
SQL>
SQL>
SQL> analyze table obj_t compute statistics for table;
SQL> select blocks,empty_blocks from user_tables where table_name = 'OBJ_T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
      2584            8
SQL>
SQL>
SQL> drop table obj_t;
SQL> drop cluster obj;
SQL>
SQL> create cluster obj (OBJECT_TYPE VARCHAR2(19),OWNER VARCHAR2(30))
  2  single table hashkeys 2000;
SQL>
SQL> create table obj_t cluster obj (OBJECT_TYPE,OWNER)
  2  compress
  3  as select * from all_objects;
SQL>
SQL> analyze table obj_t compute statistics for table;
SQL> select blocks,empty_blocks from user_tables where table_name = 'OBJ_T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
      2584            8
SQL> alter table obj_t add (my_col number);
SQL>
SQL> SQL> drop table obj_t;
SQL>
SQL> create table obj_t
  2  nocompress
  3  as select * from all_objects;
SQL>
SQL> analyze table obj_t compute statistics for table;
SQL> select blocks,empty_blocks from user_tables where table_name = 'OBJ_T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
       549           27
SQL> alter table obj_t add (my_col number);
SQL>
SQL> drop table obj_t;
SQL> create table obj_t
  2  compress
  3  as select * from all_objects;
SQL>
SQL> analyze table obj_t compute statistics for table;
SQL> select blocks,empty_blocks from user_tables where table_name = 'OBJ_T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
       155            5
SQL>


With an IOT you need the mapping table as well, in order to be able to support bitmap indexes. However, it seems like a partitioned IOT + a mapping table + bitmap indexes + a materialized view + PCT fast refresh + query rewrite has the potential for problems -- I'm not saying it wouldn't work, but my own preference is avoid mixing so many features in a single object. Just a personal thing. 

>> ii) if that happened did you (please say yes) file a tar/bug??? things will never get fixed unless we (we -- collective we) report them. 

Absolutely, yes, but Oracle Support wouldn't look at it without a copy of the table + MV etc., a 150Gb export of sensitive gov't data. Since we felt that we had to revert to other means to populate the summaries anyway, we ended up sticking with our alternative solution.

Speaking of which ... an alternative that we felt protected our summary MV and got us the physical row ordering/partitioning etc that we wanted was to ...

i) Load the new fact data into a non-partitioned table (pending later partition exchange)
ii) Summarize the new data to a non-partitioned summary table.
iii) Partition-exchange into the fact table
iv) Partition-exchange into the summary table, on which was defined a never-refresh MV with query rewrite enabled
v) Mark the summary MV as fresh.

That's just an fyi -- it's what we could do in our own circumstances to workaround our own problems.

btw, Oracle Support could really use some people who understand summary MV's and query rewrite ... just IMHO ;) 

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

5 rows selected.

SQL> CREATE TABLE Test_Table (Cd VARCHAR2(50), PRIMARY KEY (Cd))
  2  /

Table created.

SQL> CREATE TABLE Test_IOT (Cd VARCHAR2(50), "COUNT(*)" NUMBER, PRIMARY KEY (Cd)) ORGANIZATION INDEX
  2  /

Table created.

SQL> 
SQL> CREATE MATERIALIZED VIEW Test_IOT ON PREBUILT TABLE AS
  2  SELECT Cd, COUNT(*) FROM Test_Table GROUP BY Cd
  3  / 
CREATE MATERIALIZED VIEW Test_IOT ON PREBUILT TABLE AS
           *
ERROR at line 1:
ORA-12082: "ENGADMIN"."TEST_IOT" cannot be index organized


SQL>