Analyze table bug

From: Sameer Utrankar <sameeru_at_exchnage.ml.com>
Date: Wed, 15 Jul 1998 12:21:47 -0400
Message-ID: <35ACD71B.C589E7A9@exchnage.ml.com>

Has anyone seen wrong results (bad/garbage data returned by select) when table is analyzed v/s when it isn't ?

I have a table with PK and 3 other indexes. When it's not analyzed, a query works fine. When it's analyzed, query returns bad data (same number of rows are returned but one of the selected columns has garbage data in it). Sounds familiar to anyone ? Sun Unix, rdbms is 733.

Table with data is too big to be sent out to the entire list. I am sending .sql and outputs with and without table analyzed. If table does not have any indexes (if I drop them all) and then table is analyzed, query works fine so it's something to do with indexes and analyzed table. If init.ora compatible is set to 722 then there is no problem. If compatible is 733 then problem happens.

Any hints and suggestions (other than saying don't analyze the table and use rule based - which is what I am doing for the time being until the problem is resolved) ?

SCRIPT START : Session:dev1-DAILYPL:2 15-Jul-98 11:45:32 am ****

SCRIPT File: D:\Carat\Sql\ad1.sql

Starting from line number : 1

Processing ... Select

ADJUSTMENT SUM(AMOUNT MAX(ADJUST

---------- ---------- ----------
56831      -13576     07-JUL-98
71361      7          29-JUN-98
71872      -1530      07-JUL-98
72874      0          07-JUL-98
72919      0          07-JUL-98
73203      0          07-JUL-98
73252      -12222     07-JUL-98

7 rows retrieved

SCRIPT END : 15-Jul-98 11:45:34 am ****
SCRIPT START : Session:dev1-DAILYPL:2 15-Jul-98 11:45:52 am ****

SCRIPT File: D:\Carat\Sql\ad1.sql

Starting from line number : 1

Processing ... Select

ADJUSTMENT_LINE_ID                       SUM(AMOUNT MAX(ADJUST
---------------------------------------- ---------- ----------


-:.1:1:1000000000000000000000000000E+125 -13576     07-JUL-98


-:.1:1:1000000000000000000000000000E+125 7          29-JUN-98


-:.1:1:1000000000000000000000000000E+125 -1530      07-JUL-98


-:.1:1:1000000000000000000000000000E+125 0          07-JUL-98


-:.1:1:1000000000000000000000000000E+125 0          07-JUL-98


-:.1:1:1000000000000000000000000000E+125 0          07-JUL-98


73252      -12222     07-JUL-98

7 rows retrieved

SCRIPT END : 15-Jul-98 11:45:52 am ****
- application/x-unknown-content-type-sql_auto_file attachment: dailypl_problem.sql

Received on Wed Jul 15 1998 - 11:21:47 CDT