RE: Snapshots on prebuilt tables with reduced precision

  • From: "Parker, Matthew" <matthewp@xxxxxxxxxx>
  • To: <Leng.Kaing@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 12 Oct 2004 02:32:15 -0700

"With Reduced Precision" simply allows you to place a materialized view =
over a prebuilt table that the definition of the prebuilt table does not =
match the the precision of columns in the defining query of the =
materialized view. If you have data in the master that is larger than =
the precision of the prebuilt table, it will fail on refresh.
This would best be used if the prebuilt table had the larger precision =
definition than the master since smaller data size data would be =
replicated to the MV and this would allow any previous data in the =
prebuilt table that is larger than the master site to still maintain =
it's size. If you had a prebuilt table with smaller column widths than =
the master, then I would alter those columns on the prebuilt table to =
the larger size, instead of having the materialized view possibly fail =
in the future for the ORA-01401.

create table yo (col1 varchar2(10));

create table yoyo (col1 varchar2(8));

alter table yo add constraint pk_yo primary key (col1);

alter table yoyo add constraint pk_yoyo primary key (col1);

create materialized view yoyo on prebuilt table with reduced precision =
refresh force as select * from yo;

insert into yo values ('0123456789');

commit;

exec dbms_mview.refresh('YOYO','C');
BEGIN dbms_mview.refresh('YOYO','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01401: inserted value too large for column
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

Drop materialized view yoyo;

alter table yoyo modify (col1 varchar2(10));

create materialized view yoyo on prebuilt table with reduced precision =
refresh force as select * from yo;

exec dbms_mview.refresh('YOYO','C');

PL/SQL procedure successfully completed.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kaing, Leng
Sent: Tuesday, October 12, 2004 12:45 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Snapshots on prebuilt tables with reduced precision

Greetings all,

Initially I thought the option "with reduced precision" was used to get =
=3D around the problem of replication tables where column orders or =
number =3D of columns do not match. eg. master table has 4 columns but =
we only want =3D to replicate 3 of the columns. And/or the order of the =
columns on the =3D master and slave tables do not match.

However, today I've just discovered this definition in the Oracle =3D
manuals: "Specify WITH REDUCED PRECISION to authorize the loss of =3D =
precision that will result if the precision of the table or materialized =
=3D view columns do not exactly match the precision returned by =
subquery"=3D20 I'm now confused. What does "authorise the loss of =
precision" mean? =3D Master column can be varchar(10) and slave column =
can be varchar(8) and =3D
2 characters dropped off in the process?
Am I right to assume that "with reduced precision" is used with the =3D =
number of columns and/or order in the master and slave do not match?

TIA,

Leng.

--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l

Other related posts: