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
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
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)