Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

WHERE Clause Tips
Oracle Tips by Burleson
 

While most of these tips are generalizations, each of them has been proven to be effective much of time. In general, itís best to write your code according to these tips (making necessary allowances to meet your required functionality) and then differentiate from the tips as necessary to improve performance. These tips all apply to the WHERE clause for your SELECT, DELETE, and UPDATE statements:

  • Use the first column of the index. If necessary, use several columns of the index to assure that Oracle selects the index that you want to use. Knowing how to identify and use the indexes on a table is a very important skill.

  • If your statement references more than one table, make sure that each column is referenced with a table name or table alias. This avoids overhead required for Oracle to determine the table of each column.

  • If youíre using AND conditions, make sure that the condition most likely to cause the query to fail is tested first. This will save processing time by avoiding comparisons that will later be invalidated by a frequently occurring condition.

  • If youíre using OR conditions, make sure that the condition most likely to cause the query to fail is tested last. This will save processing by avoiding comparisons that are more likely to fail in favor of comparisons that are likely to succeed.

  • Donít join against unnecessary tables.

  • Join only columns of the same datatype and length.

  • Avoid the use of implicit datatype conversions.

  • Donít use any functions (whether built-in or user-defined) on the left side of an expression.

  • Try to avoid the use of the IN, ANY, ALL, BETWEEN, and NOT operators.

  • Use the >= operator instead of the LIKE operator wherever possible. If the LIKE operator must be used, try to avoid the use of %string% conditions with the LIKE operator. Using the LIKE operator is expensive because Oracle must step through each position in a string of text, which takes a considerable amount of processing time.

Remember, each of these tips is a generalization based on many individual statements. Successful performance tuning is the result of many hours of tedious work to wring out every bit of performance; these tips will only start you along that road.

There are some additional tuning tips that apply when youíre using Oracleís rule-based optimizer, which attempts to execute every SQL statement using the same method.

Rule-Based Optimizer

The tips outlined in this section are relevant only when using the rule-based optimizer. Most Oracle installations now predominantly use the cost-based optimizer, but use of the rule-based optimizer is still far from uncommon.

There are two primary conditions that you should be aware of when using the rule-based optimizer in your queries.

  • Indexed columns referenced in the WHERE clause of your query should be listed in the same order as the columns are included in the index. If your code doesnít follow this pattern, Oracle might use a less effective index to execute your query.

  • If you are joining multiple tables in your query, list the tables in the FROM clause in order from the largest to the smallest. This will allow Oracle to cache data from smaller tables so conditions can be evaluated against the data in larger tables.

If it seems like using the rule-based optimizer requires more work, thatís because it does. Using the rule-based optimizer requires you to be much more conscious of the conditions that exist in your data and the indexes in place on your tables.

While tuning SQL statements is the most common type of performance tuning, there are some general guidelines that can significantly improve the performance of your PL/SQL code as well.

 

             
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter