| Selecting data out of a partition [message #113383] |
Fri, 01 April 2005 13:44  |
informatica_developer_mn
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
p2=y
My question is would there be a difference in performance if table is acessed
select * from table a partition(p1)
vs
select * from table a columna=x
It would be great if some one could give me the performance in this cases
Thanks
|
|
|
|
| Re: Selecting data out of a partition [message #113390 is a reply to message #113383] |
Fri, 01 April 2005 15:54  |
smartin
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
