Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: tunning index in view
"Keith Boulton" <kboulton_at_ntlunspam-world.com> wrote in message
news:Lev87.14761$vN4.95410_at_news11-gui.server.ntli.net...
> I notice you are using the rule based optimiser. It used to be the case
that
> the RBO would not properly merge the view sql with a query on a view where
> the view involved unions i.e. it would always evaluate the view in its
> entirety first. I'm not sure if this is still the case, but you may find
> that analysing the tables and using the cost based optimiser will choose
the
> correct access path.
>
> "Sergio" <sjosiowicz_at_hotmail.com> wrote in message
> news:4e1c3001.0107271212.29fb06cc_at_posting.google.com...
> > Hello:
> > I have the following sql statements. The first statement generate the
> > explain plan which is showed below the "1 sql". This plan is what i
> > want because the table ta_prefactura_10701 is very long compared with
> > ga_abonados. But I have several ta_prefactura_* and i want to do a
> > view which include the several ta_prefactura_*. That view was called
> > ta_prefactura, but when i do the "2 sql", i have the explain plan
> > which is showed below the "2 sql". This is an ugly plan, because it
> > scan the full tables ta_prefactura_* which are very long. How can i
> > define the view or the statement sql to make access the the
> > ta_prefactura_* by index in the 2nd. sql?
> > I have defined a compound index in the ta_prefactura_10701 which
> > include the fields cod_cliente and num_telefpaga.
> >
> > 1 sql)
> >
> > select * from ta_prefactura_10701 ti, ga_abonados ga
> > where ti.cod_cliente = ga.cod_cliente and ti.num_telefpaga =
> > ga.num_abonado;
> >
> > SELECT STATEMENT Optimizer=RULE
> > NESTED LOOPS
> > TABLE ACCESS (FULL) OF GA_ABONADOS
> > TABLE ACCESS (BY INDEX ROWID) OF TA_PREFACTURA_10701
> > INDEX (RANGE SCAN) OF AK3_TA_PREFACTURA_10701 (NON-UNIQUE)
> >
> >
> > 2 sql)
> >
> > select * from ta_prefactura ti, ga_abonados ga
> > where ti.cod_cliente = ga.cod_cliente and ti.num_telefpaga =
> > ga.num_abonado;
> >
> > SELECT STATEMENT Optimizer=RULE
> > NESTED LOOPS
> > VIEW OF TA_PREFACTURA
> > UNION-ALL
> > TABLE ACCESS (FULL) OF TA_PREFACTURA_30801
> > TABLE ACCESS (FULL) OF TA_PREFACTURA_20801
> > TABLE ACCESS (FULL) OF TA_PREFACTURA_40801
> > TABLE ACCESS (FULL) OF TA_PREFACTURA_10701
> > TABLE ACCESS (FULL) OF TA_PREFACTURA_30701
> > TABLE ACCESS (FULL) OF FA_LLAMADAS_ANT
> > TABLE ACCESS (BY INDEX ROWID) OF GA_ABONADOS
> > INDEX (UNIQUE SCAN) OF PK_GA_ABONADOS (UNIQUE)
>
>
Correct
Partition views are available using CBO only. The init.ora parameter PARTITION_VIEW_ENABLED should be set to true, and I have a feeling the compatible parameter needs to be at least 7.3.3
Regards,
Sybrand Bakker, Senior Oracle DBA Received on Sat Jul 28 2001 - 07:52:04 CDT