| Partitioning [message #214188] |
Mon, 15 January 2007 06:08  |
kedar_ambardekar
Messages: 1
Registered: January 2007
|
Junior Member |
|
|
I created three partitions for a list partioned table:
partition sch_complete values('X', 'C' , 'UC' , 'UT' ,'MC'),
partition sch_archived values('AR'),
partition def values(default)
When I issue the following query:
select count(*) from sch_ked where sch_status <> 'AR'
I expect that Oracle should ignore the "sch_archived" partition, but it doesn't happen, here is the explain plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 3418116897
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 24 (9)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 12 | | | | |
| 2 | PARTITION LIST ALL | | 45100 | 528K| 24 (9)| 00:00:01 | 1 | 3 |
|* 3 | INDEX FAST FULL SCAN| IDX_BIT_SCH_STATUS | 45100 | 528K| 24 (9)| 00:00:01 | 1 | 3
----------------------------------------------------------------------------------------------------
This is strange behaviour !! Can anyone comment if I am missing something ?
|
|
|
|
| Re: Partitioning [message #214325 is a reply to message #214188] |
Mon, 15 January 2007 19:54  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Hmmm. Looks like partition pruning does not like NOT predicates. I can't get it to work either. That would make it consistent with b-tree index scans, which work the same way.
This works though:
select count(*)
from sch_ked
where sch_status > 'AR'
or sch_status < 'AR'
Don't know whether that's much help though.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
