Re: Corrupt index in data mart?

From: Frank van Bortel <f.van.bortel_at_vnl.nl>
Date: Mon, 18 Oct 1999 14:54:43 -0700
Message-ID: <380B9723.27D569F1@vnl.nl>

Wilson NG wrote:

> Help, all oracle expert:
>
> Symptom: performance of a query that join multiple tables (including a
> very large fact table) used to take about 50secs to return data
> (whatever the tools were used). The very large fact table was then
> truncated one day. Then all the same data were reloaded into this fact
> table. The indexes on this fact table were remained unchanged.
>
> The same query then took more than 6 mins. The explain plan of the
> query looked exactly the same as the one before, i.e., made use of all
> the pre-defined indexes in the very large fact table. It looked that
> the query used the correct plan but the indexes were not actually used
> (or some problem here?).
>
> Have tried to drop indexes and re-created again, but it's worst, i.e.,
> even the plan did not use the correct indexes.
>
> Any ideas?

Was it analyzed originally? Try analyzing table and indexes, can use estimate on table,
advise compute for index(es)

--
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle

Work:                                Home:
----------------------------------   ----------------------------
Inter Access V&L                     Hunzestraat 4
Palatijn 3, 7521 PN Enschede         7555 WB Hengelo
PoBox 545, 7500 AM Enschede          (31)074-2425046

053-4341500 Received on Mon Oct 18 1999 - 16:54:43 CDT