| Design decision - Table partitioning for archiving? [message #242719] |
Mon, 04 June 2007 16:16  |
prasadpav
Messages: 1
Registered: June 2007
|
Junior Member |
|
|
Dear all,
We have oracle DBA team in our project but whatever decision they take it always appear as if they are just doing it for the sake of billability and no concern about the proper design. I'm not a DBA expert, but I want to know what other oracle experts here think about the following items:
1) We have a simple table (table with three columns - VARCHAR(42), CLOB & TIMESTAMP). This table is populated frequently with data and this data should be retained for just a day. So, those records which are more than a day old can be thrashed. In order to achieve this - I think it would be simple to write a stored procedure or some other scheduler job to simple clear the data every night. However, our DBA team came up with the idea of table partitioning and put forward some points that seem to support their decision. May I know whether is this the best solution or is it just an overkill solution?
2) In another instance, for the above mentioned simple table, we have to do a simple INSERT and SELECT (First column VARCHAR one is the primary key column). Again, our DBA team insists that we have to have stored procedures for doing insert and select. So, they ended up creating 2 stored procs (one for insert and one for select) and a package for holding these procs. I don't understand, why on earth do we need these stored procs for doing simple inserts and selects. One argument is - future changes to the table will be seamless, but look at the dependency on another deployment item. Also, future changes is not strictly seamless because we have to go through the whole process of system testing and production installation window..etc. My point is, we should use stored procs only in the case of multiple table interactions and committing different table updates in a single transaction.
Thanks for going through this post.
Regards,
Prasad
|
|
|
|
| Re: Design decision - Table partitioning for archiving? [message #242728 is a reply to message #242719] |
Mon, 04 June 2007 21:22  |
saibal
Messages: 111
Registered: March 2007
Location: India
|
Senior Member |
|
|
It is difficult to pass a judgment based on the briefs you provided and not being conversant with the business rules being followed in your organization, I can only hazard an educated guess, but it seems to me that in the first case where you want to retain data only for a certain period, Global Temporary Table with ON COMMIT DELETE rows, would have been a simple and straightforward solution.
And as for your second point, in my opinion, procedures are there to do a task for you, and if in your case, the DBAs insist on using procedures to SELECT from and INSERT into the table, then so be it. I don't see too many downsides with this.
Regards
|
|
|
|
| Re: Design decision - Table partitioning for archiving? [message #244453 is a reply to message #242719] |
Tue, 12 June 2007 23:20 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
1. Depends on how quickly you want to delete old data.
In a partitioned table, dropping old partition(s) takes just a second.
In a non-partitioned table, it depends on amount of data and may take hours; additionally (in case of direct-path insert) you should move remaining rows to avoid high watemerk exceeding. Not good for system performance.
If you want to process/report/do anything with rows inserted in other session(s) (suppose the application does not use one session for inserting data and reporting), global temporary table cannot be used at all. Also commiting transactions once a day is not a good idea.
2. What is wrong with stored procedures? On the other hand, would you not mind to access database in two different ways (direct sql vs. stored procedures)?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
