Materialized View with Index in Separate Tablespace

1
votes

We're attempting to perform data migration from one schema to another via the use of materialized views. The process I've setup currently works as follows:

  • Create snapshot/materialized view log:

    create snapshot log on OLDSCHEMA.table;

  • Create materialized view on new schema:

    create snapshot table as select * from OLDSCHEMA.table@OLDDB

  • Refresh the materialized view

  • Break the link, preserving the table

For historical reasons, the indexes are kept in a separate tablespace and we want to maintain the same structure on the new schema (I'm aware that there is no performance benefit to this, we're doing this to stay consistent). I understand that I can accomplish this by altering the primary key after the fact:

alter index PK_IDX rebuild online tablespace IDX_TABLESPACE

But is it possible to perform this at the time of snapshot creation to avoid having to move it? I'm hoping for some additional clause for the create snapshot command that affects where the primary key gets generated.

Thanks

1
sqloracle
if you break the link and preserve the table why are you using an mv in the first place ? (use create as select .... no ?!?) – haki
Because the original tables still get updated as/after they're copied, we want to rely on the fast refresh to easily pull in those changes. The link is only broken once we confirm all the data is there, the original schema is set to read-only at the end, this minimizes the downtime. – Alexander Tsepkov
have you tried using the using index tablespace x clause ? although i think it applies only for the internal indexes. – haki
Thanks, that does exactly what I want, didn't realize you could pass tablespace to using index clause. If you post that as an answer, I'll accept it. – Alexander Tsepkov

1 Answers

2
votes

you have a using index tablespace clause in the create materiazlized view command.