Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: tunning index in view
It would help if you mentioned the version of Oracle you are using.
It looks as if you should look at partitioned views (a deprecated feature, however, in 8.0) to make the minimum change to your system.
There are a couple of articles on this topic on my website.
-- Jonathan Lewis Host to The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases See http://www.jlcomp.demon.co.uk/book_rev.html Seminars on getting the best out of Oracle See http://www.jlcomp.demon.co.uk/seminar.html Screensaver or Lifesaver: http://www.ud.com Use spare CPU to assist in cancer research. Sergio wrote in message <4e1c3001.0107271212.29fb06cc_at_posting.google.com>...Received on Fri Jul 27 2001 - 15:32:31 CDT
>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)