DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Problem in creating partition....(Very Urgent)

  1. #1
    Join Date
    Sep 2001
    Posts
    24
    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
    Shyla

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What are the inserted row's values?

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    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

  4. #4
    Join Date
    Sep 2001
    Posts
    24
    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]
    Shyla

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    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

  6. #6
    Join Date
    Sep 2001
    Posts
    24
    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
    Shyla

  7. #7
    Join Date
    Mar 2002
    Posts
    534

    Wink

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width