Re: Problem with Hash sub-partitioning

It would have been great if you can just "append" a partition into a table without knowing exactly how the table is organized partition-wise, much like direct-load insert into a table. That's not possible currently.

1. A range-list partitioned table would have been great, as you seemed to indicate deptid is a low cardinality column, which by the way is not suited for hash partition key. But multi-column partitioing is not supported by list, and you indicated you might use multi-columns for the subpartion key.
2. Any reason you did not use multi-column range partitioning, without subpartitions? From what you described in your post, a multi-column partition key on (ID, DEPTID) would have worked.
3. Depends on how the data in your temporary table (S, T) is created, consider if you can create them as hash-partitioned table. Make them "equi-partitioned" with your main table (X). You can then exchange a range-hash PARTITION from X with the whole hash-partitioned table.

"Yash" <yash_ganthe_at_hotmail.com> wrote in message news:<al9t40$2dv$1_at_news.vsnl.net.in>...
> 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
Received on Fri Sep 06 2002 - 23:47:53 CDT