Home » RDBMS Server » Performance Tuning » Range Hash Partitioning vs. range partition plus B Tree index (merged)
Range Hash Partitioning vs. range partition plus B Tree index (merged) [message #383737] Thu, 29 January 2009 22:24 Go to next message
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 partition vs. range partition plus b tree index [message #383739 is a reply to message #383737] Thu, 29 January 2009 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Which one is advantageous?Range hash partition or range partition plus b tree index.
It depends upon data distribution & processing requirements.

If one was "best" for all/every case there would be no need for the other alternative.

You get to choose the one which is better for your particular requirements.
You can determine which is "better" by running your own benchmark tests.
Re: Range Hash Partitioning vs. range partition plus B Tree index [message #383740 is a reply to message #383737] Thu, 29 January 2009 22:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Do NOT multi-post!
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 Go to previous message
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
Previous Topic: explain plan
Next Topic: Exchange Partition
Goto Forum:
  


Current Time: Sat Nov 09 15:01:31 CST 2024