SQL performance tips wanted
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: SQL performance tips wanted

  1. #1
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    195

    Question 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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    go to http://asktom.oracle.com

    some good stuff there and his books are really good too

  3. #3
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    195
    Thanks.

    Originally posted by davey23uk
    go to http://asktom.oracle.com

    some good stuff there and his books are really good too

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Jonathan Lewis book is also good.

    Tamil

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Your Q I've answered in the dbforums.com as well...
    http://www.dbforums.com/t926266.html
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width