Re: Cost Based Optimizer

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Wed, 9 Dec 98 00:40:53 +0200
Message-ID: <ABrhQRsK42@protasov.kiev.ua>

Hi,

It is not true - hints are not ignored in views and inner selects. Here is a sample:

SVRMGR> create table test_plan
     2> (id integer
     3> ,constraint test_plan_pk primary key (id)
     4> );

Statement processed.
SVRMGR> create or replace view v_test_plan1 as

     2> select * from test_plan where id=1; Statement processed.
SVRMGR> create or replace view v_test_plan2 as

     2> select /*+ full(test_plan) */ * from test_plan where id=2; Statement processed.
SVRMGR> delete from plan_table;
2 rows processed.
SVRMGR> explain plan set statement_id='query1' INTO plan_table for

     2> select * from v_test_plan1;
Statement processed.

SVRMGR> select substr(operation,1,10),
     2>        substr(options,1,15),
     3>        substr(object_name,1,25),
     4>        id,parent_id
     5>  from plan_table order by statement_id,id;
SUBSTR(OPE SUBSTR(OPTIONS, SUBSTR(OBJECT_NAME,1,25)  ID         PARENT_ID
---------- --------------- ------------------------- ---------- ----------
SELECT STA                                                    0
INDEX      UNIQUE SCAN     TEST_PLAN_PK                       1          0

2 rows selected.
SVRMGR> delete from plan_table;
2 rows processed.
SVRMGR> explain plan set statement_id='query1' INTO plan_table for

     2> select * from v_test_plan2;
Statement processed.

SVRMGR> select substr(operation,1,10),
     2>        substr(options,1,15),
     3>        substr(object_name,1,25),
     4>        id,parent_id
     5>  from plan_table order by statement_id,id;
SUBSTR(OPE SUBSTR(OPTIONS, SUBSTR(OBJECT_NAME,1,25)  ID         PARENT_ID
---------- --------------- ------------------------- ---------- ----------
SELECT STA                                                    0
TABLE ACCE FULL            TEST_PLAN                          1          0

2 rows selected.

Second view contains full scan hint and it is not ignored.

Andrew Protasov

> 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 - 16:40:53 CST