This discussion is archived
8 Replies Latest reply: Apr 23, 2008 1:13 AM by 325351 RSS

Data partitioning & Index partitioning

user629987 Newbie
Currently Being Moderated
Hi,

I have the following questions about implementing ILM on a existing table consisting of millions of rows and with index partitioning...

1) how can data be partitioned when index partitioning exists?
2) what happens to the indexes when the table data is partitioned (per month)?
3) when the data is partitioned, will the indexes AUTOMATICALLY reorganize according to the data partitions? otherwise, what has to be done to the index partitioning?

Thanks.
  • 1. Re: Data partitioning & Index partitioning
    459761 Newbie
    Currently Being Moderated
    1) how can data be partitioned when index partitioning exists?

    When you partition a table that has indexes, the indexes can either be made global (one index spanning all partitions) or local (one index per partition). For ILM purposes, you would generally choose local indexes so that the indexes can migrate through storage tiers along with the data partitions. Moreover, global partition maintenance during storage tier migration can be expensive. There can be other reasons to consider a global index. But for ILM, local partitions are generally preferred.

    2) what happens to the indexes when the table data is partitioned (per month)?

    I believe I've answered this above.

    3) when the data is partitioned, will the indexes AUTOMATICALLY reorganize according to the data partitions? otherwise, what has to be done to the index partitioning?

    Yes. However, local index reorganization is more efficient than global reorganization.
  • 2. Re: Data partitioning & Index partitioning
    user629987 Newbie
    Currently Being Moderated
    Hi,

    1) What are actually the steps to partition an existing table with indexes, if you want to implement range partitioning with local indexes?

    2) Do you first need to delete the existing indexes, then implement range partitioning on the table with the Alter Table statement, and then create the local indexes per partition?

    3) Could you give me an example of an sql script to do this (implement range partitioning on the table with existing indexes, and making those indexes local)?

    I'm implementing ILM manually, not using ILM Assistant
    Thanks.
  • 3. Re: Data partitioning & Index partitioning
    325351 Newbie
    Currently Being Moderated
    If you look in the VLDB book in 11g or the Data Warehousing Guide in 10gR2 you will find lots of examples showing you how to perform these tasks.

    If you want to convert your table to partitioned and do it without affect access to the table then look at using online reorganization.

    Lilian
  • 4. Re: Data partitioning & Index partitioning
    629346 Newbie
    Currently Being Moderated
    Hi,

    The best way to do this would be to create a new table with the desired partitioning and build a local index on this. If it is possible to take the table offline and recreate all its dependencies manually, then the new table can simply be renamed to the old table. If the table needs to stay online, you may want to look into using online redefinition with dbms_redefinition. Below I have shown a sample of how you could recreate the table and index:

    Original definitions -

    create table my_test_table (id number, val number);

    create index my_test_table_idx
    on my_test_table (id)
    global partition by range (id)
    (partition p1_ind values less than (5),
    partition p2_ind values less than (maxvalue));

    New definitions -

    create table my_test_table_new (id number, val number)
    partition by range (id)
    (partition p1_tab values less than (5),
    partition p2_tab values less than (maxvalue));

    create index my_test_table_idx_new
    on my_test_table_new (id)
    local;

    Renaming -

    alter table my_test_table rename to my_test_table_old;
    alter table my_test_table_new rename to my_test_table;
    alter index my_test_table_idx rename to my_test_table_idx_old;
    alter index my_test_table_idx_new rename to my_test_table_idx;
  • 5. Re: Data partitioning & Index partitioning
    user629987 Newbie
    Currently Being Moderated
    Hi,
    Thanks for the examples.
    But what about the data? How do you transfer the data from the original table to the new table?
  • 6. Re: Data partitioning & Index partitioning
    325351 Newbie
    Currently Being Moderated
    If the tables are the same format you can transfer the data using the

    insert /*append */ into new_orders select * from orig_orders ';

    If you want to do it online here is an example

    reate user lmh identified by lmh;
    grant dba to lmh;
    grant create session to lmh;

    connect lmh/lmh;
    create table lilian
    ( a int,
    b varchar2(20) );

    REM Check that table can be reorg
    BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('lmh','lilian',
    dbms_redefinition.cons_use_rowid);
    END;
    /


    REM Create an Interim Table
    create table lilian_int
    ( a int,
    b varchar2 (20),
    c int,
    d int );

    Rem redefine the table
    BEGIN
    DBMS_REDEFINITION.START_REDEF_TABLE('lmh', 'lilian','lilian_int',
    'a a, b b, 0 c, 1 d',dbms_redefinition.cons_use_rowid);
    END;
    /

    REM Apply some data
    insert into lilian values (1,2);
    commit;

    REM Sync Changes
    BEGIN
    DBMS_REDEFINITION.SYNC_INTERIM_TABLE('lmh', 'lilian', 'lilian_int');
    END;
    /

    BEGIN
    DBMS_REDEFINITION.FINISH_REDEF_TABLE('lmh', 'lilian', 'lilian_int');
    END;
    /
  • 7. Re: Data partitioning & Index partitioning
    user629987 Newbie
    Currently Being Moderated
    Hi Lilian,

    Thanks a lot for the examples!

    I know that the online-redefinition process cannot be used for columns of LONG type....how about for columns with BLOB content?
  • 8. Re: Data partitioning & Index partitioning
    325351 Newbie
    Currently Being Moderated
    It works with BLOBs

    Lilian