Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle 9204

Re: oracle 9204

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 13 Nov 2004 09:19:20 +1100
Message-Id: <419536b8$0$2676$afc38c87@news.optusnet.com.au>


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.

>
> How exactly would you propose to range partition based a sequence?
> In other words ... what you are writing here is mutually exclusive
> in normal usage. You can hash partition based on a sequence but not
> range partition.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US