05 Simple Tuning Rules

Select Consideration

select * from sales_history

versus

Select s.order_ID, s.product_ID, s.price, s.product_name

from sales_order s

The simplest rule is to select only the data you need  instead of all of the data from the table.

Watch the size of the select * query …. its 55 MB

versus

The size is now only 29 MB

Use Table Aliases

Use table aliases and always prefix all columns with table aliases. Advantage is

• so that you can clearly understand where the column is coming from, i.e. which table and makes the query readable
• it reduces the parse time. Oracle parses the statement so that it can get the best execution plan. When it is parsing it scans each column and tries to figure out which table it is from. By providing the alias we are reducing the time it takes to figure out in which table the column is located

GOOD

select N. name, A.address

from name n, address a

where n.addressID=a.addressID
<p style="text-align: center;">versus</p>

BAD

select name,address

from name, address

where addressID=addressID

Using WHERE rather than HAVING

HAVING clause filters the rows only after all the rows have been fetched, grouped and sorted. So whenever possible try to filter the data using WHERE rather than HAVING. This reduces a lot of overhead on the database.

Notice that all the data is fetched (19M) before filtering is applied

versus

Fetched only one row and 15 bytes of data !!!!

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help  elevate my companies Search engine rankings)