Solved

reindexing in oracle 10g

Posted on 2007-12-05
4
5,066 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 20417225
>> 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
ID: 20417259
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 35

Expert Comment

by:Mark Geerlings
ID: 20420095
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
ID: 20421175
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

749 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