-
I want to create a partitioned table on two columns.
So i created range partition. But when i inserted records, the values are not storing in a proper partitions. Pl. help to create the correct partitioned table....
It is very urgent.
Thanks in advance...
Here is the create script. Very Very urgent....
create table abc1 (countrycode varchar2(2), mis_date date)
partition by range (mis_date, countrycode)
(partition AU_200202 values less than (to_date('01-MAR-2002', 'DD-MON-YYYY'), 'AV')
storage(initial 40960 next 40960) tablespace data,
partition HK_200202 values less than (to_date('01-MAR-2002', 'DD-MON-YYYY'), 'HL')
storage(initial 40960 next 40960) tablespace data,
partition AU_200203 values less than (to_date('01-APR-2002', 'DD-MON-YYYY'), 'AV')
storage(initial 40960 next 40960) tablespace history,
partition BH_200203 values less than (to_date('01-APR-2002', 'DD-MON-YYYY'), 'BI')
storage(initial 40960 next 40960) tablespace history)
Regards,
S.Kumar
-
What are the inserted row's values?
-
Hi,
I guess that you will have almost no date in the partitions HK_200202 and BH_200203 but all data in the partitions AU_200202 and AU_200203.
As far as I know (I had no time to test it) it is due to the following:
When a table is partitioned by range on multiple columns, Oracle first checks the first condition. In your case it is "<= than (to_date('01-MAR-2002', 'DD-MON-YYYY'))" and if this is true it doesn't check the next one " <='AV' ".
So when we have a value like ('02-MAR-2002','BI') it does the follwing:
1) The first condition of the first partition '02-MAR-2002'<='01-MAR-2002' is not correct and so jumps to the next partition
2) the second partition in your case has the same first condition and so will not be correct either and so jumps to the next partition
3) the first condition of the third partition will be correct and so stores the data there.
One case were you will get the wanted result is with ('01-MAR-2002','HL')
For details and a more mathematical explaination see:
http://otn.oracle.com/docs/products/...rti.htm#430096
In your case I would use range partitioning over the date and hash for the countrycode which would solve your problem.
Hope that helps
Mike
-
Hi,
The rows i inserted was ,
insert into abc values('AU','10-FEB-2002');
insert into abc values('AU','10-MAR-2002');
insert into abc values('BH','10-FEB-2002');
insert into abc values('BH','10-MAR-2002');
In this All the rows which has AU are stored in AU_200202 partition and All the rows which has BH are stored in BH_200202 .
Is there any other way to have a composite partition.
And when i delete a partition the latest mis_date and the countrycode column only should get deleted.
For ex: countrycode = 'AU'
mis_date = '10-MAR-2002'
If i delete a partition this values alone have to be deleted.
Can i use list partition with any other method. Help to create a composite partition.
When i delete the data. I should only give the partition name.
I should not use the columns countrycode and mis_date.
Thanks in Advance.
Regards,
S.Kumar
[Edited by shiala_100 on 04-16-2002 at 02:51 AM]
-
Hi again,
In 9i Release 1 it is not possible to combine list and range partitioning. As far as I know it will/should be possible in Oracle9i Release 2.
Mike
-
Hi,
Is there any other way we can handle both the columns
(countrycode & mis_date). I will insert the records countrycode and date wise.
But one problem is, i want to delete the latest composite partition countrycode & mis_date.
Help me to find out some solution on this.
Thanks & Regards,
S.Kumar
-
Hi,
One way which should work is to first set the countrycode and then the date.
You will need for each country_code a set of partitions.
When a table is partitioned by range on multiple columns the condition is not "less" but "less or equal" that's why I replaced '01-MAR-2002' by '28-FEB-2002 23:59:59'.
I am not sure, and have not tested, if in this case oracle can optimise it's querys with partition pruning if you select all data over a certain time periode.
Below you find a script which seems to work fine.
Good luck
Mike
drop table abc1;
create table abc1 (countrycode varchar2(2), mis_date date)
partition by range (countrycode, mis_date)
(
partition AV_200202 values less than ('AV', to_date('28-FEB-2002 23:59:59', 'DD-MON-YYYY HH24:MI:SS')),
partition AV_200203 values less than ('AV', to_date('31-MAR-2002 23:59:59', 'DD-MON-YYYY HH24:MI:SS')),
partition BH_200202 values less than ('BH', to_date('28-FEB-2002 23:59:59', 'DD-MON-YYYY HH24:MI:SS')),
partition BH_200203 values less than ('BH', to_date('31-MAR-2002 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
);
insert into abc1 values('AV', to_date('01-FEB-2002', 'DD-MON-YYYY'));
insert into abc1 values('AV', to_date('01-MAR-2002', 'DD-MON-YYYY'));
insert into abc1 values('BH', to_date('01-FEB-2002', 'DD-MON-YYYY'));
insert into abc1 values('BH', to_date('01-MAR-2002', 'DD-MON-YYYY'));
SQL> select * from abc1 PARTITION(AV_200202);
CO MIS_DATE
-- ---------
AV 01-FEB-02
SQL> select * from abc1 PARTITION(AV_200203);
CO MIS_DATE
-- ---------
AV 01-MAR-02
SQL> select * from abc1 PARTITION(BH_200202);
CO MIS_DATE
-- ---------
BH 01-FEB-02
SQL> select * from abc1 PARTITION(BH_200203);
CO MIS_DATE
-- ---------
BH 01-MAR-02
