Home » RDBMS Server » Performance Tuning » Query tuning (Oracle 10g)
Query tuning [message #377228] Mon, 22 December 2008 00:20 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am new to tuning. The below query is part of a procedure which is being called many times. This needs to be run faster.

SELECT A.CustCustName CustName,B.LocDesc Location, A.TelNo TelNo, A.AccountNo AccountNo, 
	A.ContactPerson ContactPerson, A.LandMark Landmark, A.Address1 Address
	FROM MCUSTCUSTOMER A 
	INNER JOIN MLOCATION B ON A.OffCode = B.OffCode AND A.LocCode = B.LocCode 
	WHERE A.CustCustomerCode = '0002' 
	AND A.OffCode = '1001'
	AND A.COMPCODE= '005'


In above query INNER JOIN is used.....what if the join is simply written in WHERE clause as under? Does it effects the speed of query in any way or what is the correct way of writting.

SELECT A.CustCustName CustName,B.LocDesc Location, A.TelNo TelNo, A.AccountNo AccountNo, 
	A.ContactPerson ContactPerson, A.LandMark Landmark, A.Address1 Address
	FROM MCUSTCUSTOMER A, MLOCATION B
       WHERE A.OffCode = B.OffCode
         AND A.LocCode = B.LocCode 
	 AND A.CustCustomerCode = '0002' 
	 AND A.OffCode = '1001'
	 AND A.COMPCODE= '005'


Also for indexes, there is only a clustered index on MCUSTCUSTOMER (CustCustomerCode,COMPCODE)
There is no index on any other column.On table MLocation there is a Clustered Index (LocCode,OffCode)
Do I need to create another NonClustered Index on MCUSTCUSTOMER(CustCustomerCode,OffCode,COMPCODE)
Also the index in MLocation needs to have he order of column changed.

Please advice as I am new to tuning and don't want to make create indexes on fly as this is a System in production.

Thanks in advance,
Mahi
Re: Query tuning [message #377246 is a reply to message #377228] Mon, 22 December 2008 02:11 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Well, you need to run the two statements and compare the results. This should help: EXPLAIN PLAN and SQL_TRACE.

And, if you don't want to play with indexes on production, your first step should be to copy the objects and data to a development/test system where you can.

Best of luck!
Re: Query tuning [message #377260 is a reply to message #377246] Mon, 22 December 2008 03:01 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I am working on Development only but just enquired if making more indexes would not hinder the performance anyway. Meanwhile I am taking out the explain plan.
Re: Query tuning [message #377288 is a reply to message #377260] Mon, 22 December 2008 04:17 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Indexes must be maintained, so DML will be slightly slower. Your queries, however, should be a lot faster. You need to design the solution to get maximum query performance with the minimum set of indexes.
Previous Topic: What is difference between 2 statements?
Next Topic: Querying the database faster
Goto Forum:
  


Current Time: Mon Dec 05 12:40:46 CST 2016

Total time taken to generate the page: 0.05767 seconds