reindexing in oracle 10g

I have a table of 20 million records. i want to reindex the whole table. i can't reindex in one shot. i am planning to do that by reindexing every thousands of records. Is there any other way which will not affect the performance?
spal10Asked:
Who is Participating?
 
mohammadzahidCommented:
You can create index using NO LOGGING option. this will speed up index creation. Only downside for using this option is that there is no redo log generated during index creation.
0
 
SujithData ArchitectCommented:
>> reindex the whole table
YOu mean create another index on the table? Or re-build the index.

However, there are no ways to index a subset of your records.

Is your table partitioned? If so, you can re-build the local indexes on specific partitions.
0
 
mohammadzahidCommented:
Is your data from table updated, deleted frequently? If not, then I suggest you create Index Organized table and move data in new table.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Oracle does not allow us to build an index on only part of table's records.  You can only create an index on the entire table.  Yes, if you have an index that is already partitioned, you can move or rebuild individual partitions.  But you cannot create just one partition at a time of a new index.

No, there is no way to rebuild or move existing indexes without affecting performance.  And no, there is no way to create a new index without affecting performance.

What is the problem or error that prevents you from re-indexing the table now?
0
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.

All Courses

From novice to tech pro — start learning today.