Home » SQL & PL/SQL » SQL & PL/SQL » load/select data into subpartition
load/select data into subpartition [message #204001] Fri, 17 November 2006 05:15 Go to next message
mohanbvn
Messages: 1
Registered: November 2006
Location: INDIA
Junior Member


PLEASE HAVE A LOOK UPON MY PROBLEM - I MENTIONED CLEARLY

please reply me at the earliest.

I am having a quarterly table with 3 months data, it is partitioned and subpartitioned (16 subparititons) with 2 models

I have to load that data into Monthly table. i am creating a partition (sample)on the cycno. (this will be same for all data) and the model no.s will differ by 21 & 33 only. the whole data is divided by these 2 models only.

i am facing a problem of data loading. as when i am loading it is not storing in the specified partition.

i am using the queries as

create table abc(cycno number(6), model number(2), abc number(10)) partition by range(cycno) subpartition by hash(model) subpartitions 2
(partition P1 values less than(11))(subpartition sp1 values less than (22), subpartition sp2 values less than(34));

when i am loading the data it is saying as out of range or data is inserting into the main paritition but not into subpartition.

the cycno : 200512 model : 21 & 33

PLEASE REPLY ME AT THE EARLIEST.

THANKS IN ADVANCE
MOHAN

Re: load/select data into subpartition [message #204018 is a reply to message #204001] Fri, 17 November 2006 06:08 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I'm very surprised that syntax works. VALUES LESS THAN is a clause used with range partitions, but you have used it in HASH sub-partitions.

With HASH sub-partitions, you do not decide which values go into which sub-partition. Oracle decides based on the hash value of the partition column.

The correct syntax for hash subpartitions is

create table abc(
  cycno number(6)
, model number(2)
, abc number(10)
) 
partition by range(cycno) 
subpartition by hash(model) 
subpartitions 2
(partition P1 values less than(11));


List sub-partitions should solve your problem (available 9i and above).


create table abc(
  cycno number(6)
, model number(2)
, abc number(10)
) 
partition by range(cycno) 
subpartition by list(model) 
subpartitions 2
subpartition template
(
  subpartition sp1 values (21)
, subpartition sp2 values (33)
)
(
  partition P1 values less than(11)
);


Ross Leishman
Previous Topic: How can I find records which contains: '
Next Topic: ORA-06503: PL/SQL: Function returned without value
Goto Forum:
  


Current Time: Sat Dec 03 05:37:51 CST 2016

Total time taken to generate the page: 0.05344 seconds