Re: Cost Based Optimizer

From: <abalbekov_at_usa.net>
Date: Tue, 08 Dec 1998 19:51:59 GMT
Message-ID: <74k00v$cq8$1@nnrp1.dejanews.com>

In article <74cmr0$39q_at_bgtnsc03.worldnet.att.net>, "Jerry" <jcman_at_worldnet.att.net> wrote:
> I have a query against a view that is very slow in RULE, but fast in cost.
> But for other reasons I want to run the database in RULE. When I re-create
> the view with a /*+ CHOOSE */ hint and run it under RULE mode, EXPLAIN_PLAN
> will execute it differently in the database under CHOOSE mode, without the
> hint on the view.
>
> Is this behavior normal? BTW, all tables have statistics estimated in both
> scenarios.
>
> Thanks.
>

Optimizer will consider hint only if it is specified in the outermost select. It is ignored if indicated in the view. What you can do here - put database in CHOOSE mode but do not supply statistics for any table except involved in the view query. Without statistics Oracle will take Rule optimization.

Albert

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Dec 08 1998 - 13:51:59 CST