Re: Hints in Practice

Bob Jones wrote:
>>>>>>> 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.

So is Oracle's use of NULL. And there is no workaround. So is Oracle's security model with roles, and system and object privileges. So is Oracle's transaction model. So is Oracle's MVCC. If you make the choice to use Oracle ... then embrace Oracle.

There isn't a single commercial RDBMS that is standard anything. The SQL 'standard' is just a myth perpetrated by marketing types.

>>>> 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.

Do reads not blocking writes violate things? How about writes not blocking reads? And as pointed out by others here Oracle is not the only RDBMS with hints? There is no place to hide. If you don't like them ... don't use them. It isn't worth the energy you are putting into this.

>>>> 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. 

Well there is but I still prefer the BBC.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org