| Performance tuning [message #494306] |
Sun, 13 February 2011 02:03  |
ashish_sun123
Messages: 52
Registered: November 2010
Location: Bangalore
|
Member |
|
|
There are 5 tables a,b,c,d,e
a is selecting 10 records out of million records
b is selecting 10000 records out of 50000 records
c is selecting none out of million records
d is selecting 5 records out of million records
e is selecting 50000 records out of 100000 records
How these tables will/should be placed in from and where clause so that the query runs faster?
Alss is it that only running the TKPROF will provide the solution for this? If yes please explain
|
|
|
|
|
|
|
|
|
|
| Re: Performance tuning [message #494338 is a reply to message #494315] |
Sun, 13 February 2011 11:48  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In older, outdated, currently unsupported versions of Oracle, the order of things in the from clause and where clause affected the execution plan, especially the driving table in the from clause. The driving table was the table with the fewest rows after the filter conditions were applied to it. With current versions of Oracle (10g and 11g) and even 9i which is now unsupported, it does not matter, as Oracle's optimizer will automatically choose the best execution plan, regardless of the order of tables in the from clause or anything in the where clause.
There are many other things that will still affect the execution plan, such as indexes, statistics, and hints. You can use autotrace to see what execution plan Oracle plans to use. On rare occasions, it does something different from what is planned, and tkprof will show that. Mostly, all you need to see what Oracle plans to do is autotrace or explain plan.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
