 Partition & Performance [message #164767] |
Sat, 25 March 2006 16:57  |
lucas4394
Messages: 24
Registered: October 2005
|
Junior Member |
|
|
We have a large table (3GB, 12 million rows, 12 GB index) that holds security data. This size of this table increases rapidly
weekly. We also have some stored procedures loading data to
this table daily or weekly, however, the performance of the stored procedures are very slow even we optimized all queries from stored
procedures. We are thinking if some partitioning scheme will improve performance. If anyone has any suggestions, that will
be a great appreciated.
Thanks,
Lucas
|
|
|
|
|
|
| Re: Partition & Performance [message #164777 is a reply to message #164767] |
Sun, 26 March 2006 00:09  |
lucas4394
Messages: 24
Registered: October 2005
|
Junior Member |
|
|
Thank you for your suggustion, if I have time, I will read
this book. However, i really need to know how partition can
improve the performance and if it is fit in my case.
Lucas
|
|
|
|
|
|
| Re: Partition & Performance [message #164838 is a reply to message #164793] |
Mon, 27 March 2006 00:30 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Seriously, @anacedent's advice - whilst abrupt - is good.
However, I sense that you don't want good advice, you just want your question answered. So here goes:
Take a look at this doc I wrote - it descibes how partitioning may be used to improve performance. Any SQL that does not fit this model will probably stay the same or become slightly slower. It is unlikely to slow anything down significantly.
If you are looking for a free tuning resource, you can try the Oracle Performance Tuning manual, or look at the rest of my website.
_____________
Ross Leishman
|
|
|
|
| Re: Partition & Performance [message #165538 is a reply to message #164838] |
Thu, 30 March 2006 20:21 |
lucas4394
Messages: 24
Registered: October 2005
|
Junior Member |
|
|
Thanks anacedent and rleishman for the good advices.
We are thinking about partition by different application
categories. Since we already have lots of stored procedures,
web applications are using this table, we don't know how
the changes effect our current tasks, for example, do we need
to modify the queries in search and update?
Lucas
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
