how to force partition pruning ???

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:20:03 CST