RE: Database Outages - Best Practices

>When I used V7 we had tables for each year of data and then used a view
>to present the data to the user. It was easy to delete a years
>worth of data, Just change the view. ...

Way back in V6, we did something similar.

We had an equipment control tracking system that 90% of the time wanted to know where an item was "now", but 10% of the time also needed to view the history of where it had been.

Current data was held in one table, history in another. As new data came in, the (prior) current data was inserted into the history table and the current record was updated in place (to avoid some fragmentation that would be caused by a delete followed by an insert).

We had about 40 offices around the country that needed a dozen different reports pre-printed for them when they arrived at work. We set up batch jobs to run each of the reports for each of the offices and watched the disk drives thrash themselves to death as they all fought for access to the same data.

So, we got smarter about it.

We reformatted the reports to print one office per page and ran the report once for the nation instead of 40 times for the 40 offices. A simple program parsed out the printfiles and split them into 40 separate files and forwarded them on to the relevant office printer. That cut our thrashing from 40 * 12 to just 12.

Most of the reports were just variations on the same theme, requiring 95% of the same joins and filters. So, we created views that provided the core joins and columns and wrote the reports to work off of the views.

Then, before the batch jobs were fired off at 1am in the morning, we dropped the views and created tables with the same name, populating them with the joined data. The reports whizzed thru the data! Once the reports were done, we dropped the tables and reinstituted the views. That way, anyone running the report for their office during the day (say, because of a jammed printer) would get the latest and greatest version of the data.

It was a great way to design away a performance problem plus get much more maintainable reports.

That was all possible in v6, the technical options are even more powerful today!

--
http://www.freelists.org/webpage/oracle-l