Solved

Can I force oracle to rebuild index from table !

Posted on 2002-04-25
8
2,361 Views
Last Modified: 2010-08-05
I a having a problem of corrupt index. I tried rebuild option but is still giving problem. After reading the usage I realised rebuild does not refer to data table but creates index from existing index.
any ideas ? how can I refer to table to rebuild the index orI have to drop and recreate it (which i don't want to do)
thanks
0
Comment
Question by:anil27
8 Comments
 
LVL 3

Accepted Solution

by:
p_yaroslav earned 50 total points
ID: 6967862
Hi!

Usually you have to drop and recreate corrupted index.
Ihave like problem on Oracle 8.0.5. Only after recreating index - all become OK!



Best regards!
Yaroslav.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 6967864
Building indexes Oracle tries to use existing indexes. If you want to get a brand new index drop the corrupted index. The index creation will take more time as yet, but your new index will reflect the current situation. To help Oracle to index the table fast use big temparary tablespace for the implicit sorting which Oracle does indexing tables.
0
 

Author Comment

by:anil27
ID: 6968099
thanks !
I have dropped and recrested it and the problem is solved.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:DrSQL
ID: 6968582
anil27,
   In your comment (and it was very nice of you to provide feedback) you said that Yaroslav's answer solved your problwm, yet you graded it with a B.  Was this a mistake?  If so, we can get a moderator to fix it.  Remember, that the grades are to indicate whether or not the posted information directly led to your solution.  It's not about how hard it was or whether or not you liked the answer.  Grades are important for those searching for solutions in the knowledge base - they probably won't look at a "B" answer and yet that answer solved your problem.  Please post one more time to clarify this for us.

Good luck!
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 6970336
There was a bug in older versions of Oracle8 (and maybe even up to 8.1.6). If an index already exists on a set of columns (say col1, col2, col3), and then you create an index that is a subset of that index (col1), Oracle will build the index by reading from the concatenated index, rather than the table. This is normal behavior. However, the bug was causing the index to be corrupted, and just doing a rebuild of the index would not fix it.  The only way to do it properly was to create the subset index first, then create the concatenated index afterwards, forcing Oracle to read from the table for each index.

Of course, if you just migrated to 8.1.7, the problem went away.

Andrew
0
 

Author Comment

by:anil27
ID: 6970389
thanks Andrew for the insight.
Regards
0
 

Author Comment

by:anil27
ID: 6970404
DrSql !
I have graded the answer as per my expectations and I have reasons for it :
The solution I accepted as answer is the one which actually worked and first posted so accepted. But this solution is not comprehensive as mentioned by scwertner the temporary tablespace may be required on some platforms. Moreover I asked for some insight into how can I make Oracle to look in to table again for reindexing which Andrew tried to answer.
I may be wrong but this is my perception about grading.
I hope I answered your concerns.
thanks & regards
0
 

Expert Comment

by:xrchen
ID: 11000077
by the way, how do you know the index is corruptted.
thanks,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 70
Error executing command from server 6 49
Extract the first word (before the , ) 2 48
what privileges needed for S2 for this function (Oracle 12c)? 3 21
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

815 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

14 Experts available now in Live!

Get 1:1 Help Now