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

"inferno2000" <inferno2000_at_my-deja.com> wrote in message news:a9390719.0211210014.162f5c72_at_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?

Hi Inferno2000,

Yes, partition pruning is not being performed (as there is no partition range operation).

*However*, you are requesting data that is indexed via a *global* index. Therefore Oracle is simply using this index to efficiently access the data. There is no need to use partition pruning as the index (which points to all partitions) can directly access the required rows. The rows are being directly 'pruned' so to speak.

>
> ======
>
> 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.

This *appears* to be correct again as again no partition range operation is listed in the execution plan.

However I have seen "funnies" with this where partition pruning is obviously occurring due to the statistics being generated (ie. low number of consistent/physical reads) but the execution plan doesn't seem to show this. You need to trace the operation and see exactly what the optimizer is doing.

However (again) here I believe the reason to be somewhat different. You 'only' have 3000 tiny rows in your table taking at a guess depending on your block size somewhere between 10 and 20 blocks or so. Oracle is probably just saying "ah bugger it, I might just as well read the whole damn lot" !!

Increase the number of rows 10 fold and you should see a different result.

One last point. I notice you've generated statistics with the analyze command. Don't, especially with partitioned tables. Use dbms_stats as this generates both global and partition level statistics. This could be your issue.

>
> What could be the possible cause? Could it be "PARTITION_VIEW_ENABLED"
> settings is being set to FALSE in INIT.ORA?

This parameter only affects the use of partitioned views. It should have zip effect in your case.

Good Luck

Richard Received on Thu Nov 21 2002 - 08:03:23 CST