| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Critical Qs on materialized views
comments inline
On 8/29/05, VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> wrote:
>
>
>
> Jared, Folks
>
> Qs 1 Can the PK constraint be created on a PRE-Created (already existing)
> Unique index in ver 8i too?
Check the docs. I don't think this option existed in 8i. You won't know for
sure until you check the docs
and/or try it.
Qs 2 Like an index can be created in parallel with NOLOGGING, ONLINE Clauses
> can the Primary Key Constraint also be created with such clauses on a Table
> having NO indexes? If NOT then would it NOT be advantageous to 1st Precreate
> the unqiue index & then add the PK constraint?
>
>
You can build a PK by that method, but it doesn't seem to work for MV's.
Here's the scenario I've been working with. I need to rebuild a rather large table with little or no downtime.
The chosen method is via MV.
Updates to the source table must be preserved so that they can later be applied to the MV table.
You *could* pre-create the table, then create the PK, and finally create the MV on it with pre-built table.
When you do this though, the rows in MLOG$_<SOURCE_TABLE> will
be deleted without being applied to the MV. Correcting this will require a
complete
refresh, which will take as long as building the table in the first place.
The following test demonstrates that:
12:41:33 SQL>create table m1( pk number(4) not null, name varchar2(20) not null);
Table created.
12:41:33 SQL>
12:41:33 SQL>create index m1_idx on m1(pk) nologging;
Index created.
12:41:33 SQL>alter table m1 add constraint m1_pk primary key(pk);
Table altered.
12:41:33 SQL>
12:41:33 SQL>insert into m1 values(1,'Row 1');
1 row created.
12:41:33 SQL>commit;
Commit complete.
12:41:33 SQL> 12:41:33 SQL>create table m2 12:41:33 2 nologging 12:41:33 3 as 12:41:33 4 select * 12:41:33 5 from m1 12:41:33 6 /
Table created.
12:41:33 SQL>
12:41:33 SQL>create index m2_idx on m2(pk) nologging;
Index created.
12:41:33 SQL>alter table m2 add constraint m2_pk primary key(pk);
Table altered.
12:41:33 SQL>
12:41:33 SQL>create materialized view log on m1;
Materialized view log created.
12:41:33 SQL>
12:41:33 SQL>insert into m1 values(2,'Row 2');
1 row created.
12:41:33 SQL>commit;
Commit complete.
12:41:33 SQL> 12:41:33 SQL> 12:41:33 SQL>create materialized view m2 12:41:33 2 on prebuilt table 12:41:33 3 refresh fast 12:41:33 4 as 12:41:33 5 select * 12:41:33 6 from m1 12:41:33 7 /
Materialized view created.
12:41:33 SQL>
12:41:33 SQL>exec dbms_mview.refresh('M2','FAST')
PL/SQL procedure successfully completed.
12:41:33 SQL> 12:41:33 SQL> 12:41:33 SQL>select * from m1;
PK NAME
---------- --------------------
2 rows selected.
12:41:33 SQL>select * from m2;
PK NAME
---------- --------------------
1 row selected.
12:41:33 SQL>
12:41:33 SQL>select * from mlog$_m1;
no rows selected
12:41:33 SQL>
12:41:33 SQL>set echo off
I've found that something similar will happen if you create the materialized
view with 'never refresh', alter it
to 'refresh fast' and then try to do a fast refresh. The difference is an
error is generated.
12:46:30 SQL>create materialized view m2 12:46:30 2 on prebuilt table 12:46:30 3 never refresh 12:46:30 4 as 12:46:30 5 select * 12:46:30 6 from m1 12:46:30 7 /
Materialized view created.
12:46:30 SQL>
12:46:30 SQL>alter materialized view m2 refresh fast;
Materialized view altered.
12:46:30 SQL>
12:46:30 SQL>exec dbms_mview.refresh('M2','FAST')
BEGIN dbms_mview.refresh('M2','FAST'); END;
*
ERROR at line 1:
ORA-12057: materialized view "JS001292"."M2" is INVALID and must complete
refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841 ORA-06512: at line 1
In both cases a complete refresh is required.
To work with what MV creation will allow (at least to the best of my
knowledge at this time)
the target table is created empty with a primary key constraint.
A complete refresh is then done, and the MV altered to do periodic fast refreshes.
Other indexes are then created as needed.
I haven't yet tried any of Tim's suggestions, but may if I have time.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist -- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 30 2005 - 14:48:33 CDT
 |
 |