| 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.
|
|
|
|
| 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
|
|
|
|
| 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.
|
|
|
|
| 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?
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
