what are the tips, methods or preferred ways to optimize sql query.

---

Clarification of Question by teckleong-ga on 13 Apr 2005 21:10 PDT

performance tuning in terms of SQL language and Oracle db/Server

Not SQL-specific but these often help.

use an index when appropriate (more a database design issue)
use lazy evaluation
consider caching results rather than recalculating them

Experience.

A common mistake is to use a function in the where clause that forces
the database to scan the entie table to find the rows that meet the
criteria.  eg select ... from ... where to_upper(my_column) ='YES')

In the above situation you could use a function based index to speed
up the query. (see docs over at otn.oracle.com)

Another common mistake is to invalidate the index on a column eg
select ... from ... where my_column like '%astring%'  In this example,
the database can't use an index for the substring search.  If the
string is of limited lenght you can do several things.  (see
asktom.oracle.com and do a search or buy his books)

You want to minimize IO and there are ways to do that.  An index may
usually help, but not alywas.  There are so many possibilities and we
don't know what version of Oracle, what the query is, that the tables
are, what optimizer are you using etc.  Hard to help.

I agree scubajim in that the question is unspecific enough that it is
hard to give a specific answer, but that might be what you intended so
that you can get a general idea.

Well planned Indexes can save the db engine a lot of work. They is no
shame in having more indexes active on a table than the 'text-book'
database designer would have, so be generous to yourself.

Plan your SQL queries so that they are least likely to bring back more
records than you actually need. A simple example may be bringing back
records with *bert* in the name, do you realy want 'Robert' and
'Albert'? Was there a means in your table to be more specific?
Remember also that depending on your SQL function, you might be
applying locks to more records than is necessary. I have never liked
locking blocks of data, but sometimes it is necessary.

Sometimes, in reports or data analysis of a snapshot of data, it can
be better to have your SQL create a temporary table of results, that
speeds up processing at the server side and frees up the Live tables
more quickly.

If your particular engine can use stored functions, then it is often
worth making use of those, obviously not so dynamic as you can be with
your SQL texts but then many SQL texts on a database tend to be a
large mix of static text and a relatively small amount of dynamic
text.

In the tables themselves, good planning is important, avoid
duplicating fields in seperate tables, even if your design ends up
having more tables because of this. Drill down tables do not really
slow down a DB engine, in fact they are many times that these can
increase the speed of access.

Hmmm, i just wrote something on this ...
http://oraclesponge.blogspot.com/2005/04/writing-good-sql.html

All of the above suggestions are very good. The remaining question is
- how do you know where to start? Well, most major database products
(including Oracle) have tools to help you out. Generally, the feature
is named something like 'explain plan'. The concept is that you submit
your query to the tool, and the tool shows you how the query engine
will go about access the data to satisfy the query. The first thing to
look for in this 'plan' is the dreaded 'full table scan'. This is the
most simple problem to solve, usually by adding an appropriate index
on the affected table. After adding the index or altering your query
to be more efficient (like removing function calls from the WHERE
clause), you can execute another plan, and see if there is any
improvement. This is much easier than just repeatedly guessing at what
change to make, and then re-executing the query.