Add/Drop Partitions
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Add/Drop Partitions

  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Add/Drop Partitions

    Hi,


    created a partition table with Global Partition indexing. I dropped a partition and created new partition without any index creations. The drop partition still left the partition index and didn't create a new index for the new added partition.

    SQL> SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS;


    INDEX_NAME PARTITION_NAME STATUS
    ------------------------------ ------------------------------ --------
    PTTRACKLOG_PAR_INDX PT1 UNUSABLE
    PTTRACKLOG_PAR_INDX PT3 USABLE
    PTTRACKLOG_PAR_INDX PT2 USABLE


    SQL> select table_name, partition_name from user_tab_partitions;

    TABLE_NAME PARTITION_NAME
    ------------------------------ ------------------------------
    ASH_PTTRACKLOG PT4
    ASH_PTTRACKLOG PT3
    ASH_PTTRACKLOG PT2

    From the above - PT1 partition is dropped and PT4 is added. PT1 index is still existing but unusable and PT4 partition didn't create any new index on PT4?
    If i drop a partition, should this also drop the corresponding partition index if the Global indexing is using?

    If a new parition is added, will this create a new partition index automatically, with the same name as new tabl partition?

    Should all the partition indexes needs to be rebulit or just the index on the new partition added?


    Thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    >> If i drop a partition, should this also drop the corresponding partition index if the Global indexing is using?

    There is no corresponding index partition for the table partition unless you have used local partitioning of the index.

    >>If a new parition is added, will this create a new partition index automatically, with the same name as new tabl partition?

    No -- that happens with local index partitioning.


    why did you choose to use global partitioning instead of local?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Global indexing means 1 index for the entire table. That index should have gone invalid and require a rebuild as soon as you dropped one of the partitions , unless you did a UPDATE INDEXES in the drop partition command.

    Adding a new partition always invalidates global indexes.

    Local indexes are not the same as partitioned indexes. If you create local indexes then Oracle will take care of creating or dropping the associated partitions of the index when the base partition goes away. If you just created a partitioned index on the table (ie not as a local index) then you have to do all the work.

    What create statement did you use for the index?
    Last edited by jhmartin; 11-01-2006 at 08:17 PM.

  4. #4
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Quote Originally Posted by slimdave
    why did you choose to use global partitioning instead of local?
    Because he finds it more appropriate for his need dear
    Does your question mean all the global indexing can be substituted
    by local indexing?
    Last edited by yxez; 11-01-2006 at 09:09 PM.
    Behind The Success And Failure Of A Man Is A Woman

  5. #5
    Quote Originally Posted by yxez
    Does your question mean all the global indexing can be substituted
    by local indexing?
    Local indexes must contain the partition key, and it is ideal if the leading column of the index IS the partition key. If you can't do that then local indexes won't work.

  6. #6
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Quote Originally Posted by jhmartin
    Global indexing means 1 index for the entire table.
    Hi dear....i forgot my partitioning knowledge because I haven't read
    it again for a long time. I have just created a partitioned table
    and a global index and did not bother about other options.

    What is the advantage/disadvantage again of a Global Index vs
    Global Partitioned Index vs a Local index in a Partitioned Table?

    If i relate it to a partitioned table:

    A. Non-partitioned, index by travel_date and fullname
    ---------------------------------------------------
    Select fullname from TRAVEL where travel_date='01-DEC-93'
    and fullname like 'BIN%LADEN%OSAMA%'

    B. Partitioned by travel_date, Global index by travel_date and fullname
    -------------------------------------------------------------
    Select fullname from TRAVEL PARTITION(TRAVEL1993)where
    and fullname like 'BIN%LADEN%OSAMA%'

    Which do u think runs faster? Or is partitioning just for ease
    in managing large tables and not really performance.

    Is it the same with Partitioned Indexes? They have same
    performance benefit with non partitioned index but only segment management differs.


    Thanks
    Last edited by yxez; 11-01-2006 at 11:28 PM.
    Behind The Success And Failure Of A Man Is A Woman

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    >> Global indexing means 1 index for the entire table.

    Global partitioned indexes do not mean that.

    >> Adding a new partition always invalidates global indexes.

    No, it never does.

    >> Local indexes are not the same as partitioned indexes. ... If you just created a partitioned index on the table (ie not as a local index)

    Rather a confusing explanation -- local and global are just two different types of partitioned indexes.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    I admit that they are both different kinds of partitioned indexes, but they differ in their behavior significantly so they shouldn't be generalized as just partitioned indexes. Local indexes have unique behavior in regards to table partition operations.

    Now that I think about it, you are correct that adding a partition wouldn't need to invalidate a global index. It is dropping that was the problem.

  9. #9
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Quote Originally Posted by jhmartin
    Local indexes must contain the partition key, and it is ideal if the leading column of the index IS the partition key. If you can't do that then local indexes won't work.
    So, if I have an EMP table(Hiradate,Fullname)
    partitioned by HireDate, I can only create a partitioned
    index on hiredate column, And can create only a global index for
    Fullname column.
    What is the difference in performance in I create a global index
    on Hiredate rather than a partitioned index? I guess none?
    Behind The Success And Failure Of A Man Is A Woman

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by yxez
    So, if I have an EMP table(Hiradate,Fullname)
    partitioned by HireDate, I can only create a partitioned
    index on hiredate column, And can create only a global index for
    Fullname column.
    no -- if you partition the index on the same key as the table with the same intervals then you have a locally partitioned index. If you partition on a different column you have a global partitioned index.

    What is the difference in performance in I create a global index
    on Hiredate rather than a partitioned index? I guess none?
    There is no magic performance boost, it all depends on how you use the data. Also this is all very well documented.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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