Nicholas Irving, March 30, 2023 - 9:04 pm UTC
The livesql does not allow me to create 2 different schemas so I am simulating that by naming the 2nd schema _mv and I have simplified the schema so I don't show all the transform I am having to do to translate a JSON stored in a clob. I can provide a more complex example of the transform.if required,
I am showing the error I am getting when creating the MV in the 2nd schema which would be the same name but without _mv.
The reason for the 2nd schema is that we are migrating from v5.5 to v7.1.4 of an application and they don't want the added cost of creating a new database and the associated hardware costs. Happy to pay for more storage, just not the compute.
In the complex example the data in the first schema JSON clob column is extracted and converted into columns of the 2nd schema table. GoldenGate is not possible, as I would simply the do the migration using that and reading other comments MV is the way to go as we can drop the MV and still keep the underlying data, indexes and sequences meaning the migration is transparent to the updates version of the software.
Does that make sense?
March 31, 2023 - 5:04 pm UTC
I see what you're trying to do; I still don't understand why you're trying to use a materialized view to do it!
I would simply the do the migration using that and reading other comments MV is the way to go as we can drop the MV and still keep the underlying data, indexes and sequences meaning the migration is transparent to the updates version of the software.
Perhaps I'm missing something, but I doubt it's that straightforward in practice. In particular, it assumes that you can have a fast refresh on commit MV. Depending on what exactly the transformation is, it may not be possible.
Have you considered using edition-based redefinition?
This is designed for you to do schema migrations with zero downtime & avoid the hassle/overhead of creating a second schema. Unlike the MV approach this also allows you to run both versions of the app at the same time.
If you're dead set on using MVs, then rather than identity columns use sequence defaults. In effect these are the same. A big difference is you can assign sequence defaults whenever you want. Identities can only be applied to new columns.
So you can create the table & make an MV on top of it. When it comes to the switchover, drop the MV and modify the column to use the sequence.
Nicholas Irving, March 31, 2023 - 8:59 pm UTC
Thanks asktom. Currently we are not concerned about the refresh delay, just more about getting the data into the new schema tables and transformed.
EBR sounds perfect as it removes the problem of rollback, just need to work out how to do the transformation of the JSON data in the CLOB on xx million records, hence why the Materialized View was so attractive to us, it was taken care of OOTB and gave us that refresh capability without having to figure out the deltas between creating the new schema and going live into production.
EBR is the way to go, if we can't get it working then we have the fallback of MV and Sequence update, or just a plain ETL into a staging table.
You have given us a new direction and focus and for that you have my grateful thanks
April 04, 2023 - 5:41 am UTC
glad to be of service - keep us posted on how it goes