Re: Problem with Hash sub-partitioning

Let me address your problem by walking
through a couple of questions:

Assume I have created a partition with 8 hash subpartitions. I have a row where the hash key is 77 - which of the subpartitions does it belong in ?

Answer - you can't tell.

Assume it belongs in first subpartition - add a subpartition (which splits one of the existing subpartitions).

Which subpartition will be split ?

Answer - the first one, to make subpartitions 1 and 9.

Where will the row with 77 be now ?

Answer - you can't tell without looking, although you know it has to be in the first or the ninth.

Hash (sub)partitions do not work the same was as range partitions. Adding a subpartition does not simply add an empty space for new data, it always splits an existing partition, and the new data has to go in the correct partition, which isn't necessarily the newly split one.

The fact that your first experiment worked was luck, not guaranteed behaviour.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______September 24/26, November 12/14

____USA__________November 7/9 (MI), 19/21 (TX)

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html








Yash wrote in message ...


>Hi,

>I have a table X(id number, name varchar2(20), location varchar2(20),

 deptid


>number). This is partitioned by range on LOCATION and further by DEPTID. I

>add a partition and a default subpartition to this table.

>I then get a non-partitioned table T , all DEPTID values of which are

 unique


>with value=1. The table has exactly the same schema as the X table. To get

>the data from this new table into X, I add a subpartition and exchange it

>with the table T, using the ALTER TABLE .... EXCHANGE SUBPARTION ....

>command.

>The table T is then dropped. I get another table S having the DEPTID value

>of 2 in all its rows. I follow the same process as above and exchange it

>with another subpartition.  This works fine. But if the table S is

 exchanged


>with a subpartition first, and table T later with another, I get an Oracle

>error saying that rows from the table did not qualify for being in the

>subpartition.

>

>The order in which I will have to exchage the tables is not fixed. My

>requirement is that I should be able to add a sub-partition and exchange

>with a table immediately after the table is created.

>How do I get around the problem? I am bent on using HASH subpartitioning as

>I might have to use multiple columns as the subpartitioning key.

>

>Please help

>Yash

>

>