Re: Critical Qs on materialized views
- From: Jared Still <jkstill@xxxxxxxxx>
- To: VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx>
- Date: Tue, 30 Aug 2005 20:46:20 +0100
comments inline
On 8/29/05, VIVEK_SHARMA <VIVEK_SHARMA@xxxxxxxxxxx> 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
---------- --------------------
1 Row 1
2 Row 2
2 rows selected.
12:41:33 SQL>select * from m2;
PK NAME
---------- --------------------
1 Row 1
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
Other related posts:
