Re: tunning index in view

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)
Received on Sat Jul 28 2001 - 04:10:24 CDT