Archiving VS Partitioning Tables

Hi To All,
This is my first message on the Forum.
I have recently passed the DBA part 2 exam, I have very little experience as DBA although I have more then 7 years of experience as an Oracle Developer.
As soon as I informed my supervisor that now I am officaillay an Oracle Certified Associate DBA, he assigned me submit two reports.
A) We have tables that have millions of records. An example of the smallest table is that it has 66338594 records with average row length as 102.
I am supposed to provide some suggestion as to what should be done with these tables (Archive or partition or both based on dates like prior to 2000) what ever the option is the archived data or partitioned data must be available for “SELECTS” only.
I would like your expert’s opinion on such a case.
What are the advantages of archiving vs. partition?
Is there a table size that oracle recommends that should be archived or partitioned?
If we choose archive as one of the option, then after all data before 2000 is archived, the data from production tables must be deleted, but deletion of data from table does not release space, how can this improve performance?
I don’t know as I said I am new as DBA, does not understand a lot of technicality involved.
I was thinking that data that is used very rarely and only for “SELECTS” can be archived and the remaining data can be partitioned as A and B (A being most current). Is it possible to query the archived data.
B) Currently we are using Oracle 9i as DB, and Oracle 6i for Forms and reports, I am supposed to submit the advantages the comes with 10G, if we migrate from these version.

Thanks in advance,

Habeeb
Thanks to all

Habeeb

I assume that archiving means moving data from one database to another correct?

It depends what your boss want, if he wants a small compact database then you have to archive, if data older than 5 years is not needed anymore then archive

If you dont mind have a huge database then just partition, if you need the last 10 years data then partition

Archiving is not that simple though, depends on your data model it can be a nightmare archiving data since you have to archive from child to parent, grand parent, gran grand parent and so on. Work out all those dependencies probably takes you months.

Partition isnt too bad, a fast way to reduce a huge table into small segments but then again there are limitation such as global indexes needed to be rebuilt if partition structure is changed, such as merging or splitting partitions. In OLTP systems mostly we use global indexes so it quite a headache if you have a 1000 million partitioned tables and you need to add a new partition which cause a rebuild of all global indexes in the table.

Regarding versions, if you wanna move to 10g I suspect you need to upgrade your Developer Suite as well, I am not that sure if 6i is certified against 10g (except Oracle E-Business Suite of course). Upgrading Developer is tough if your 6i applications are server-client based because moving to 10g Developer Suite means you need Oracle Application Server and migration to three tier architecture. Lots of things to consider. 10g database is very powerful though, ASH is specially helpful.

It's all about depends....

Thanks dear for the enlightenment :)

It is really scary to manage VERY LARGE DATABASEs (like the movie).
I am confused about the purpose of partitioning agains sacrificing
the rebuilding of GLOBAL indexes. Do I have to partition or not :confused:

What do u mean by ASH? or is it ASM?

"10g is powerful" ....but I dont feel much of its power yet :D
except the power of my new servers core duo and large memories.

Any other powers in 10g u have appreciated yet? kindly share pls.


Thanks

Habeeb should bear in mind that partitioning has to be paid for as well. The overhead on maintaining global indexes is not too bad when you consider that you have to perform exactly the same maintenance when you are deleting rows from the underlying table anyway.

sorry, I should have specified, ASH I meant Active Session History, not the automatic storage management

I am so sorry that I could not response in time, Thanks to all who have replied.
I have read all replies.
What I meant by archiving is to move data not to another DB, but to a seperate storage.
I was reading how Oracle 10G can benefit partitioning and I read this article, Information lifecycle management, how the storage cost can be minimized by partitioning most of the historical data at low-end storage.
Plus some advantages about archiving (data if neccessary) by using XML.
As I said I have very little or no knowledge about these issue.
I am really thankfull to these sites and people like you who help people like me.

Thanks again,

Habeeb

well archiving to another database or not does not matter, if its same database just move the data under another schema and different tablespaces located in low end storage if you dont or cannot use partitioning (you need seperate license)

MHabeeb,
I think you should first find what you want to achieve. Lower the cost of the storage, improve the performance, or what? Also you have to decide do you need all the data on daily basis or most of them can be safely moved away from the database and kept somewhere just in case that someone needs that.
Then you can answer your questions yourself.
Do not pay so much attention on the other people's oppinion. Yes, they could be more experienced but they for sure does not knows your exact situation.
Better try to make a plan for both approaches EXACTLY how that will look like. See the pros/cons of both and discuss that with your boss. Do not forget to count the price of the partitiononing option and storage and etc.
There is no just black and white solution, they all have pros and cons it is not exactly like I WAS RIGHT or I WAS WRONG. Both will work, the question is what will fit better in your situation

Regards
Boris

Quote:

---

Originally Posted by Bore

I think you should first find what you want to achieve. Lower the cost of the storage, improve the performance, or what? Also you have to decide do you need all the data on daily basis or most of them can be safely moved away from the database and kept somewhere just in case that someone needs that.
Then you can answer your questions yourself.

---

As a general principle I think this is incorrect. While you do need to be very clear about what you're trying to achieve and why, you also need to have a very good understanding of the pros and cons of all the possible solutions as well in order to define the most appropriate methodology -- that's where the experience part comes in.

Yes you are right, that's actually what I am trying to decide. What to do I have so many options, to archive or not to partition or not there are certainly adv and disadv to all approches and as said by Bore "Both will work" but taking a decision is something which not can have economic impact but also about future maintainance, for example I am not sure if the existing data is partitioned will it impact the current code or not? I mean there are so many questions that certainly comes with experience.
Thanks to all.

Habeeb

Partitioning does not impact existing code -- it is a change at the physical layer, which the code does not care about. However, if you partition on a date field or something then you may have to maintain that scheme by addinng additional partitions on an appropriate schedule.

Archiving away data will shrink your database, which will make many maintenance tasks faster (restoring a small database is faster than large, etc). However, archiving is not an option unless your data model supports it -- if it depends on being able to query all data since the Beginning Of Time to make some calculations, you can't do it.

Assuming your data model supports it, AND you partition on the archiving key, then archiving becomes as simple as unattaching partitions from one database and attaching them to the other (see transportable tablespaces).