Re: partition table question ?

If you used a full explain plan (see my web site for example) you should find that this query also has

    partition_start = number(1)
    partition_stop = number(1)

The autotrace plan has not yet caught up with the available technology.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

tedchyn_at_yahoo.com wrote in message <7u0dfm$hlj$1_at_nnrp1.deja.com>...
>Sir,
>I created a test table with following 4 partitions, insert 10 records to
>partition q1 and analyze the table with compute stat. Why autotrace did
>not show only partition q1 is scanned ? Is is necessay to define index
>before partition elimination taking place ?
>
>Thanks in advance, Ted Chyn(tedchyn_at_yahoo.com)
>
>
>CREATE TABLE sale
> ( invoice_no NUMBER,
> sale_year INT NOT NULL,
> sale_month INT NOT NULL,
> sale_day INT NOT NULL )
> PARTITION BY RANGE (sale_year, sale_month, sale_day
> ( PARTITION q1 VALUES LESS THAN (1999, 02, 01),
> PARTITION q2 VALUES LESS THAN (1999, 03, 01),
> PARTITION q3 VALUES LESS THAN (1999, 04, 01),
> PARTITION q4 VALUES LESS THAN (1999, 05, 01))
>
> ;
>insert 10 records to q1
>
>SQL> analyze table sale compute statistics;
>
>Table analyzed.
>
>
>SQL> set autotrace on
>SQL> l
> 1* select count(*) from sale partition(q1)
>SQL> /
>
> COUNT(*)
>---------
> 10
>
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
> 1 0 SORT (AGGREGATE)
> 2 1 TABLE ACCESS (FULL) OF 'SALE' (Cost=1 Card=10)
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Wed Oct 13 1999 - 02:22:59 CDT