This content has been marked as final. Show 8 replies
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.
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
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.
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)
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;
Thanks for the examples.
But what about the data? How do you transfer the data from the original table to the new table?
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;
create table lilian
( a int,
b varchar2(20) );
REM Check that table can be reorg
REM Create an Interim Table
create table lilian_int
( a int,
b varchar2 (20),
d int );
Rem redefine the table
'a a, b b, 0 c, 1 d',dbms_redefinition.cons_use_rowid);
REM Apply some data
insert into lilian values (1,2);
REM Sync Changes
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('lmh', 'lilian', 'lilian_int');
DBMS_REDEFINITION.FINISH_REDEF_TABLE('lmh', 'lilian', 'lilian_int');
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?
It works with BLOBs