| Partitionning strategy - Row movement b'cos of Update on key partition [message #146910] |
Mon, 14 November 2005 10:25  |
silk
Messages: 5
Registered: November 2005
Location: France
|
Junior Member |
|
|
I have a table TBL_EVENTS of 70 millions rows. The column STATUT of the table TBL_EVENTS has 4 differents values : 01, 02, 03, 04.
90% of the rows have a statut equal to '04'.
My application is divided in 4 procedures :
- first one take all the events '01', make some operations and change the statut to '02',
...
- the last one take all the events '03', make some operations and change the statut to '04'
The rows with a '04' statut 're not used anymore. But we've to keep them. I can't split the table TBL_EVENTS. Too much impacts on the application.
I want to improve performance. One solution is to partition the table TBL_EVENTS on the column STATUT :
- partition 1 - range : '01', '02' and '03
- partition 2 - range : '04'
But I know there'll be a lot of row movements between partitions b'cos of the update on the STATUT.
Good choice ? Other solutions ?
Thanks.
[Updated on: Mon, 14 November 2005 11:02] Report message to a moderator |
|
|
|
| Re: Partitionning strategy - Row movement b'cos of Update on key partition [message #146920 is a reply to message #146910] |
Mon, 14 November 2005 10:56  |
sunil_v_mishra
Messages: 506
Registered: March 2005
|
Senior Member |
|
|
hi,
I dont think the way you are partitioning is good option because as per your post
---> 90% of the rows have a STATUT equal to '04'
it means nearly 54-55 million record in partition table having STATUT 04
You have to select some other field on which you have to partition your table....may be year may be date ...
And frankly speaking we dont know all the field of your table... so its up to you to decide on which field you have to partition table and how much partition you required.
Secondly your table contain many record so if you are thinking of normalization and then to implement the normalize table ...will be very painfull it will require lot of application design change.
Other method to improve the performance in your case is to create index on your table...
Find out which optimizer is used by SQL query which access your table having 70 million record. RBO or CBO
Note :- This is not the end , I hope you will get more reply from other Experts on your issue.
Regards
Always Friend Sunilkumar
|
|
|
|
| Re: Partitionning strategy - Row movement b'cos of Update on key partition [message #146932 is a reply to message #146910] |
Mon, 14 November 2005 15:24 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I agree you aren't going to accomplish much by putting 63 million out of 70 million in a single partition, unless your goal is to concentrate performance benefits on the remaining 7 million.
If so, then might want a single partition for each status. But like sunil said what other columns are in your table? More information on your situation and goals for the change would be helpful. Partitioning in and of itself won't magically help performance, need a goal and an approach to take.
Also, after the 01s go to 02s, are they ever used again?
|
|
|
|
| Re: Partitionning strategy - Row movement b'cos of Update on key partition [message #147025 is a reply to message #146932] |
Tue, 15 November 2005 06:45 |
silk
Messages: 5
Registered: November 2005
Location: France
|
Junior Member |
|
|
Hi,
You're right. The rows with a '04' statut 're not used anymore.
But we've to keep them. I can't split the table TBL_EVENTS in two different tables. Too much impacts on the application.
So my aim is to avoid working on the 04s. I can see only 2 ways : partitionning or index on STATUT.
The 01s, 02s and 03s can be used after. But if I create one partition by type of status there'll be a lot of row movement between partitions. Is it bad ??
The structure of table TBL_EVENT is :
TRANS_ID VARCHAR2(20) KEY,
EVT_TYPE VARCHAR2(10) KEY,
STATUT VARCHAR2(5) ,
...
Index B*tree : (TRANS_ID, EVT_TYPE)
[Updated on: Tue, 15 November 2005 06:55] Report message to a moderator |
|
|
|
| Re: Partitionning strategy - Row movement b'cos of Update on key partition [message #147062 is a reply to message #146910] |
Tue, 15 November 2005 10:11 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Yeah, give it a shot with the two partitions, one for 04 and one for the rest. But ideally you have some sort of date field you could use, you didn't finish listing the table columns...We don't have anything else to go on.
But if rows only change status once, and only some of them goto status 4, then there wont be all that much movement.
You'd probably want to compress the status 04 data, and will need to come up with something to compress the newly added 04 data on a regular basis.
Test it all and see if the movement is as bad as you think.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
