I am just starting to look at workspace manager. I see it can be very useful to handle versioning and what if analysis for our requirements. This is a big impact on the database. I read the documentation but I am a little concerned about what I don't know. Is this solution mature enough? Any feedback from people who have implemented in a big scale for 200+ tables? I am concerned about things like not being able to drop partition, performance, etc. Any feedback will be appreciated.
Workspace Manager has been around since at least Oracle 8, so it is a very mature, developed product. It contains a broad range of functionality as needed by many users over the years. There are implementations that use that many versioned tables, so that should not be a problem. There is some overheard associated with the table after it has been version enabled. My suggestion would be to run some tests to see if it is acceptable for your use case. As you noted, there are DDL restrictions as outlined in the doc. Most users work under these constraints as the versioning that is provided would be difficult or data-intensive(ex. making full copies) to provide another way.
If you have any follwowup questions, let me know. But, the best suggestion would be to version enable some tables and run some tests to see if it fits your requirements. The SQL doesn't have to change, so it shouldn't be too difficult to test out the functionality and see if it fits what you are attempting to do.
In addition to what Ben said: performance does not need to be impacted. You can actually use partitioning cleverly to avoid having to search through history for example.
I have been involved in proof-of-concept tests where we have had one partitioned table with 40 million records version-enabled (the version-enabling itself took about 1.5 hours), multiple triggers and constraints, and performance was absolutely brilliant. It is worth the time though to get yourself aquainted with what really happens under the hood when you version-enable a table, and use that knowledge to re-think your datamodel. It may not need any changes but it may benefit from adapting it in the long run.
Also, not all of your tables need to be version-enabled. Tables that rarely change (once every year, or once every quarter for example) do not need version enabling. Only the tables that do change frequently, or which could be used in comparing scenario's should be version-enabled. Look-up tables do not need it.
Don't hesitate to ask if you have other questions,
Thank you. In fact, we have already started development using VERSION ID and effective dates manually to track versions. In the middle of it, I learned about workspace manager. We are evaluating wether it makes sense to change what we already have built and use the workspace manager. For that to happen, we are wondering what functional benefits it has over manually implementing versions to version id and effective date columns. Obviously, anything can be built manually, and some of these workspace manager procedures make it easier to maintain the data and has some cool procedures regarding what can be done within workspaces. I've gone through a lot of the procedures to learn about workspace manager, but in you mind, what are some of the best features of workspace manager and some of the best procedures in terms of functionalities to convince somebody to use this vs manual approach? I guess it depends on the requirements we have, but I'm taking it from the other side: trying to find features OWM has which we could leverage in our application.
It does depend on your requirements and business rules :-)
On a serious note: One of the possibilities with Workspace Manager is the workspaces themselves, and the fact that you can use these not only for Long-Term Transactions, but also for comparing alternatives for example. I'm not sure what business you are in but let's take an example of land registration: a new highway needs to be designed and there are a few different designs. For every design you need to know how much land you need to aquire and how much that will cost and which owners are involved. You can then create a Workspace for every alternative, and do whatever it is that is needed without interfering with the existing data, or other users (they will still see the actual data). You can even create child workspaces from these alternatives.
I think that is one of the most powerful features, because it allows you to do your thing on the actual production data without having to go through snapshots that go stale, or interfere with day-to-day business, or anything else.
If it is just simple versioning you are after, it may well be that developing a few extra columns and triggers is the better way to go because it causes less overhead (but more work and maintenance!). On the other hand there is also the fact that you can use an off-the-shelf product instead of custom-made software, which in the long run usually ends up being cheaper and easier (allthough the learning curve may be a bit steeper).
And finally: Workspace Manager allows you an extra layer of security and data integrity. Because an ordinary user should not be able to edit the LIVE-data, it is easy to perform a check before data gets merged. This enhances your data integrity, because a simple error does not end up in your main data as easily. Especially in a multi-editor environment this can be a tremendous benefit if people are working on the same data, so they're not overriding each other's edits, thus maintaining a better overall quality of your data.
Are you saying that you've partitioned a version-enabled table in such a way that current rows are in one partition and history rows are in an older partition? If so, could you share an example of that? I've looked a bit in the past at the views that Workspace Manager creates to figure out what is a "current" row but I've never managed to combine partitioning with Workspace Manager. Once the historical data got "too big" to deal with in the current table, I've tended to moved away from Workspace Manager and toward building my own solution based on a partitioned table. If you've got a sample that combines both, you'd really make my day.
Yes we did manage to do that. Off the top of my head I'm not entirely sure how we did it, it's been a couple of years since. I'd have to search my archives for the exact way, but from memory here's how: The tables contained land parcels. Those parcels fall logically under municipalities, and because wee needed to know when a parcel's valid time ended the parcels both had a column with a code for the municipality and an en-of-life parcel (and also columns for which parcel came to be from which old parcels, so you could trae histry back, but that aside). The single table covered the whole country, basically, every single parcel of land had one record in that table.
We created a kind of dual partitioning: a partitioning by municipality code, and by end-of-life date column. If the eol-column indicated it was a valid parcel (I seem to remember we couldn't use NULL and had to resort to 31-12-2999 or something like that) it would end up in the main partition for that municipality, otherwise in the history partition. And we used our own code to set the eol-column (there was a bunch more business logic in triggers and such, made it quite difficult to version-enable). It had to be our own eol-column, because of legal reasons it couldn't be the WM-column. But apart from the legal reasons, we had tried it with the WM-columns as well and if memory serves me right that did work (I was part of the Proof-of-Concept project, we had to try different solutions).
Despite the table containing some 15 million rows (or something like that) we managed to get excellent performance out of it.
If you want, I can see if I can still find the exact way we did it, but that might take some time.
No, because the queries would always be spatially limited. So the queries would always use an SDO_FILTER, and because we partitioned the spatial index on the table as well the spatial index would do the pruning for us, which meant we would achieve tremendous performance. The only problem would be if we'd query the history without any spatial filtering, but that would never happen (and if some user would be not smart enough to query for the whole of the country, that query would be broken off anyway).
Without an SDO_FILTER almost no application could deal with the amount of data. Be aware that this was a specific GIS-implementation, where the applications looking at, modifying and changing the data would be GIS-applications, and the records would all have an SDO_GEOMETRY. So the users would only be interested in a small part of the table inside one of the partitions, mainly the active partitions, anyway.
It actually surprised us a bit when we found out that without any reference to the partitioned columns any query with a spatial reference would be very quick indeed, we had not expected that. But then we found out from Oracle Support that if you have your spatial index partitioned, it will automatically prune on the root mbr of the partition, thus resulting in really good performance. Any other queries that would go across boundaries would be batch-queries anyway, where performance was less of an issue and usually we could manage to insert a reference to one of the partitioned columns.
I suspect though that it should not be a problem if you used one of the WM-columns for partitioning, so that the WM-views would work properly if you cannot use the spatial pruning. Haven't tested that, at least not that I can remember :-) (all this was 3 years ago now, so not entirely fresh in memory anymore).