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.
Printable View
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
Thanks.
Quote:
Originally posted by davey23uk
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
not to be fecious but rtfm, especially the concepts manual, there is wealth of info in there
... but try and avoid loops in the joins, ie. if A joins to B, and B joins to C, don't join A to CQuote:
Originally posted by chrisrlong
... join it to everything you possibly can, even if you know it is redundant...
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.Quote:
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
- Chris