** INDEX ACCESS PROBLEMS (CORRUPTION ????)

Can anyone help?? Oracle support do not seem able to!

We have two simple tables as follow (Approx 5Mil Records on each):

Table1     AccountNo <- Uniquely Indexed
                 Address

Table2     AccountNo   } Uniquely Indexed
                 Company      } on composite key
                 Data..

SQL Query:

select Table2.AccountNo, Company, Address from Table2, Table1 where Table1.Accno = Table2.Accno;

For some unknown reason this query takes 'an age' to run. Using 'Explain Plan' we can see that the index is not being used (Performing a full scan on Table2 - Merged Join). This query has worked previously, and I can see no reason why it shouldn't anyway!

I have Dropped & recreated all the indexes to no effect.

When I create New files/Indexes with different names but Identical structures and containing the same data, the same query works OK.

It would appear that something is preventing the optimizer from using the index.

Does anyone know of a way to determine if there is any damage to either a tablespace/table/index/Schema which may cause this??. It is as though the index does not exist.

Additionally, this may be a red herring, but Some simple views accessing Table2 which were created months ago work fine, but a new view Identical to an old one, created today takes an age??

I can easily fix this problem by rebuilding the table but I would like to find out what caused it, and more importantly, if any other files are corrupt.

I would appreciate any help which could be offered!!.

Regards

Mark. Received on Fri May 29 1998 - 00:00:00 CDT