Converting partition views to partitioned tables
A few days I was talking to a customer who has a legacy data warehouse, designed back in Oracle 7.3 days which relies extensively on partition views as a way of handling the volume of data involved (basket level sales from a national high-street chain). Over the years the underlying platform has changed and the database has been upgraded to Oracle 10g, but in that time the data-load code has not changed more than was needed to remain functional.
As you may know, partition views are a way of dividing up what would otherwise be a large table into a series of smaller tables that contain specific subsets of the data and then building a UNION ALL view over the small tables so that it appears that there is single table containing all of the data. From the view predicates and, sometimes, with the help of check constraints the query engine will be able to identify and access just the tables that contain the rows of interest, which is of course similar to partition elimination or pruning. Of course, this technology was the forerunner to the far more versatile partitioning introduced in Oracle 8i, but the old way still exists and still can be used.
Migrating from partition views to partition tables is not normally a big deal if you keep to the same partitioning scheme - basically you create an empty partitioned table and exchange each of the component tables into the partitioned table. You don't even have to do the whole table in one go, it quite possible to make a partition view over as a union all of a partitioned table and the remaining (unconverted) tables.
The fun starts when you want to change the partitioning scheme! For example. suppose the system was designed for efficiency of batch with an empty table each day to receive the day's transmission from the outlets, in effect range partitioned on data-load date. But in reality the only time people look at data-load date is during the batch process, the rest of the time people query on sales date. Often the two dates are the same, but in the real world network links go down, stores get flooded or whatever and for various reasons data can arrive several days late. So when we decide to migrate to partitioned tables perhaps we should also think about changing the key column for the partitioned table from load date to transaction date. But now our simplistic exchange conversion method won't work so well as some data will need to move to other partitions as it arrived 'late'. Given this scenario I would be very tempted to create a compressed partitioned table and to use an ordered direct-path load to populate the partitions from the source tables.