Batch update of a critical table

From: Ravi Pinto <ravipinto_at_my-deja.com>
Date: Fri, 24 Sep 1999 19:35:12 GMT
Message-ID: <7sgjpg$j5c$1@nnrp1.deja.com>

Hello world,

This is my first posting to any Oracle newsgroup. Therefore , if it is not appropriate here, please let me know where I should post.

Situation.

---

A rate table with 22 million records needs to be updated periodically. The records would be read from a flat file.

Problem.

---

The application using the rate table is a mission critical app, with 24 x 7 x 365 (366 ???) uptime. People from world over would be using it. Therefore, the updates should happen fast.

Solutions.

---

Solution 1. Load the records into a new table and after the load is over, rename original table to something else, rename new table to rate table and continue.
Question :
a. How fast would this be ???
b. Does rename obtain exclusive lock on tables ??

Soultion 2. Maintain 2 sets of rate tables, say R1 and R2. Designate R1 as current rate table and R2 as backup table. Load records into R2. After load is over, update app specific parameter table with some sort of record signifying that R2 is now current rate table. The app querying the rate table decides on which table to use by first querying the parameter table.
a. Sounds logical ???
b. Any concerns.

Any other solutions that you are already using ??? Currently we are tending towards the second solution. However, I think it is better to ask people who may already have handled this.

Thanks for listening,
and TIA,
Ravi...
--

Ravi Pinto, Bombay, India.
mailto:ravipinto_at_my-dejanews.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 24 1999 - 14:35:12 CDT