Closed Thread
Results 1 to 3 of 3
  1. 04-20-2001 07:18 AM #1
    Fiona
    Fiona is offline Junior Member
    Join Date
    Nov 2000
    Location
    London
    Posts
    83

    Smile

    I have a table which has partitions. What I want to do is to create index for each partition, one at the time.

    In the past I have used the syntax:

    CREATE BITMAP INDEX IDX_FACT_INV_TIME ON
    GLX_FACT_INVENTORY(TIME_KEY)
    LOCAL
    (
    PARTITION P1037 TABLESPACE FACT_INV_01,
    PARTITION P1121 TABLESPACE FACT_INV_02,
    PARTITION P1191 TABLESPACE FACT_INV_03,
    PARTITION P1254 TABLESPACE FACT_INV_04,
    PARTITION P1290 TABLESPACE FACT_INV_05,...etc
    );

    to create the index.

    I cannot find any other syntax for creating index for partitioned table, is it even possible to create index for one partition at the time?


    Thanx for your help in advance!
    Fiona
  2. 04-20-2001 08:47 AM #2
    tomate
    tomate is offline Member
    Join Date
    Mar 2001
    Posts
    188
    You are create for all partitioned table an index because you give the word 'local' on.

    Index offer a table, which is partitioned.( offer all):

    create
    on (col1, col2,...);




    Index of each patitioned tablepart:

    create
    on
    (col1, col2,...)
    local;



    Index has an own partitioned methode independent of the table

    create
    on
    (col1, col2,...)
    global index by range (col)
    partition p1 values less than 500,
    partition p1 values less than 2500
    ....


    I hope you have understaand it.

    Thomas Schmidt
  3. 04-20-2001 09:16 AM #3
    nealh
    nealh is offline Member
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    If the index is local, then its partitions are tied into those of the table and you can't create them separately. You can, however, switch them off and rebuild them separately using syntax such as:

    alter index modify partition unusable;

    and

    alter index rebuild partition part_name;

    I've found this quite a useful feature because you can switch off indexes during loads of partitions, which speeds up the loads but doesn't mean you have to rebuild the entire index.


    4. « Previous Thread | Next Thread »

    Bookmarks

    Posting Permissions

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

    Forum Rules

    Click Here to Expand Forum to Full Width