Re: CBO stupid !!!

On Sat, 30 May 1998 21:44:51 +0100, "David" <darussell_at_msn.com> wrote:

>Many of the reasons for Full Table scans are caused because of the lack of
>Histograms in 7.2, I would recommend 7.3.x which include many new plan
>options, STAR, HASH JOIN and HISTOGRAMS.......
>
>Regards
>David Russell
>

In most of the applications I've worked on, I don't think histograms would help because processing usually involves bind variables and to quote from the concepts manual:

Histograms are not useful for columns with the following characteristics:
* All predicates on the column use bind variables ...

I believe hash join is only really useful if one of the tables is very small as it involves full table scans of the tables involved in the join. The only times I've seen a hash join in an access path, the statement has been too slow.

A star plan seems to apply only in very limited circumstances.

I suspect the main reason for inappropriate full table scans and join orders is that the default optimizer goal is maximum throughput.

The point is that use of an index where a full table scan would be more efficient or use of a nested loops operation in place of a sort-merge or hash join results in a query that runs maybe 3 or four times slower. Use of a full table scan or a hash join operation where it is inappropriate can result in a thousand-fold increase in the time taken. The first_rows hint almost always avoids a full-table scan or sort-merge operation. Received on Wed Jun 03 1998 - 04:51:19 CDT