Re: Cost Based Optimizer

Thanks for the comment. It's great to have this possibility!

"Vek zivi, vek uchis' - durakom pomresh" :) Albert

In article <ABrhQRsK42_at_protasov.kiev.ua>,   oracle_at_protasov.kiev.ua wrote:
>
> 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
> >
> >
>

--
Albert Balbekov, Senior Consultant
GRT Inc.

760 Summer Street, Suit 101      Tel:(203) 324-2400
Stamford, CT 06901               Fax:(203) 324-9085

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own