Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ** INDEX ACCESS PROBLEMS (CORRUPTION ????)

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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 29 May 1998 16:24:08 GMT
Message-ID: <3576e061.14864423@192.86.155.100>


A copy of this was sent to "Mark Ragan" <mark.ragan_at_dial.pipex.com> (if that email address didn't require changing) On Fri, 29 May 1998 17:12:01 +0100, you wrote:

>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.
>

Can you see if the tables had been analyzed when only partially full? The fact that if you simply copy the data to new tables and index it and that uses the index (most likely using the rule based optimizer, you didn't say anything about analyzing the tables).

Do this:

select num_rows from user_tables where table_name = 'TABLE1' or table_name = 'TABLE2'; If that comes back non-null and is way off from the real record counts you need to either re-compute the stats or drop them.

>Regards
>
>
>Mark.
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 29 1998 - 11:24:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US