-
SQL performance tips wanted
Does anyone have articles about how to write good quality SQL statements in Oracle? Or anything such as tips, DO's or DONT's regarding SQL performance?
Thanks.
-
go to http://asktom.oracle.com
some good stuff there and his books are really good too
-
-
Jonathan Lewis book is also good.
Tamil
-
A couple of quick tips that will fix most performance problems:
When building your SQL, add a single table at a time to the statement and
1) restrict it by everything you possibly can, even if it seems redundant, and
2) join it to everything you possibly can, even if you know it is redundant.
Giving the optimizer as much information as possible and as many options as possible are the first steps to better SQL performance.
- Chris
-
Your Q I've answered in the dbforums.com as well...
http://www.dbforums.com/t926266.html
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
not to be fecious but rtfm, especially the concepts manual, there is wealth of info in there
I'm stmontgo and I approve of this message
-
Originally posted by chrisrlong
... join it to everything you possibly can, even if you know it is redundant...
... but try and avoid loops in the joins, ie. if A joins to B, and B joins to C, don't join A to C
-
Originally posted by slimdave
... but try and avoid loops in the joins, ie. if A joins to B, and B joins to C, don't join A to C
Sorry, but absolutely wrong in my book. You absolutely want to join A to C. I have an example in my (working on forever) book that shows how the optimizer can miss transitive joins if you don't do them explicitely - at least in 8i. Plus, I've never seen a case where the optimizer has a problem if you 'close the loop', as you say - I do it all the time.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|