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.

oracle explain plan fr select *

oracle explain plan fr select *

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

versus

Oracle explain plan for limited felds

Oracle explain plan for limited fields

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.

oracle explain plan using having clause

oracle explain plan using having clause

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

versus

oracle explain plan using where clause

oracle explain plan using where clause

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s