when to Partition ?

[rshivagami]

Hi all, Need some advice here - the project that I am working on is going live this fall and we do have some estmiates on the usage but are not sure of the rate of the data growth. Is it wise to partition the table right now on intuition or is it better to wait to see how it is growing and use that as a guideline in deciding the partitions ? These particular tables that I have in mind will be used for OLTP & some online repor

[marist89]

depends. If you don't know how you will ultimately partition the tables, how can you pick a partition key?

[tamilselvan]

Partition does NOT work very well with high volume OLTP.

Tamil

[rshivagami]

I meant to write - "These particular tables that I have in mind will be used for OLTP & online reporting"....
Tamil, but the partition will help reporting process though....is there any other method that could benefit a table used for both OLTP & reporting ? we are estimating that these tables can end up having about 10 million records..

Shiva.

[rshivagami]

I meant to write - "These particular tables that I have in mind will be used for OLTP & online reporting"....
Tamil, but the partition will help reporting process though....is there any other method that could benefit a table used for both OLTP & reporting ? we are estimating that these tables can end up having about 10 million records..

Shiva.

[pando]

well only range partitioning will benefit your reports and range partitioning needs some meaningful partition key in order to use partition elimination

if you dont know the key we dont neither

[tamilselvan]

Shiva,

You must decide what you want - online perf (high volume inserts) or reporting query perf.

You can't have both.

Choose one. Let me know it.

Tamil

[marist89]

Originally posted by rshivagami
we are estimating that these tables can end up having about 10 million records..

10 Million records? I thought you were talking about a big table. I wouldn't worry about partitioning for only 10 Million rows.

[jodie]

Originally posted by tamilselvan
Partition does NOT work very well with high volume OLTP.

Tamil

Tamil -
Can you explain why Partitioning is bad with OLTP?

I haven't yet had the opportunity to use partitioning - none of our db siizes could justify the cost. However, we have a new app coming in and they request it. The table that will need it has ~80 Million rows and will be about 4 Gig in size. It's mainly an OLTP system - but not HUGE volume. The entire DB is refreshed from the mainframe once a week, takes daily inserts and updates, and has a batch update process every night. The batch refresh is done using SQL*Loader.

Does the benefit of the load offset the problems with OLTP - in your opinion?

Thanks for your input...
Jodie

[tamilselvan]

In a HIGH volume OLTP inserts on a partitioned table and on partitioned indexes, oracle has to do MORE work.
1 Find out the partition key value
2 Find out the appropriate table partiton where the row should go
3 Find out the appropriate partition from the indexes where the key valeus should be inserted.

In a normal heap table, Oracle will do less work for inserts.

I am not against partition as many of you guys think. In fact I use partition very cautiously.

Basically, partition is defined for DSS/DW system.
Even in OLTP, many systems use hash partition and global indexes. You need to evaluate your system carefully. What are the pros and cons of using partition?

I give a real life example:
There are 5500 users using a system. The system runs from 7 am to 8 PM Monday to Friday. There is a table, say tableX. On an average the table gets 15 Million rows per day. We decided to keep 3 days worth of data at any time on the table. That means, 45 Million rows.
On the 4th day, we delete 3 days older data. B/c of heavy concurrent inserts on the table during the day time, initially I thought hash partition with global indexes would be OK. But on the test, insert took longer time. So I changed it to a normal heap table with appropriate freelists and freelist groups. And the perf went up.

Bottom line:
If you do not know how many insert/update/delete are going to happen on the table, then you do not partition it.

Tamil