Can I drop the partitioned index then recreate it?

Posted on 2007-10-17
Medium Priority
Last Modified: 2008-01-09
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?  
Question by:luyan
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20096352
<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?

LVL 21

Accepted Solution

oleggold earned 1500 total points
ID: 20097040
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
LVL 18

Expert Comment

ID: 20098442
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.
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20099415
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.


Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question