• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 887
  • Last Modified:

Can I drop the partitioned index then recreate it?

If one partitioned table has one partitioned index, can I drop the partitioned index then recreate it?
The user said in Oracle it's quite different to create partitioned index before or after importing data. He said the query would run fast when creating partitioned index after importing data. Is it true?  
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
<If one partitioned table has one partitioned index, can I drop the partitioned index then recreate it?

if you have to load o lot of data, ie the full table data, it will be eventually be faster to drop the index before, and recreate if afterwards.
now, do you really load data from many/all partitions of that table?

I agree and Your client is right as index ,any part-d or not interfers with dml performed in importing the data so You'd better to drop the index ,to run the imort and then recreate the index.To be certain You're on the same page,thre's also a disable option for the index,so You could disable it first then import and enable
with a partitioned table, you can simply "drop index <index_name>" to remove the index.
creating an index is also no different from a normal create index statement.  it will result in a locally partitioned index with teh partitioning aligned to the partitions of the parent table.
Naveen KumarProduction Manager / Application Support ManagerCommented:
syntax is :
create index my_partition_idx on my_par_table ( part_column_name ) local;

This is to create a partitioned index whose partitioning will be same as that of the table.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now