Re: Hints in Practice

>>>>>> I would do anything to avoid using those hints. They are not portable
>>>>>> and do not conform to any standard.
>>>>> Thus you don't use array processing, don't use exception handling,
>>>>> don't use PL/SQL packages, and heaven-forbid don't use any type of
>>>>> index other
>>>>> than B*Tree and Bitmap.
>>>> Again, do we have other choices? With PL/SQL, maybe.
>>> Many other choices.
>>>
>>> DBMS_STATS.SET procedures, stored outlines, write better statements,
>>> tune initialization parameters more often, the list is long.
>>
>> Now you are just going 10 different directions. What standard does any of
>> these follow?
>
> That's not the point. The point was there are alternatives to using
> hints if you want to force Oracle's CBO to use a specific plan. I wasn't
> going any further than that.
>

The point was that the hints used in the SQL are non-standard, therefore not portable.

>>> Because you specifically equated Oracle making hints available to a lack
>>> of confidence in, or ability of, the CBO. And I think that is just plain
>>> wrong.
>>
>> If they are confident about the optimizer, why are hints necessary?
>
> You are missing the point. SQL is a language that defines the result
> not the plan. We don't instruct the optimizer on what to do. We rely
> upon it to do the right thing.
>

Exactly. Putting hints in the SQL violates all that.

>>> The CBO is certainly not perfect but it is, with equal certainty,
>>> getting smarter with every release (notably excluding 8.1.6). Were it
>>> not Oracle would not be producing the results we see in the real
>>> world.
>>
>> Exactly, that's why we also need HBO - Hint Based Optimization.
>
> I thought HBO was what you were complaining about just a paragraph ago.
> --

Yes, I was being sarcastic about hints. There is no such term as HBO I know of. Received on Thu Aug 24 2006 - 21:15:42 CDT