| Partitioning In Phases and Impact [message #195260] |
Wed, 27 September 2006 11:51  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
In our production DB we have plan to implement partitioning on 12 huge tables in 3 phases 4 tables each time, on all the tables partitioning column is a Date field and it is going to be
range partitions,It's a OLTP application. Oracle 9i Rel 2 Version
One of the Question raised by developer is
" Since we are doing in phases that means on 1 weekend we will do on 4 tables, and after 15 days 2 phase and so on..
till the next phase the existing 8 tables are unpartitioned and 4 tables are partitioned,
He says the existing queries will have different plan and will behave differently, since now after 1 phase completion , it has to join partition tables with unpartition table,
But what i wanted to tell him since we are not indexing the partitioning column and the queries don't involve the partitioning column in the where clause it shouldn't have impact, it's just Global indexes on PK column , initially we are doing partitioning for administratio / maintenance purpose.
I know testing will yield some results, but since in testing and development DB size was small i did partition on all tables in 1 go so couldn't do more test of phase wise, regarding queries and select statement in DEV and TEST after partitioning things are same no gain no loss..
Based on your experience can you people put some light on this doubt of doing in phases.
Regards
[Updated on: Wed, 27 September 2006 11:53] Report message to a moderator |
|
|
|
| Re: Partitioning In Phases and Impact [message #195311 is a reply to message #195260] |
Wed, 27 September 2006 20:27  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The likely effects are:
Desirable
- Joins between partitioned tables using an equi-join on the partition col will be faster (because of Partition-wise Joins)
- SQLs that previously performed a full scan for range queries on the partition column will be faster (because of Full Partition Scan)
Undesirable
- If you have a huge number of partitions (say >500), then range scans on LOCAL partitioned indexes for queries that do NOT use the partition key will be slower (not by a lot, but they have to scan every partition - you would definitely notice it in a loop, or in system load with 100's of users). This will not improve when you partition the rest of the tables.
Some execution plans could change (unrelated to the points above), but usually for the better. Poorer performance would be unusual and unpredictable, and would be unrelated to the fact that only some of the tables are partitioned. You could deal with it on a case-by-case basis.
Ross Leishman
|
|
|
|
| Re: Partitioning In Phases and Impact [message #195419 is a reply to message #195311] |
Thu, 28 September 2006 09:01 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Ross,
I really appreaciate your excellent comments !, It really helps me in going ahead, because of some unncessary developer's doubt manager get confused and made me think twice can we do in phase or should do in 1 go on all 12 tables which practically was not possible doing in straight 1 go.
Now as earlier planned i will be doing in Phases,
1 clarification on this "
Undesirable
If you have a huge number of partitions (say >500), then range scans on LOCAL partitioned indexes for queries that do NOT use the partition key will be slower (not by a lot, but they have to scan every partition - you would definitely notice it in a loop, or in system load with 100's of users). This will not improve when you partition the rest of the tables.
i have 120 partitions on each table,
I don't have Local Partitionied Index as i said earlier the partitioning Key column is not indexes as of know all are Global Indexes our initial aim is to partition for maintenace/Admin purpose.What do you that undesirable part will not happen in my case am i right ?.
Thanks for your valuable input.
Thanks Again .
|
|
|
|
| Re: Partitioning In Phases and Impact [message #195465 is a reply to message #195419] |
Thu, 28 September 2006 18:28 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I know you said there would be no LOCAL indexes, I just didn't believe you.
GLOBAL indexes are a pain for maintenance/admin. You need to rebuild the entire index whenever you DROP/TRUNCATE/EXCHANGE a table partition. If you are only partitioning for backup purposes, forget I said anything.
Don't be surprised though if someone comes up with the bright idea of making the indexes LOCAL in the near future. With 120 partitions, I don't think you'll notice any problem though.
Ross Leishman
|
|
|
|
| Re: Partitioning In Phases and Impact [message #195677 is a reply to message #195465] |
Sun, 01 October 2006 01:47 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Ross,
Another question on partitioned tables, Just want to know your input on this,
One of DBA says " DBMS_STATS doesn't work good on partitioned tables, analyze table is good "..
How far this is true any link or doc which says this.
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
