Link to home
Start Free TrialLog in
Avatar of luyan
luyan

asked on

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?  
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

<If one partitioned table has one partitioned index, can I drop the partitioned index then recreate it?
yes


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?

ASKER CERTIFIED SOLUTION
Avatar of oleggold
oleggold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.

Thanks