OraFAQ Forum: Client Tools » Problem with local indexes in a Partition

Home » Other » Client Tools » Problem with local indexes in a Partition

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with local indexes in a Partition [message #25863]

Wed, 21 May 2003 14:19

sriram
Messages: 58
Registered: September 2000

Member

I have a large fact table for my data warehouse. It has partitions and I created local indexes to speed up the query process.
Is there anyway to disable or delete the local index and still load the data using insert statements in Oracle 9i? This way it takes less than to
load the data and then I can enable them or rebuild them?

Thanks

Sriram

Report message to a moderator

Re: Problem with local indexes in a Partition [message #25869 is a reply to message #25863]

Wed, 21 May 2003 18:29

Mike
Messages: 417
Registered: September 1998

Senior Member

You can either use the command:
ALTER INDEX cost_ix DROP PARTITION p1;

Or you could also first load your data in a temp table and then exchange the table partitions. For more details have a look at:
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96521/partiti.htm#11985

HTH
Mike

Report message to a moderator

Re: Problem with local indexes in a Partition [message #25876 is a reply to message #25869]

Thu, 22 May 2003 07:51

Mike
Messages: 417
Registered: September 1998

Senior Member

The alter command does not work for a local index only for a global one. Any thoughts?

Sriram

Report message to a moderator

Re: Problem with local indexes in a Partition [message #25877 is a reply to message #25869]

Thu, 22 May 2003 07:55

sriram
Messages: 58
Registered: September 2000

Member

ALTER INDEX cost_ix DROP PARTITION p1
works only with global indexes not with local indexes. Any ideas how to drop a local index for a partition or make a local index for a partition unusable ?

Thanks

Sriram

Report message to a moderator

Re: Problem with local indexes in a Partition [message #25912 is a reply to message #25877]

Mon, 26 May 2003 04:15

Mike
Messages: 417
Registered: September 1998

Senior Member

Sorry that I have given you that wrong information.

A way around this index problem would be to first load your data in a temp table create the required indexes and then exchange the table partitions. For more details have a look at:
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96521/partiti.htm#11985

Report message to a moderator

Previous Topic:	dynamic insert stat
Next Topic:	how does non english characters get store in oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 19:32:05 CDT 2024