Re: how to force partition pruning ???

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 4 Mar 2003 10:46:04 -0000
Message-ID: <b4207f$c5j$1$8300dec7@news.demon.co.uk>

Look in $ORACLE_HOME/rdbms/admin
for files:

    utlxplan.sql
    utlxplp.sql
    utlxpls.sql

Do a proper explain plan that shows the
partition start and stop values from the plan table, and you will find that you are doing partition elimination.

autotrace has not caught up with the
optimizer yet.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"cris" <cris119_at_operamail.com> wrote in message
news:b41uhf$eo5$1_at_panco.nettuno.it...


> Quoted from Oracle9i "SQL Reference" book:

>

> You can select rows from a single partition of a partitioned table

by


> specifying the keyword PARTITION in the FROM clause. This SQL

statement


> assigns an alias for and retrieves rows from the sales_q2_2000

partition


> of the demo table sh.sales:

>

> SELECT * FROM sales PARTITION (sales_q2_2000) s

>     WHERE s.amount_sold > 1000;

>

> This is what I did:

>

> SQL> set autotrace on

>

> SQL> alter session set optimizer_mode = 'ALL_ROWS';

>

> Session altered.

>

> SQL> select avg(numeric_column) from big_table partition

(small_partition) ;


>

> AVG(NUMERIC_COLUMN)

> --------------

>      4.21059701

>

>

> Execution Plan

> ----------------------------------------------------------

>     0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=411 Card=1

Bytes=4


>            )

>

>     1    0   SORT (AGGREGATE)

>     2    1     TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=411

Card=152831


>             Bytes=611324)

>

> Why the optimizer insists to perform a full table scan ???

>

> Oracle Enterprise Edition 9.0.1.3

> Platform: SuSE SLES7

>

> Thanks in advance, Cris

>

Received on Tue Mar 04 2003 - 04:46:04 CST