Partitioned table needs Partioned indexes? [message #156998] |
Mon, 30 January 2006 10:40 |
TLegend33
Messages: 203 Registered: March 2005
|
Senior Member |
|
|
I created an empty partitioned table as a copy of my source table that was not partitioned. I was able to load the data from the source table into the target partitioned table. I then renamed the source table to *_old, and renamed the new partitioned table to the source tables name. The indexes for the original source table were still valid, so I assumed they would work for the new partitioned table as well. One problem that I'm running into is very slow performance that may be attributed to this new partitioned table. Do I need to partition the indexes for this partitioned table as well?
Thanks.
|
|
|
Re: Partitioned table needs Partioned indexes? [message #157007 is a reply to message #156998] |
Mon, 30 January 2006 12:05 |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
TLegend33 wrote on Mon, 30 January 2006 11:40 | I created an empty partitioned table as a copy of my source table that was not partitioned. I was able to load the data from the source table into the target partitioned table. I then renamed the source table to *_old, and renamed the new partitioned table to the source tables name. The indexes for the original source table were still valid, so I assumed they would work for the new partitioned table as well. One problem that I'm running into is very slow performance that may be attributed to this new partitioned table. Do I need to partition the indexes for this partitioned table as well?
Thanks.
|
Leave partitioning out of it for a moment: index only apply to the table the index is on. When you created a new table, it had no indexes, unless you created them. Even though you renamed things so they eventually had the same name, that doesn't matter; the objects are different from their current names. The indexes are associated with the object itself.
MYDBA > create table test (a number);
Table created.
MYDBA > create index test_idx on test(a);
Index created.
MYDBA > create table test_new as select * from test;
Table created.
MYDBA > alter table test rename to test_old;
Table altered.
MYDBA > select table_name, index_name from user_indexes where table_name like 'TEST%';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
TEST_OLD TEST_IDX
MYDBA >
When you renamed the table, the index "went with it".
|
|
|
Re: Partitioned table needs Partioned indexes? [message #157011 is a reply to message #157007] |
Mon, 30 January 2006 13:06 |
TLegend33
Messages: 203 Registered: March 2005
|
Senior Member |
|
|
Thanks! This is exactly what I was looking for. I renamed the indexes for the '_old' table, then recreated the indexes for the '_new' table. However, I do have a question regarding one of the indexes that is displayed with the
'select table_name, index_name, status from user_indexes where table_name = 'TABLE_NEW';
The index: SYS_IL0000013677C00026$$ has a status of 'N/A'. How can I correct this? I believe this would associated with the primary constraint on the table. Do I have to drop & recreate the table?
Thanks
[Updated on: Mon, 30 January 2006 13:08] Report message to a moderator
|
|
|
|
Re: Partitioned table needs Partioned indexes? [message #157024 is a reply to message #157018] |
Mon, 30 January 2006 15:19 |
TLegend33
Messages: 203 Registered: March 2005
|
Senior Member |
|
|
There was a LOB column in the table I renamed to _old. I changed the datatype to CLOB and created the _new table. That LOB column was not previously indexed, so I'm not sure either. If my index updates don't improve performace, I can always drop/recreate the table.
Thanks.
|
|
|
Re: Partitioned table needs Partioned indexes? [message #157028 is a reply to message #157024] |
Mon, 30 January 2006 15:42 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
>> That LOB column was not previously indexed,
It is the lobindex.
Please read more about lobs in documentation.
scott@9i > create table mytbale (c1 number primary key, c2 blob);
Table created.
scott@9i > select table_name,index_name from user_indexes;
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
MYTBALE SYS_C001796
MYTBALE SYS_IL0000007570C00002$$
[Updated on: Mon, 30 January 2006 15:43] Report message to a moderator
|
|
|
|
|