Re: Frequency of Validating Index Structures

Larry,
I shall comment on parallel rebuilding of indexes. Upto 7.3.4.3 Hp-UX 10.20 if your were rebuilding indexes with parallel clause it was resulting in ORA-8102.It was applying to only concatenated(index for more than one field).If it was having only one field index no problem at all.So I was avoiding it rebuilding by using parallel. Work around to get rid off ORA 8102 todropand recreate such indexes. Now they have fixed in 7.3.4.4/5 HP-UX 10.20.I tried rebuilding in parallel after installing this patch and not found any error. To track such error you have to analyze such tables(not indexes) with validate structure. It takes long if table is large like 5/6 GB and also place table lock while analyzing.
HTH
Regards
Rafiq

From: larry elkins <elkinsl_at_flash.net>
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Subject: Frequency of Validating Index Structures Date: Fri, 26 Jan 2001 17:15:27 -0800

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 Sun Jan 28 2001 - 09:20:46 CST