On 8/29/05, VIVEK_SHARMA <VIVEK_SHARMA@(protected)> 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;
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 /
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
comments inline<br><br> <div><span class="gmail_quote">On 8/29/05, <b class="gmail_sendername">VIVEK _SHARMA</b> <<a href="mailto:VIVEK_SHARMA@(protected)">VIVEK_SHARMA@(protected) .com</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> <br><br>Jared, Folks<br><br>Qs 1 Can the PK constraint be created on a PRE -Created (already existing) Unique index in ver 8i too?</blockquote><div><br> Check the docs. I don't think this option existed in 8i. You won't know for sure until you check the docs <br> and/or try it.<br> </div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204 , 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">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?<br><br></blockquote></div><br> You can build a PK by that method, but it doesn't seem to work for MV's.<br> <br> Here's the scenario I've been working with. I need to rebuild a rather large<br> table with little or no downtime.<br> <br> The chosen method is via MV.<br> <br> Updates to the source table must be preserved so that they can later be<br> applied to the MV table.<br> <br> You *could* pre-create the table, then create the PK, and finally create<br> the MV on it with pre-built table.<br> <br> When you do this though, the rows in MLOG$_<SOURCE_TABLE> will <br> be deleted without being applied to the MV. Correcting this will require a complete <br> refresh, which will take as long as building the table in the first place.<br> <br> The following test demonstrates that:<br> <br> 12:41:33 SQL>create table m1( pk number(4) not null, name varchar2(20) not null);<br> <br> Table created.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>create index m1_idx on m1(pk) nologging;<br> <br> Index created.<br> <br> 12:41:33 SQL>alter table m1 add constraint m1_pk primary key(pk);<br> <br> Table altered.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>insert into m1 values(1,'Row 1');<br> <br> 1 row created.<br> <br> 12:41:33 SQL>commit;<br> <br> Commit complete.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>create table m2<br> 12:41:33 2 nologging<br> 12:41:33 3 as<br> 12:41:33 4 select *<br> 12:41:33 5 from m1<br> 12:41:33 6 /<br> <br> Table created.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>create index m2_idx on m2(pk) nologging;<br> <br> Index created.<br> <br> 12:41:33 SQL>alter table m2 add constraint m2_pk primary key(pk);<br> <br> Table altered.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>create materialized view log on m1;<br> <br> Materialized view log created.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>insert into m1 values(2,'Row 2');<br> <br> 1 row created.<br> <br> 12:41:33 SQL>commit;<br> <br> Commit complete.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL><br> 12:41:33 SQL>create materialized view m2<br> 12:41:33 2 on prebuilt table<br> 12:41:33 3 refresh fast<br> 12:41:33 4 as<br> 12:41:33 5 select *<br> 12:41:33 6 from m1<br> 12:41:33 7 /<br> <br> Materialized view created.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>exec dbms_mview.refresh('M2','FAST')<br> <br> PL/SQL procedure successfully completed.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL><br> 12:41:33 SQL>select * from m1;<br> <br> PK NAME<br> -- ---- -- -- ---- ---- ---- --<br> 1 Row 1<br> 2 Row 2<br> <br> 2 rows selected.<br> <br> 12:41:33 SQL>select * from m2;<br> <br> PK NAME<br> -- ---- -- -- ---- ---- ---- --<br> 1 Row 1<br> <br> 1 row selected.<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>select * from mlog$_m1;<br> <br> no rows selected<br> <br> 12:41:33 SQL><br> 12:41:33 SQL>set echo off<br> <br> <br> I've found that something similar will happen if you create the materialized view with 'never refresh', alter it<br> to 'refresh fast' and then try to do a fast refresh. The difference is an error is generated.<br> <br> 12:46:30 SQL>create materialized view m2<br> 12:46:30 2 on prebuilt table<br> 12:46:30 3 never refresh<br> 12:46:30 4 as<br> 12:46:30 5 select *<br> 12:46:30 6 from m1<br> 12:46:30 7 /<br> <br> Materialized view created.<br> <br> 12:46:30 SQL><br> 12:46:30 SQL>alter materialized view m2 refresh fast;<br> <br> Materialized view altered.<br> <br> 12:46:30 SQL><br> 12:46:30 SQL>exec dbms_mview.refresh('M2','FAST')<br> BEGIN dbms_mview.refresh('M2','FAST'); END;<br> <br> *<br> ERROR at line 1:<br> ORA-12057 (See ORA-12057.ora-code.com): materialized view "JS001292"."M2" is INVALID and must complete refresh<br> ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 803<br> ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 860<br> ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT", line 841<br> ORA-06512 (See ORA-06512.ora-code.com): at line 1<br> <br> <br> In both cases a complete refresh is required.<br> <br> To work with what MV creation will allow (at least to the best of my knowledge at this time)<br> the target table is created empty with a primary key constraint. <br> <br> A complete refresh is then done, and the MV altered to do periodic fast refreshes.<br> <br> Other indexes are then created as needed.<br> <br> I haven't yet tried any of Tim's suggestions, but may if I have time.<br> <br> <br> -- <br>Jared Still<br>Certifiable Oracle DBA and Part Time Perl Evangelist<br> <br>
