Re: conceptual question regarding partitioning

"Also, I'm not too sure whether the account_id is too good a partition column. You could potentially have 1000s account ids, resulting in 1000s partitions. This will become unmanageable. When partitions were originally introduced, an Oracle instructor advised to use them for date ranges *only* IMHO this is a sensible approach, and I'm not looking forward to use partitions on floating data like ids. I'm also sure from the performance perspective you don't gain much. "

We would never have 1000's of partitions because when we implement the hash, we would fix the number of partitions that oracle would hash on.

As for your comment about not gaining much performance wise. Can you elaborate on this? Everything I've read seems to tell me I have much to gain.

If I have 100 accounts, and 10 partitions. That means each partition will have the data for only 10 accounts using the hashing strategy.

When a query comes in to select the data for a specific account, the scan to retrieve that data would only have to weed through 10 accounts worth of data (in a single partition) to find the results rather than 100 accounts worth of data if the table were not partitioned at all.

The Oracle book I have (complete reference) states that Partition pruning used in conjunction with the Cost Based Optimizer can eliminated uneeded partitions from being scanned.

I must admit, I am a database application developer and not a true DBA. (Working in small startup and there is no DBA so I'm filling the role for now).

I agree with you that combining the logical and physical schema should not be done. That's why I was hesitant.

We're talking about performance of a web site that's gonna have oracle on the backend. Performance is so critical. Performance, performance, performance. It could mean success or failure of the business. That's the only reason I would even consider this.

Thanks,

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!