RE: Frequency of Validating Index Structures

Larry,
Thanks for further input. Whatever I described in my message was based on my personal experience which I faced with 7.3.4.3 of HP-UX 10.20. I used parallel clause with 7.3.4.0 NCR UNIX and had no problem at all except with BITMAP indexes only even not using parallel explicitly but if table degree is > 1 then it turns into ORA-600.

Regards
Rafiq

From: larry elkins <elkinsl_at_flash.net>
To: <ORACLE-L_at_fatcity.com>
CC: <rafiq9857_at_hotmail.com>
Subject: RE: Frequency of Validating Index Structures Date: Sun, 28 Jan 2001 12:45:32 -0600

Rafiq,

Thanks for taking the time to reply. I found Metalink documents on the 8102 error describing the concatenated issue, as well as some others, but, they don't apply exactly to this situation. In this case it is a single column numeric primary key, and, it is not a part of any other concatenated indexes on the table. Anyway, the DBA's have a few Metalink documents on resolving 8102 problems and continue to work on it. The simplest approach that you hope works is dropping and rebuilding the index. This doesn't seem to have solved their problem. Whenever this pops up, it is always on the same statement on the same table (a delete using the PK). Unknown is if this happens every time or is just for one (or more) values.

And when I think about it, their development, testing, education, and QA databases for the app all use 7.3.4.5. The actual production database, though, is 7.3.4.3. And on the table in question, a simple validate structure doesn't report any errors with the table itself. The cascade option, as would be expected, picks up and reports the inconsistency between the index and the data. I ran across enough bugs and notes on Metalink related to index corruption and parallel rebuilds, though none were exact matches on scenarios and/or versions, that it still might be worth their mentioning when opening up a TAR on this. Then again, there are a lot of other reasons for corruption and the parallel rebuilds may have nothing to do with what they are seeing.

Oh well, gotta run and get some chores out of the way so I don't miss the start of what could turn out to be the most boring Super Bowl ever ;-)

Regards,

Larry G. Elkins
elkinsl_at_flash.net
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Mohammad Rafiq

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