Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5088
  • Last Modified:

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?
0
spal10
Asked:
spal10
  • 2
1 Solution
 
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
 
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
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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