| Range Hash Partitioning vs. range partition plus B Tree index (merged) [message #383737] |
Thu, 29 January 2009 22:24  |
dr46014
Messages: 49
Registered: February 2007
|
Member |
|
|
Which one is advantageous?Range hash partition or range partition plus b tree index.
I have a table where it is partitioned on enter_date.For every enter date there may be chances of same account number again and again with few more account number each time.Now if i do a range hash partition what will be the advantage.If i do the index on account_nbr and partition on enter date what will be the advantage
|
|
|
|
|
|
|
|
| Re: Range Hash Partitioning vs. range partition plus B Tree index [message #383767 is a reply to message #383740] |
Fri, 30 January 2009 00:49  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You index to positively identify a small number of rows. Usually <10%, ideally <1% of a table. If fewer than 1% of rown in a table match an indexable condition, then indexes will be advantageous.
Partitioning is different. Query performance through partition pruning is only one reason to partition; you also need to consider partition-wise joins, archiving, loading, and segment maintenance needs.
Partition pruning is generally advantageous where indexes are sub-optimal. They are excellent when they can be used to resolve a predicate that filters up to 90% or so of rows. If you are using partitioning JUST for partition pruning more than 99% of rows, it is still WAY efficient, but probably overkill (it would usually be simpler to maintain indexes).
In your case, partition on Date by all means. Then if each Account Number represents more than 10% of each partition you could LIST sub-partition. However if each Account Number represents <10% of each partition, you should probably just index it.
HASH partitioning is generally preferred for partition-wise joins and segment management. It lacks the fine-grained control required for effective partition pruning tuning.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
