Q: partition table question ?

From: <tedchyn_at_yahoo.com>
Date: Tue, 12 Oct 1999 22:41:59 GMT
Message-ID: <7u0dfm$hlj$1@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 Tue Oct 12 1999 - 17:41:59 CDT