split partitions
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: split partitions

Hybrid View

  1. #1
    Join Date
    Dec 2005
    Posts
    195

    split partitions

    I have a question in split partition. The table name is sales_by_region.

    CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,
    store_name VARCHAR(30), state_code VARCHAR(2),
    sale_date DATE)
    STORAGE(INITIAL 10K NEXT 20K) TABLESPACE sap_stage_data
    PARTITION BY LIST (state_code)
    (
    PARTITION region_east
    VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
    STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50)
    TABLESPACE sap_stage_data,
    PARTITION region_west
    VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
    PCTFREE 25 NOLOGGING,
    PARTITION region_south
    VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
    PARTITION region_central
    VALUES ('OH','ND','SD','MO','IL','MI','IA'),
    PARTITION region_null
    VALUES (NULL),
    PARTITION region_unknown
    VALUES (DEFAULT)
    );

    I am splitting the region_east partition into region_east_1 & region_east_2 as below. My question is, which values('CT', 'VA', 'MD') goes to region_east_1 and which values goes to region_east_2 partition. How oracle maps these values('CT', 'VA', 'MD') to two partitions(region_east_1 & region_east_2). Can any one please clarify?

    ALTER TABLE sales_by_region
    SPLIT PARTITION region_east VALUES ('CT', 'VA', 'MD')
    INTO
    ( PARTITION region_east_1
    PCTFREE 25 TABLESPACE sap_stage_data,
    PARTITION region_east_2
    STORAGE (NEXT 2M PCTINCREASE 25))
    PARALLEL 5;

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Are you sure this is a valid syntax? That for sure will work for hash partitioning but here I think you should specify at which value the partition should be split.

    Regards

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Listed values go to first partition then CT, VA and MD go to region_east_1
    All non listed values existing in original partition go to region_east_2

    By the way, your case study rings a bell... I'm almost sure I've seen than example in some place.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Sep 2001
    Posts
    200
    I was also looking a ask a question about partition split. I have this example that i saw somewhere out there...

    ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
    INTO (PARTITION my_table_part_1,
    PARTITION my_table_part_2);

    then follows this select/verification
    COLUMN high_value FORMAT A20
    SELECT table_name,
    partition_name,
    high_value,
    num_rows
    FROM user_tab_partitions
    ORDER BY table_name, partition_name;

    TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
    ----------- ------------ --------- ---------- ------------- ----------
    MY_TABLE MY_TABLE_PART_1 3 2
    MY_TABLE MY_TABLE_PART_2 MAXVALUE 2

    My questions is...where does the (3) as in At (3) above come from?
    I would have thought it comes from the HIGH_VALUE column of the table to be split?
    but my does not return anything..
    SQL> select high_value from user_tab_partitions where table_name ='AAS_AON_FCT_2004_Q4';

    no rows selected
    Thanks
    Last edited by ndisang; 04-02-2007 at 12:23 PM.
    Life is what is happening today while you were planning tomorrow.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    ndisang -- Your example looks like related to range-partitioning where you are creating partitions depending on some value range of the key.

    The number (3) in the example is the higher-value you admit in that specific partition
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Dec 2005
    Posts
    195
    PAVB, thank you for the answer.

  7. #7
    Join Date
    Sep 2001
    Posts
    200
    You are right PAVB...my table is range partition on date field.
    Thanks...
    Life is what is happening today while you were planning tomorrow.

  8. #8
    Join Date
    Dec 2005
    Posts
    195
    I have one quick question about split partition.

    My table is list partitioned table. I have default partition name called REST. Now i wanted to add new partitions, but due to the default partition, i am not able to add it. But default partition REST does not have any values. I mean, default partition is empty now. At this circumstances, i want to add a partition. As per my knowlege, the only option is, drop the default partition and add new partitions. Is there any other options to add new partition at this situation? If so, can any one provide a syntax to add new partition?

    Thanks

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You can't add partitions in the middle of your partition set... you have to split partitions.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Dec 2005
    Posts
    195
    Dear PAVB, I understand that i can not add a partition now. Can you please tell me the syntax for spliting the default partition. Again, default partition is empty. Now how can i split it? If possible, i need commands for this?

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