Home » RDBMS Server » Performance Tuning » Selecting data out of a partition
Selecting data out of a partition [message #113383] Fri, 01 April 2005 13:44 Go to next message
Messages: 2
Registered: March 2005
Junior Member
Hello every one,

We have a hash partitioned table partitioned on columna and each partiton holds only 1 value

ex: p1=x

My question is would there be a difference in performance if table is acessed

select * from table a partition(p1)
select * from table a columna=x

It would be great if some one could give me the performance in this cases

Re: Selecting data out of a partition [message #113390 is a reply to message #113383] Fri, 01 April 2005 15:54 Go to previous message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Two things come to mind:

1. If you are going to be using partitioning, you should go ahead and get used to doing an explain plan on queries and paying particular attention to the pstart and pstop columns towards the right of the explain plan output. They tell you which partitions of a table are accessed in your query. If they have the same value, then you know that CBO knew to only hit one partition as opposed to the whole table.

2. Are you sure you are not mixing hash partitioning up with list partitioning? List being a list (set) of values that you define going into specific partitions. Hash being an internal oracle algorithm to "arbitrarily" place values in different partitions based on how those values are hashed through an internal algorithm.

In general, I'd suggest against using specific partition names inside of queries, in most cases, because that eliminates the transparency that partitions allow, and if you ever change how things are partitioned, then your query is out of luck. Let the CBO make the partition selections for you, just provide it with enough information (statistics and thoughtful partitioning scheme) so that it can make the appropriate decisions.
Previous Topic: what to tune first
Next Topic: no_index optimizer hint
Goto Forum:

Current Time: Sun Feb 28 07:55:22 CST 2021