OraFAQ Forum: Server Administration » Performance with general selecetion statments

Home » RDBMS Server » Server Administration » Performance with general selecetion statments

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

Performance with general selecetion statments [message #370689]

Wed, 19 January 2000 11:58

Petey
Messages: 2
Registered: January 2000

Junior Member

is there an issue with performance on general selection statements between 2 and 3 tables? That is I use

select -------, -------, ------, etc

from tablea a, tableb b, table c

where a.info = b.info
and a.something = c.something
and a.whatever = b.whatever
and b.who != c.who;

is there an order in which each case should be ordered?
I have many insert into statements that use these general select statements that could impact the overall preformance of the product if it is an issue.

Report message to a moderator

Re: Performance with general selecetion statments [message #370691 is a reply to message #370689]

Wed, 19 January 2000 14:44

Amit Chauhan
Messages: 74
Registered: July 1999

Member

Hi,
While making a select statement, try to give the where condition which filters out most of the rows as first in the WHERE clause, and then subsequently the other statements. This will result in less execution time. Also you can check that it should use the right indexes.

Thanks
Amit

Report message to a moderator

Re: Performance with general selecetion statments [message #370704 is a reply to message #370689]

Thu, 20 January 2000 19:51

Greg Skakun
Messages: 10
Registered: January 2000

Junior Member

You will not find this documented in the Oracle manuals but as a general rule you should always list the key table that you are referencing in in your where clause as the last table in your "from" clause. The order of the tables in the "From" clause can affect how the optimiser chooses its execution path as proven by my experience with TKPROF and explain plan.

Report message to a moderator

Re: Performance with general selecetion statments, Optimizer Syntax [message #370707 is a reply to message #370689]

Fri, 21 January 2000 08:04

Petey
Messages: 2
Registered: January 2000

Junior Member

what is the syntax you use for the optimizer in such a clause as a SELECT FROM WHERE? I have found plenty of documentation on what statements like analyze, explain plan, etc but no documentation on how these statements are used within your code. If they are system calls then do you call these statements on a sql file that has these clauses in them or are these optimizer calls placed within the code itself?

Report message to a moderator

Re: Performance with general selecetion statments, Optimizer Syntax [message #370711 is a reply to message #370689]

Fri, 21 January 2000 14:42

Greg Skakun
Messages: 10
Registered: January 2000

Junior Member

You may have mis-read my message. It has been my experience in tuning SQL statements tha the optimiser will determine an execution path that is most optimal if you code the table that is most referenced by your where clauses at the end of the from clause.

I've used explain plan and tkprof a lot to analyze sql statements to determine how the optimizer chooses its execution paths.

Report message to a moderator

Previous Topic:	Viewing Bind Variable Values on the Database
Next Topic:	view problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 14:00:54 CDT 2025