Solved

reindexing in oracle 10g

Posted on 2007-12-05
4
5,059 Views
Last Modified: 2013-12-18
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
Comment
Question by:spal10
  • 2
4 Comments
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
>> 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
 
LVL 11

Expert Comment

by:mohammadzahid
Comment Utility
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
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
 
LVL 11

Accepted Solution

by:
mohammadzahid earned 75 total points
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now