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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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
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?