Home » RDBMS Server » Server Administration » Partitioned table needs Partioned indexes?
Partitioned table needs Partioned indexes? [message #156998] Mon, 30 January 2006 10:40 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #157018 is a reply to message #157011] Mon, 30 January 2006 14:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>SYS_IL0000013677C00026$$
Not sure.
I guess it is a lob index. Is there a lob column in table?
Indices created by primary constraints have a different naming convention.

[Updated on: Mon, 30 January 2006 14:19]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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

Re: Partitioned table needs Partioned indexes? [message #157659 is a reply to message #157028] Fri, 03 February 2006 12:07 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I added another clob column to my table and the lobindex was created, but still presents a status of 'N/A'. I read that the lobindex is created whenever a lob column is created..I'm not sure why it would be invalid.

Thanks.
Re: Partitioned table needs Partioned indexes? [message #193124 is a reply to message #157659] Thu, 14 September 2006 23:57 Go to previous message
anurag078
Messages: 34
Registered: May 2006
Location: West Des Moines
Member

Please send us the SQL you run, to get the status as 'N/A' and it's complete output also, please.

Anurag
Previous Topic: How to rename a lob index created by Oracle ?
Next Topic: Proben in schemas - urgent
Goto Forum:
  


Current Time: Mon Dec 05 10:52:33 CST 2016

Total time taken to generate the page: 0.07305 seconds