Home » RDBMS Server » Performance Tuning » Partitioning
Partitioning [message #214188] Mon, 15 January 2007 06:08 Go to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Tuning an oracle view to increase the speed of the view
Next Topic: Performance Issue!!!
Goto Forum:
  


Current Time: Wed Dec 07 13:02:22 CST 2016

Total time taken to generate the page: 0.10690 seconds