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

Home -> Community -> Usenet -> c.d.o.server -> Re: tunning index in view

Re: tunning index in view

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 27 Jul 2001 21:32:31 +0100
Message-ID: <996265772.27353.0.nnrp-01.9e984b29@news.demon.co.uk>

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>...

>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 Fri Jul 27 2001 - 15:32:31 CDT

Original text of this message

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