Re: Oracle Table Partitioning

I implemented partioned views on one 7.3 database (partitioned by month) and have had great success with it to this point. We utilized the UNION ALL and the check constriants on the individual tables. Also, since we roll-off the oldest month and load a new month of data each month end it allowed us to take advantage of the truncate procedure.

The optimizer did wonderful. It does scan all the tables, but will return 0 rows from tables that are outside of the partition constraints.

I cannot say much for online performance. We increased our data by 50% at the same time we added the partition views. Some of the tables had a decrease in performance, but for the most part performance stayed roughly the same (+/-10%) on the tables. I figured this was not bad considering a 50% increase in data. The real performance gain was in the month end processing -- we were now able to utilize truncates, only needed to rebuild indexes and check constraints for 1/3 the data. The also reduced the amount of Rollback and Temporary space required.

If we suffered any serious performance degradation, it was in one procedure (that was already slow and runs in the background). This procedure however requires data from ALL partitions of the partitioned view.

--
Steven Seacord
Oracle DA/DBA
AMP, Inc

Mike Rushton wrote:


> 

> Has anyone implemented partitioning of tables under 7.3?

> I.e. splitting a table up into separate tables (one per year perhaps) and then

> joining them back together in a view with the UNION ALL operator

> 

> What are the performance increases like?

> 

> Does the optimizer work as Oracle suggests (i.e. it only scans the tables needed

> to retrieve the data required and not all the tables)?

> 

> What about Table constraints, do these need to be duplicated for every table?

> 

> Any views welcome before I attempt this.

> 

> Thanks

> 

> 

> Mike Rushton

> mike_at_mrush.demon.co.uk