DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Materialized View with Index Organization

  1. #1
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152

    Materialized View with Index Organization

    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:

    Code:
    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.

  2. #2
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155

    Wink

    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

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you completely missed the point about the MV being organisation index - which I do not think you can have

  4. #4
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    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.

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    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 ;

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    Try taking the DISTINCT out of your SQL..

  7. #7
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155

    Thumbs up

    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

  8. #8
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width