Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> tunning with views

tunning with views

From: Sergio <sjosiowicz_at_hotmail.com>
Date: 27 Jul 2001 13:23:34 -0700
Message-ID: <4e1c3001.0107271223.44790927@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 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 Fri Jul 27 2001 - 15:23:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US