Home » RDBMS Server » Server Administration » Performance with general selecetion statments
Performance with general selecetion statments [message #370689] Wed, 19 January 2000 11:58 Go to next message
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.
Re: Performance with general selecetion statments [message #370691 is a reply to message #370689] Wed, 19 January 2000 14:44 Go to previous messageGo to next message
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
Re: Performance with general selecetion statments [message #370704 is a reply to message #370689] Thu, 20 January 2000 19:51 Go to previous messageGo to next message
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.
Re: Performance with general selecetion statments, Optimizer Syntax [message #370707 is a reply to message #370689] Fri, 21 January 2000 08:04 Go to previous messageGo to next message
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?
Re: Performance with general selecetion statments, Optimizer Syntax [message #370711 is a reply to message #370689] Fri, 21 January 2000 14:42 Go to previous message
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.
Previous Topic: Viewing Bind Variable Values on the Database
Next Topic: view problem
Goto Forum:
  


Current Time: Thu Mar 28 15:11:24 CDT 2024