| Query Optimisation [message #161798] |
Tue, 07 March 2006 04:28  |
mhjoshi
Messages: 1
Registered: March 2006
Location: Pune
|
Junior Member |
|
|
Dear Sir / Madam,
I have a problem in my system. My system tracks of complaints on vehicle
It contains header information and detail information.
As a normal practice, one header record may have more that one detail records.
Also I classify complaints based on their age in the system meaning
1) First pair of tables contains complaint info for current day
2) Second pair contains complaint info for last 7 days
3) Third pair contains complaint info for last 8 weeks
4) Fourth pair contains complaint info for last 2 years
5) Fifth pair contains complaint info of complaints more that 2 year old
6) Sisth pair contains complaint info of deferred complaints
7) Seventh pair contains complaint info of deleted complaints
For ease of my queries I have created on view on all 7 header tables and other view on all 7 detail tables.
All seven header tables have index one on complaint_no and other on vehicle_no.
Similarly All seven Detail tables have index on combination of complaint_no and Part_no.
Here joining condition is complaint_no
When I query these two views passing complaint_no as parameter, I get answer in hardly one second.
But When I query these two views passing vehicle_no as parameter, the query is damn slow. It takes almost 5 mins
Please suggest how can I improve the performance.
For your kind information
I am using followig Oracle SQL Plus version.
------------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
------------------------------------------------------------------------
Regards,
Makarand Joshi
|
|
|
|
|
|
| Re: Query Optimisation [message #161838 is a reply to message #161827] |
Tue, 07 March 2006 07:12  |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
Would you not be better creating 1 pair of tables for all complaints then creating views on those tables for the appropriate 'date sections'
Presumably, on 'day2' you have to move all the rows that were in your 'current' tables into your weekold tables, al the rows that are more than a week old into the 8 week old tables etc etc. Surely this puts an unnecessary load on your system. Maybe I am mis-understanding your structure.
Jim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
