3 Replies Latest reply on Feb 1, 2013 8:38 AM by don123

    optimal SQL


      when i see performance tuning in orafaq.com, they mentioned the following with respect to SQL.

      Application Tuning:
      Experience shows that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL.

      i would like to know is there any document with guidelines (dos and donts) on writing optimal SQL ?

        • 1. Re: optimal SQL
          Nikolay Savvinov

          in order to code good SQL, you need to understand how the database executes it, and how to make sure that a statement is executed with as little resources spent on it as possible. It's not the matter of following a list of "DOs" and "DON'Ts", it's the question of understanding what you're doing. Read a good book on Oracle performance tuning (you can start by T. Kyte's "Effective Oracle by Design" if you haven't read it already) and then move on to something more serious, like J. Lewis's "Cost Based Fundamentals".

          Best regards,
          • 2. Re: optimal SQL
            Frank Kulash

            Here are a few things I try to keep in mind when writing SQL, and some common mistakes I've noticed.

            Optimal SQL starts before you even write a query; it starts with a good table design.
            Normalize your tables.
            Use the right datatype. A common mistake is to use a VARCHAR2 or NUMBER column when a DATE is appropriate.

            Use SQL instead of PL/SQL, expecially PL/SQL that does DML one row at a time. MERGE is a very powerful tool in pure SQL.

            Help the optimizer.
            Write comparisons so that an indexed column is alone on one side of the comparison operator. For example, if you're looking for orders that are more than 60 days old, don't say
            WHERE   SYSDATE - order_date  > 60    -- *** INEFFICIENT  ***
            write it this way instead
            WHERE   order_date  < SYSDATE - 60
            Some tools are inherently slow. These include<ul>
            <li>SELECT DISTINCT
            <li>Regular expressions
            <li>CONNECT BY</ul>
            All of these are wonderful, useful tools, but they have a price, and you can often get the exact results you need faster with some weaker tool, even if it requires a little more code.
            • 3. Re: optimal SQL

              thanks to both of you for your suggestions.