|
|
|
|
| Re: how to decide when to go for local vs global partitioned index on a table [message #635979 is a reply to message #635976] |
Tue, 14 April 2015 03:48   |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
I must have said this a thousand times: do not use partitioning just because you can; use it to solve a business problem. A busines problem is something like "the overnight batch jobs don't finish until lunchtime" or "we need this report every ten minutes, but it takes half an hour to generate" or "we delete a zillion rows every day, and online performance degrades so badly while we do it that customers cannot place orders". If you define the problem, then the partitioning strategy will become apparent. And do not say "we need to partition this table because it is big". That is not a business problem. So, why do you think you need to partition the table?
|
|
|
|
|
|
|
|
|
|
| Re: how to decide when to go for local vs global partitioned index on a table [message #636023 is a reply to message #636017] |
Wed, 15 April 2015 01:25 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
rishuk wrote on Tue, 14 April 2015 19:44That's true. Its an interview question.
I pretty much know when to go for partitioning and what are local and global partition indexes are. But the question is - when you have a table employee which is partitioned based on employee ID range. To access data faster, you'll need an index, so here comes the question - will you go for local/global index and how you'll decide which one to go for?
Your interviewer probably wanted you to say "If employee ID is the primary key, then range partitioning on it is stupid" and to explain why. You say that you know when to use partitioning, so what do you think?
|
|
|
|
| Re: how to decide when to go for local vs global partitioned index on a table [message #636199 is a reply to message #636023] |
Sun, 19 April 2015 13:01 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
This question is related to the problem of PRECISION QUERIES faced with LOCAL / NON-PREFIXED indexes on partitioned tables. Here is an excerpt from my book on SQL Tuning that describes this particular issue.
Of course this is not a blanket solution for all hash join performance issues. There is one large problem with this strategy: it does not play well with LOCAL NON-PREFIXED indexes used by queries that do not use the partitioning scheme to do partition pruning. Consider this index and query and query plan.
create index claim_history_i1 on claim_history
(
claim_number
)
local;
select /*+ gather_plan_statistics */ *
from claim_history
where claim_number = '123';
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID g3q14ucx17sbx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from claim_history where
claim_number = '123'
Plan hash value: 549843864
------------------------------------------------------------------------
| Id | Operation | Name | Starts |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | PARTITION HASH ALL | | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CLAIM_HISTORY | 128 |
|* 3 | INDEX RANGE SCAN | CLAIM_HISTORY_I1 | 128 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CLAIM_NUMBER"='123')
21 rows selected.
Of particular note is the STARTS column. Notice how the INDEX RANGE SCAN operation on line #3 was started 128 times. It is no coincidence that the number of partitions of the underlying table is 128 and thus a local index is broken down into the same 128 partitions. When we created a LOCAL NON-PREFIXED index on this table, we in essence created not 1, but 128 little indexes, one for each of the 128 partitions. What this means is a query that uses such an index without partition pruning must do an index lookup into each of the 128 partitions. This means looking for one claim number in this case, will require 128 index lookups. The same index as a GLOBAL index (and thus not partitioned) would result in only 1 lookup.
drop index claim_history_i1;
create index claim_history_i1 on claim_history
(
claim_number
);
select /*+ gather_plan_statistics */ *
from claim_history
where claim_number = '123';
------------------------------------------------------------------------
SQL_ID g3q14ucx17sbx, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from claim_history where
claim_number = '123'
Plan hash value: 1066714231
------------------------------------------------------------------------
| Id | Operation | Name | Starts |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| CLAIM_HISTORY | 1 |
|* 2 | INDEX RANGE SCAN | CLAIM_HISTORY_I1 | 1 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CLAIM_NUMBER"='123')
20 rows selected.
We did up to 128 times more index based I/O in this situation as compared to the much less I/O that would have occurred with the GLOBAL variation of the index. So once again we see planning is necessary as well as a keen understanding of how the underlying table we seek to partition is accessed by other processes. Precision queries may suffer serious performance degradation in the presence of lots of partitions.
This is from Chapter 4: JOINS which includes the example of a partitioned table. The basic idea is that when you partition you create a bunch of "mini" tables (in this example 128 partitions). So if you create a local non-prefixed index on the partitioned table, you have essentially created a bunch of corresponding indexes, one for each table (so in this case you have 128 "mini" indexes). But a precision query looking for say 1 row using the primary key, now must do an index lookup on each of these mini-indexes. What used to be a 1 index lookup in this example has turned into 128 index lookups so you have in fact increased the workload of precision queries that do not do partition pruning on this table, by 2 orders of magnitude (128X more).
Maybe this is what there were interested in. I have attached the promotional chapter of the book, the book's script file, and a related document on tuning in case you are interested in more detail. You can also find the book on Amazon if you after reading the first chapter you think it is something that might be worth reading.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
