| How to partition tables and indexes in this scenario? [message #489022] |
Wed, 12 January 2011 23:01  |
 |
steffi
Messages: 2
Registered: January 2011
|
Junior Member |
|
|
So our situation is pretty simple. We have 3 tables.
A, B and C
the model is A->>B->>C
Currently A, B and C are range partitioned on a key created_date however it's typical that only C is every qualfied with created date. There is a foreign key from B -> A and C -> B
we have many queries where the data is identified by state that is indexed currently non partitioned on columns in A ... there are also indexes on the foreign keys that get from C -> B -> A. Again these are non partitioned indexes at this time.
It is typical that we qualifier A on either account or user or both. There are indexes (non partitioned on these)
We have a problem with now because many of the queries use leading wildcards ie. account like '%ACCOUNT' etc. This often results in large full table scans. Our solution
has been to remove the leading wildcard.
We are wondering how we can benefit from partitioning and or sub partitioning table A. since it's partitioned on created_date but rarely qualified by that.
We are also wondering where and how we can benefit from either global partitioned index or local partitioned indexes on tables A. We suspect that the index on the foreign key from C to B could be a local partitioned index.
|
|
|
|
| Re: How to partition tables and indexes in this scenario? [message #489063 is a reply to message #489022] |
Thu, 13 January 2011 02:39  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Partitioning can give great benefits if you get it right, and be disastrous if you get it wrong. Trust me, I've done it both ways! You need to be very careful. Work out why you think a particular partitioning scheme will help, then construct a test that proves the benefits.
For example: you say that many queries use STATE as the predicate, which is indexed. I don't know your data, but a column of that name probably has low cardinality, so you would want to use a bitmap index. But global bitmap indexes are impossible, so either you have to use a local bitmap index or a global b-tree index: both options will probably create an index that the optimizer will ignore. This is that sort of thing you have to test.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
