CODEWITHSUNDEEP
oracle-databasepartitioningoracle12c
Mayuran
Mayuran

Reputation: 708

Oracle database manual table partitioning

We have live database and archival database in our system. Everyday data from live database is moved into archival database. Every day we generated around 2 million of data into different tables.

Since we need to keep the data for 10 years, we have created another oracle database for archival purpose and pushing the data every midnight using a batch process.

Since the data grow rapidly, we are concerned about storing the data in a table in archival database. Since we don't have oracle partitioning license, we have created table for every month for 10 years(120 tables all together).

Our archival database is more write and there will be few read only. By splitting the table into multiple table, is there any proven advantages in terms of performance/storage?

Regards, Mayuran

Upvotes: 0

Views: 345

Answers (1)

Kacper
Kacper

Reputation: 4818

Yes there are. When you have no partitioning licence and store whole data in one table you can't split it into multiple tablespaces. But when you create multiple tables you can create a tablespace for let's say each year. This allows you to store 10 years old data which is queried less frequently space with worse read than newer one which are queried more frequently.
Another adentage is size of indexes if you're going to have any. Of course you need some logic to decide which table to query (for example procedure that returns ref cursor based on month passed to proc) but when you apply this manual decision either on application or db side your indexes will be smaller than one huge.
Next adventage when some period is more than 10 years you just drop table suffixed _1015 (I would advise suffix with month and year). No need of deletes, shrink and so on.

Upvotes: 1

Related Questions