[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5086
  • 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
 
sujith80Commented:
>> 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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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