Partitioned Tables in Oracle 8.1.6

[THoey]

Has anyone used these yet? I have a DBA that is trying to push us into using them. Just curious if they are worth their effort. Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[carp]

Terry -
As usual, "it depends"!

We've found partitioning helps tremendously with very large tables, especially if they're indexed appropriately. In some cases, response time has literally been cut from close to an hour to a matter of about a minute just by partitioning. This would be especially so in the case of something like a data warehouse.

 

[MikeJones]

I've used them to partion a table of around 11 million rows, growing by around 50,000 daily. Originally we partioned on a subset of the primary key, but then found that some partions grew much faster than others and meant that some data was comparitively slower to access than others. When I left we were looking into using hash partioning to solve this.

On another note about speed, I can only totally agree with Carp. We had a Global index across all partions, which when doing a very small range scan (on 50 rows) was still slower than doing full scans, in parallell, on all the local partioned indexes.

We were very pleasently surprised by partioning...