Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle 9204
DA Morgan wrote:
> Oradba Linux wrote:
>
>> DA Morgan wrote: >> >>> Oradba Linux wrote: >>> >>>> Our DB is primarily used as an OLTP. We are planning to implement >>>> partitioning on some of the tables.We have to decide on the >>>> partitioning key. Can partitioning key be the sequence which is the >>>> primary key for the non-partitioned table? Are there any pros/cons >>>> with this approach? Have you been able to avoid global indexes in an >>>> OLTP DB ? >>> >>> >>> >>> What kind of partioning and why? >>> Range partitioning? Not likely to be of value. >>> List partitioning? Definitely not. >>> Hash partitioning? >>> >>> Are you partitioning for performance of maintainability? >> >> >> Partitioning for both. It will be a range partition. >> Why would range partition not be of value.
Gulp. Surely you jest?
Create table X (blah blah blah)
partition by range (ID)
Partition P1 values less than 1000
Partition P2 values less than 2000
etc etc etc.
Of course you can range partition on a sequence. You can range partition on anything if you can define a 'less than' clause that makes sense. Is it very sensible to do so? Different question.
To answer the poster's earlier question: why would range partitioning not be particularly useful for a sequence? Because inserting rows via their sequence means you will be clobbering to death a partition at a time. 10,20,30,40,50... these will all be fighting for insert rights into partition one. Then you move on to the 2010, 2020, 2030, 2040 entries, and these are now all going to fight for entry into partition 2.
Contention is the major drama with sequences, in other words, and range partitioning does nothing to resolve it. Hash partitioning on the other hand says 'take a value, do some magic with it, and determine a storage location based on the outcome'. So ID 10 might end up in partition 6, and ID 20 might end up in partition 1 and ID 30 might end up in partition 3... "adjacent" sequence numbers now get (physically) distributed all around the partitions: contention issue resolved (assuming different partitions are housed in different tablespace and on different hard disks, that means an I/O contention problem has been resolved too).
But is it syntactically impossible to do range partitioning on a sequence? No.
Regards
HJR
> And the type of partitioning chosen depends on the
> purpose.
>
> I would suggest you hit http://tahiti.oracle.com and read the concept
> docs on partitioning. Then reask your question when you better know
> what it is you are trying to accomplish.
>
Received on Fri Nov 12 2004 - 16:19:20 CST