This content has been marked as final. Show 3 replies
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".
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
write it this way instead
WHERE SYSDATE - order_date > 60 -- *** INEFFICIENT ***
Some tools are inherently slow. These include<ul>
WHERE order_date < SYSDATE - 60
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.
thanks to both of you for your suggestions.