Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Frequency of Validating Index Structures
Listers,
How frequently, if at all, do various DBA's validate index structures?
When helping a developer today trouble-shoot an odd production report problem (strange numbers), it became apparent when working the data and validating the results of the report that an index being used was "corrupt" (using a specific value for the column and using it's index for retrieving data brought back 3 rows -- forcing a full table scan with the same criteria brought back 7 rows). I brought this up with the only DBA still there and he went ahead and did an analyze table validate structure cascade after business hours. The analyze reported problems and a trace file was generated.
So, the DBA asked me how frequently I would recommend validating the indexes. Heck, I don't know -- I'm not a DBA. Though I still do some tasks that many would consider DBA type of tasks, I haven't been a "real" DBA in years. I am primarily a developer with a heavy bent toward application and SQL tuning. I've only come across this maybe 4 or 5 times over the past few years. I also still see the occasional trace file with the 8102 error.
Anyway, the database is 7.3.4.5 on HP-UX 11.0, and, the DBA was wondering if they should setup a routine schedule for using the validate structure cascade against the various tables/indexes. It *sounds* like a decent idea, but, I told him I would pose this question to the list and see what people would have to say.
I swore to myself that I was going to keep this short, but, on a side note, I believe the DBA said they use the parallel rebuild technique when rebuilding indexes. I'm not sure what their criteria is for *when* they decide to rebuild an index. But, it seems like I remember hearing someone mention on the list potential issues with parallel rebuild on 7.3 A quick search on Metalink turned up enough issues to make me wonder if a parallel rebuild should be used in 7.3. I also wonder if that could be a cause of the Received on Fri Jan 26 2001 - 19:17:05 CST
![]() |
![]() |