Re: Oracle 7.0NT-Optimizer, full of bugs?

From: Doug Harris <ah513_at_FreeNet.Carleton.CA>
Date: Fri, 23 Sep 1994 00:38:55 GMT
Message-ID: <CwK4Gw.1Dn_at_freenet.carleton.ca>


In a previous article, d7hansl_at_dtek.chalmers.se (Hans Lindberg) says:

> I.e, 79 rows in the first table, 202 003 rows in the second
>and 4 848 072 rows in the third table. There are primary keys and
>foreign keys where apropriate.
> I have calculated statistics with analyze on all tables.
>
> The optimizer often refuses to use the indexes and instead
>executes a full table scan on the HUGE table!

   We also experienced troubles with the 7.0.15 CBO when joining > 2 tables when composite (i.e. > 1 column) indexes were involved.

>I've been told that there is a bug preventing you from giving hints
>to the optimizer so that won't help either.

  We are using the CBO and have found it to be generally good but not perfect (what optimizer is perfect). In every case we have been able to use hints successfully when there was a problem. Hints work fine as long as you type the correctly and don't hint at impossible or incorrect query paths.    

--
   - Doug Harris
     Database Administrator, System Development Division,
     Statistics Canada.        ## WHERE ALL_OPINIONS.OWNER = USER ##
Received on Fri Sep 23 1994 - 02:38:55 CEST

Original text of this message