Re: Please help me solve this Oracle mystery...

On 06 Jun 1999 07:09:05 PDT, Nnoor_at_cris.com (NNOOR) wrote:

>In a query, a table with only 45 records was part of a join. Other
>two tables had about 100,000 record each. The query ran very slow.

Even though a table has 45 records, full table scan on it may still be slow. This happens if the table had at one point grew substantially and then rows were deleted from it (search for "highwatermark" in Oracle documentation).

>another table. Now, since we were "experienced", we dropped and
>recreated that table and the problem fixed itself.

This supports the above theory but not necessarily proves it. Besides dropping the table, truncating it will also reset the highwatermark. If you can achieve the same effect of this problem fixing itself (if you can, in fact, reproduce it), by truncating and repopulating the table with only 45 records, then I am pretty sure that was the problem.

If, however, the problem does not fix itself with this operation, I would ascribe it to the change in execution plan (see previous reply to your question).
Change in execution plan due to statistics can be easily verified by using explain plan with and without statistics. Received on Fri Jun 11 1999 - 14:29:56 CDT