A beginner's question on Oracle 8i: Table/Index Partitioning

From: inferno2000 <inferno2000_at_my-deja.com>
Date: 21 Nov 2002 00:14:59 -0800
Message-ID: <a9390719.0211210014.162f5c72@posting.google.com>

How to prove that partition elimination/pruning is being performed in Oracle 8.1.7?

I have created the following test example:

---

• Partition table example CREATE TABLE t_test (f1 NUMBER, f2 number) PARTITION BY RANGE(f1) (PARTITION t_test1000 VALUES LESS THAN (1000), PARTITION t_test2000 VALUES LESS THAN (2000), PARTITION t_test3000 VALUES LESS THAN (3000), PARTITION t_test_inf VALUES LESS THAN (MAXVALUE))
  /
  create index t_test_i1 on t_test (f1) global partition by range (f1) (partition t_test_i1_1000 values less than (1000), partition t_test_i1_2000 values less than (2000), partition t_test_i1_3000 values less than (3000), partition t_test_i1_inf values less than (MAXVALUE) )
  /
  

declare
lv_i number;
begin
  for lv_i in 0..2999
  loop
    insert into t_test values (lv_i,lv_i);     commit;
  end loop;
end ;
/

analyze table t_test compute statistics
/

---

Try to select a record and observe the resulting execution plan:

select /*+ index( a ) */
a.*
from t_test a
where a.f1 = 2001;

The plan shows:

Execution Plan

---

   0 * SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)    1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'T_TEST' (Cost=2 C

          ard=1 Bytes=6)

   2    1     INDEX (RANGE SCAN) OF 'T_TEST_I1' (NON-UNIQUE) (Cost=1 C
          ard=1)

Does this mean partition pruning is not being performed?

---

Try to compute an aggregate (and hopefully the optimizing will eliminate partition t_test1000, t_test2000, and t_test_inf)

select sum(a.f2)
from t_test a
where a.f1 between 2500 and 2600;

The plan shows:

Execution Plan

---

   0 * SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)

   1    0   SORT (AGGREGATE)                                          
   2    1     TABLE ACCESS (FULL) OF 'T_TEST' (Cost=1 Card=103 Bytes=6
          18)

It appears that there is no partition pruning being performed.

What could be the possible cause? Could it be "PARTITION_VIEW_ENABLED" settings is being set to FALSE in INIT.ORA? Received on Thu Nov 21 2002 - 02:14:59 CST