Partition Pruning

[hacketta]

Dear All

I am attempting to implement partition pruning on an 8.1.7.0.1 database running on SuSE Linux Professional 7.0. However the optimizer is choosing not to recognise the partitions. I have verified that the optimizer_mode=choose and also I've have analyzed the partitioned tables (compute not estimate statistics) but this seems to make no difference. I even tried this simple example:
========================================
drop table cb_test;

create table cb_test (id number(5), part number(5), texte varchar2(50))
PARTITION BY RANGE ( part)
(PARTITION part_q1 VALUES LESS THAN ( 1000 ),
PARTITION part_q2 VALUES LESS THAN ( 2000),
PARTITION max values less than (maxvalue));

BEGIN
FOR I in 1 .. 99999 LOOP
INSERT INTO cb_test VALUES (i, i, to_char(i)||'TOTO');
END LOOP;
END;
/

analyze table cb_test compute statistics;

explain plan set statement_id = 'bsw' for
select * from cb_test where part = 500;

select operation, options, object_name
from plan_table
where statement_id = 'bsw';
========================================
The output is as follows:
========================================
Table dropped.


Table created.


PL/SQL procedure successfully completed.


Table analyzed.


Explained.


OPERATION OPTIONS OBJECT_NAME
------------------------------ ------------------------------ ------------------------------
SELECT STATEMENT
TABLE ACCESS FULL CB_TEST
SELECT STATEMENT
TABLE ACCESS FULL CB_TEST
SELECT STATEMENT
TABLE ACCESS FULL CB_TEST
SELECT STATEMENT
TABLE ACCESS FULL CB_TEST
========================================

My database is not running parallel query, but my understanding is that this should make no difference. Could anybody out there offer any advice? Has anyone managed partition pruning without parallel query?

Any help would be much appreciated

Thanks

Austin

[tamilselvan]

Try to experiment with each partition on different tablespace.

[hacketta]

Thanks for the advice. It turned out that it was doing the pruning after all, it just wasn't appearing to in my explain plan query. I discovered this with sql_trace. The Tkprof output showed that the full table scan stopped and started at partition 1! Looks like paralell query is not mandatory for partition pruning to work.

[tamilselvan]

select '| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |' as "Plan Table" from dual
union all
select '--------------------------------------------------------------------------------' from dual
union all
select rpad('| '||substr(lpad(' ',1*(level-1))||operation||
decode(options, null,'',' '||options), 1, 27), 28, ' ')||'|'||
rpad(substr(object_name||' ',1, 9), 10, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' ||
lpad(decode(partition_start, 'ROW LOCATION', 'ROWID',
decode(partition_start, 'KEY', 'KEY', decode(partition_start,
'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6),
'NUMBER', substr(substr(partition_start, 8, 10), 1,
length(substr(partition_start, 8, 10))-1),
decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|' ||
lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L',
decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
'NUMBER', substr(substr(partition_stop, 8, 10), 1,
length(substr(partition_stop, 8, 10))-1),
decode(partition_stop,null,' ',partition_stop)))))||' ', 7, ' ')||'|' as "Explain plan"
from plan_table
start with id=0 and timestamp = (select max(timestamp) from plan_table
where id=0)
connect by prior id = parent_id
and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
and prior timestamp <= timestamp
union all
select '--------------------------------------------------------------------------------' from dual
/

Run the above query after a statement is explained.