Oracle partitioning is not always a good idea.
2011-07-15 16:59 Tracy. 阅读(242) 评论(2) 编辑 收藏 举报
a) partitioning is NOT fast=true
b) you MUST understand the physics behind the data, whats happening.
Say you took a table T with columns ( ID primary key, CUST_ID, ... )
You hash partitioned into 64 partitions by ID.
You have a local index on CUST_ID.
MOST of your queries are "where cust_id = :x"
Guess what you just accomplished.
You accomplished the feat of increasing your IO by a factor of 64!!! by 64 times!!
why? well, we have 64 tiny little index segments to range scan -- your customer id could
be in any, all or none of them.
Solution -- hash partition table, range partition index by cust_id -- now you will NOT
have affected read performance at all (probably, it could be a tiny bit better with
partitioning but nothing phenomenal) but you might find that you've reduced contention on
modifications since you have N indexes and N table segments (and hence N freelists at
least and so on)
If you have my new book -- you'll laugh at how closely your example above mirrors the one
in the book, almost scary (but I only did 8 partitions, to show an 8 times increase in
IO)
b) you MUST understand the physics behind the data, whats happening.
Say you took a table T with columns ( ID primary key, CUST_ID, ... )
You hash partitioned into 64 partitions by ID.
You have a local index on CUST_ID.
MOST of your queries are "where cust_id = :x"
Guess what you just accomplished.
You accomplished the feat of increasing your IO by a factor of 64!!! by 64 times!!
why? well, we have 64 tiny little index segments to range scan -- your customer id could
be in any, all or none of them.
Solution -- hash partition table, range partition index by cust_id -- now you will NOT
have affected read performance at all (probably, it could be a tiny bit better with
partitioning but nothing phenomenal) but you might find that you've reduced contention on
modifications since you have N indexes and N table segments (and hence N freelists at
least and so on)
If you have my new book -- you'll laugh at how closely your example above mirrors the one
in the book, almost scary (but I only did 8 partitions, to show an 8 times increase in
IO)
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2011/07/15/2107645.html