Re: Problem with sqlload and indexes under Oracle 8.0.5

dalmond14_at_my-deja.com wrote:

> We are populating an Oracle 8.0.5 database with
> data by perfoming a series of sqlloads. Prior to
> this the indexes are been dropped. When the load
> is complete the indexes are added back on. The
> indexes create fine, with no errors, but when we
> run queries against them the indexes are not
> used, a full table scan is done instead. If we
> then go into server manager and flush the shared
> pool, or bounce the database; bingo the indexes
> come into use again. Has anyone seen anything
> like this before, and give us a lead on what to
> tweak before we go crazy.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

Are you usiing Cost based parsing or Rule based parsing? Off the top of my head,
I would guess that Oracle doesn't know that there are indexes and doesn't look.
For example, if you are running those exact same queries before the load, the execution plans
are waiting - Oracle doesn't bother to re-parse the SQL.

   If you haven't run the queries yet, maybe the tables need to be analyzed before you run them?
If you are using 'Rule' based processing, try using an Index( table index) hint. It sounds
like they have already been parsed, though.

Hope that was helpful.

Lorne
(remove NOSPAM to reply via email) Received on Mon Jul 05 1999 - 21:54:13 CDT