Though I've not come across such issue, just want to discuss.
Is this issue of analyzed info being generated on O7 and O8 had some
compatibility issue betn table defn and analyzed info? just thinking.
Frank & Amy Siegel <NorthernSnow_at_worldnet.att.net> wrote in message
news:80794s$p8o$1_at_bgtnsc02.worldnet.att.net...
> Thanks you in advance for any light you may shed on this mystery.
> We have a very simple table called PLAN_HISTORY with only 5000
> rows (that's extremely tiny compared to other tables in our dB).
>
> PLAN_HISTORY has a 3 column index consisting of col1, col2, col3
> (in that order!). If I ran the query:
>
> select 'x'
> from PLAN_HISTORY
> where col1 = 'aaaa'
> and col2 = 'bbbbb'; (note no implied data conversions)
>
> it would NOT use the index AFTER UPGRADING TO ORACLE8
> and take seconds. (This query is used in an explicit cursor in a
> batch stored procedure which normally takes hours but without the use
> of the index in the explicit call turn the running procedure into days).
> Our other procedure that do the same kind of explicit cursor calls and
> indexes worked just fine.
>
> If I dropped and recreated the index it still did not choose the index!
> HOWEVER! When I dropped and recreated the table, it then
> used the index (tables always analyzed). This problem surfaced
> and was fixed (after recreating the table) all post upgrade to Oracle8.
>
> Now we have run other queries on other tables with 100 times
> the data (5 million rows) and they worked fine.
>
> As part of the Oracle 8 upgrade, I performed a full table scan
> on all the tables in the schema. Nothing unusual happened with
> the full table scan for PLAN_HISTORY.
>
> Has anyone seen this before?
> Thanks Again,
> Frank S.
>
>
Received on Tue Nov 09 1999 - 06:59:34 CST
