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

Thread: partition question

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    partition question

    I have attached a small example of maintaining partitions.

    Please try it, and you will see what I have a beef with when you do the last step...i.e. add a partition.

    When a new table partition is added, the index partition has the same name as the table partiton, and the index partition resides in the table partition tablespace.

    My Question is, how can I specify the index partition name when I add a new table partition. Also, how do I set the index partiton name to the correct one.

    All I can think of now to do is, once the table partition is added, to either rebuild the index, with the correct partition name, and tablespace. Or drop the index partitions, and re-add it !! There has to be a better way ?

  2. #2
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    here is the file (I guess it cannot be .sql) !
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You have to modify the default attributes of the index. See http://www.dbasupport.com/forums/sho...threadid=17394
    Jeff Hunter

  4. #4
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Never mind...

    I got the answer:

    Adding Index Partitions
    You cannot explicitly add a partition to a local index. Instead, a new partition is added to a local index only when you add a partition to the underlying table.

    Specifically, when there is a local index defined on a table and you issue the ALTER TABLE statement to add a partition, a matching partition is also added to the local index. Oracle assigns names and default physical storage attributes to the new index partitions, but you can rename or alter them after the ADD PARTITION operation is complete.

    You can effectively specify a new tablespace for an index partition in an ADD PARTITION operation by first modifying the default attributes for the index. For example, assume that a local index, q1_sales_by_region_locix, was created for list partitioned table q1_sales_by_region. If before adding the new partition q1_nonmainland, you had issued the following statement, then the corresponding index partition would be created in tablespace tbs_4.

    ALTER INDEX q1_sales_by_region_locix
    MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_4;

    Otherwise, it would be necessary for you to use the following statement to move the index partition to tbs_4 after adding it:

    ALTER INDEX q1_sales_by_region_locix REBUILD PARTITION q1_nonmainland TABLESPACE tbs_4;


    I thought there would be a way to add table partitions, and have the index partition get created (implicitly) where I want it, and with the correct partition name, but, I guess this has to be done in multiple steps .
    Last edited by khussain; 01-08-2003 at 12:34 PM.

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