Partition Elimination Failing to Work

From: Gee <george_at_41donny.freeserve.co.uk>
Date: 19 Feb 2003 10:32:55 -0800
Message-ID: <f7859e6f.0302191032.53898cac@posting.google.com>

OK guys,
I have a table partition by FISCAL_PERIOD, i.e. 01, 02 ... 16. It is analyzed (using analyzed command and not DBMS_STATS) running on 8.1.7, Win2000. However, when I issue a SQL statement which I think should invoke partition elimination it appears to be doing a FULL table scan. For example :

select *
from general_ledger_line
where fiscal_period = '01'

Explain plan :

SELECT STATEMENT Hint=CHOOSE		2 M	 	3672  	 	  
  TABLE ACCESS FULL	GENERAL_LEDGER_LINE	2 M	521 M	3672  	 	 	 	 

Then when I tried to explicitly tell Oracle to look at 1 partition only, i.e.

select *
from general_ledger_line partition(gll_p01)

I get the same plan and cost!
The table has a global PK and 4 other LOCAL bitmaps (which are rebuilt daily).A Sample of table defintion is listed below.

OPTIMIZER_Index_cost_adj = 10
OPTIMIZER_INDEX_CACHING = 90 Can anybody offer any advice as to why this is happening?

Regards,

George
create table general_ledger_line

(

PK,
FISCAL_YEAR,
..columns....
)partition by range (fiscal_period)
(

partition gll_p01 values less than ('02') tablespace ora10 storage
(initial 40m next 10m pctincrease 0) pctfree 2,
partition gll_p02 values less than ('03') tablespace ora10 storage
(initial 40m next 10m pctincrease 0) pctfree 2,
partition gll_p03 values less than ('04') tablespace ora10 storage
(initial 40m next 10m pctincrease 0) pctfree 2,
partition gll_p04 values less than ('05') tablespace ora10 storage
(initial 40m next 10m pctincrease 0) pctfree 2,
partition gll_p05 values less than ('06') tablespace ora10 storage
(initial 40m next 10m pctincrease 0) pctfree 2,
partition gll_p06 values less than ('07') tablespace ora10 storage
(initial 10m next 10m pctincrease 0) pctfree 2,
partition gll_p07 values less than ('08') tablespace ora10 storage
(initial 10m next 10m pctincrease 0) pctfree 2,
partition gll_p08 values less than ('09') tablespace ora10 storage
(initial 10m next 10m pctincrease 0) pctfree 2,
partition gll_p09 values less than ('10') tablespace ora10 storage
(initial 10m next 10m pctincrease 0) pctfree 2,
partition gll_p10 values less than ('11') tablespace ora10 storage
(initial 10m next 10m pctincrease 0) pctfree 2,
partition gll_p11 values less than ('12') tablespace ora10 storage
(initial 10m next 10m pctincrease 0) pctfree 2,
partition gll_p12 values less than ('13') tablespace ora10 storage
(initial 10m next 10m pctincrease 0) pctfree 2,
partition gll_p13 values less than (maxvalue) tablespace ora10 storage
(initial 5m next 1m pctincrease 0)

) Received on Wed Feb 19 2003 - 12:32:55 CST