OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query tuning (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query tuning [message #377228]

Mon, 22 December 2008 00:20

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

Report message to a moderator

Re: Query tuning [message #377246 is a reply to message #377228]

Mon, 22 December 2008 02:11

Frank Naude
Messages: 4581
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!

Report message to a moderator

Re: Query tuning [message #377260 is a reply to message #377246]

Mon, 22 December 2008 03:01

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.

Report message to a moderator

Re: Query tuning [message #377288 is a reply to message #377260]

Mon, 22 December 2008 04:17

Frank Naude
Messages: 4581
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.

Report message to a moderator

Previous Topic:	What is difference between 2 statements?
Next Topic:	Querying the database faster

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 18:17:46 CST 2024