Home » RDBMS Server » Performance Tuning » Query Optimisation
Query Optimisation [message #161798] Tue, 07 March 2006 04:28 Go to next message
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 #161827 is a reply to message #161798] Tue, 07 March 2006 06:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post your SQL, plan and stats.
Did you collect statistics?
Post the count of records on tables and the concerned column values

Re: Query Optimisation [message #161838 is a reply to message #161827] Tue, 07 March 2006 07:12 Go to previous message
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
Previous Topic: ORDER BY CLAUSE
Next Topic: Performance tuning of Oracle 8i
Goto Forum:
  


Current Time: Thu Mar 28 08:53:13 CDT 2024