Materialized View with Index Organization

[Scorby]

Oracle 9.2.0.6.0 - 64bit on Sun Solaris

Has anyone managed to create a materialized view with index organization ? I have an MV which only has one column, so I wanted it to be index organized, but I'm really struggling with the syntax for it. My latest attempt is:

create materialized view mv_annuity_tcns using index refresh on demand
organization index pctthreshold 90 including annuitytcn overflow tablespace sml_d 
as
SELECT
       DISTINCT
       TRIM(COLUMN_72)       ANNUITYTCN
FROM SRC_FEED_DATA
WHERE SRC_SYS_FEED_ID = 666
and TRIM(COLUMN_72) IS NOT NULL

Whatever I try, I get ORA-905, missing keyword. Unfortunately, it gives me no clue as to which keyword is missing and the manual is none too helpful.

[pss]

Here are you go:

13:18:16 system@QM1T> create table test (col number primary key)
13:18:31 2 organization index;

Table created.

Elapsed: 00:00:02.19
13:18:37 system@QM1T> insert into test values (1);

1 row created.

Elapsed: 00:00:00.14
13:18:45 system@QM1T> insert into test values (2);

1 row created.

Elapsed: 00:00:00.00
13:18:52 system@QM1T> insert into test values (3);

1 row created.

Elapsed: 00:00:00.00
13:18:55 system@QM1T> commit;

Commit complete.

Elapsed: 00:00:00.00
13:18:57 system@QM1T> create table test_src as select * from test;

Table created.

Elapsed: 00:00:01.06
13:20:32 system@QM1T> alter table test_src add (constraint test_src_pk primary key (col));

Table altered.

Elapsed: 00:00:00.58
13:21:10 system@QM1T> create materialized view test on prebuilt table
13:21:16 2 as
13:21:17 3 select * from test_src;

Materialized view created.

Elapsed: 00:00:02.64
13:21:24 system@QM1T>


Sergey

[davey23uk]

you completely missed the point about the MV being organisation index - which I do not think you can have

[pss]

Look carefully, I created an IOT table called test then created a MV on it. Yes, I created the MV table first then the source table for it called test_src. I don't think it makes a difference.

[ixion]

Sure you can!
Heres an example:


SQL> create table test (id number, column_72 varchar2(100))
2 tablespace users_std_Tbl;

Table created.

SQL>
SQL> alter table test add (
2 constraint test_pk
3 primary key (ID)
4 USING INDEX
5 PCTFREE 10
6 TABLESPACE users_std_tbl );

Table altered.

SQL> insert into test values (1, 'abc');

1 row created.

SQL> insert into test values (2,'def');

1 row created.

SQL> insert into test values (3, null);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> create materialized view mv_test
2 organization index
3 pctfree 10
4 pctthreshold 30
5 including tcol overflow tablespace users_std_tbl
6 using index
7 refresh on demand
8 as
9 SELECT id
10 , trim(column_72) tcol
11 FROM test
12 WHERE id = 2
13 and TRIM(COLUMN_72) IS NOT NULL ;

[ixion]

Try taking the DISTINCT out of your SQL..

[pss]

Ok. Here is the test script to prove my point:

create table test_src (loc number primary key);

insert into test_src values (1);
insert into test_src values (2);
insert into test_src values (3);
commit;

create table test (loc number primary key)
organization index;

insert into test values (1);
insert into test values (2);
insert into test values (3);
commit;

create materialized view test on prebuilt table
as
select * from test_src;

select mview_name,container_name
from user_mviews
where mview_name='TEST';

select table_name,iot_type
from user_tables
where table_name='TEST';


Sergey

[Scorby]

Thanks for all your help. I'm now getting a more helpful error message - "ORA-25175, no primary key constraint found". I think it must have been just the order of the keywords that was giving problems before. I'm not sure if there is anyway around the fact that the source table doesn't have a primary key. I could create an intermediate table, but that defeats the point of having a materialized view - I may as well just use the intermediate table.