Oracle SQL Tuning Tips - 3

• Oracle automatically performs simple column type conversions(or casting) when it compares columns of different types. Depending on the type of conversion, indexes may not be used. Make sure you declare your program variables as the same type as your Oracle columns, if the type is supported in the programming language you are using.Use:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_no = ’123′;
  HERE if emp_no indexed numeric, then after implicit conversion query will be:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_no = TO_NUMBER(’123′);
  Thus, index is used in this case.
  Don’t use:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_type = 123;
  HERE if emp_type is indexed varchar2, then after implicit conversion query will be:
  SELECT emp_no, emp_name, sal FROM emp WHERE TO_NUMBER(emp_type) = 123;
  Thus, index will not be used in this case.
•  
• The most efficient method for storing large binary objects, i.e. multimedia objects, is to place them in the file system and place a pointer in the DB.
• B-Tree Indexes do not store entries for NULL, so IS NULL is not indexable, but IS NOT NULL is indexable and thus if a huge table contains very few not null values then you should go for B-Tree indexes. On the other hand bitmap indexes support IS NULL condition.SELECT * FROM big WHERE status IS NOT NULL; (Use B-tree index in this case)
• Avoid using functions on indexed columns unless a function-based index is created; as it leads to full table scan even though index exists on the column.

Avoid using the following:

• Boolean operators >, <, >=, <=, IS NULL, IS NOT NULL
• NOT IN, !=
• Like ‘%pattern’, not exists
• Calculations on unindexed columns or (use union instead)
• Having (use a WHERE clause instead when appropriate)

Do use the following:

• Enable aliases to prefix all columns
• Place indexed columns higher in the WHERE clause
• Use SQL Joins instead of using sub-queries
• Make the table with the least number of rows, the driving table, by making it first in the FROM clause

Other important points for SQL Tuning

• Establish a tuning environment that reflects your production database
• Establish performance expectations before you begin
• Always Design and develop with performance in mind
• Create Indexes to support selective WHERE clauses and join conditions
• Use concatenated indexes where appropriate
• Consider indexing more than you think you should, to avoid table lookups
• Pick the best join method
• Nested loops joins are best for indexed joins of subsets
• Hash joins are usually the best choice for “big” joins
• Pick the best join order
• Pick the best “driving” table
• Eliminate rows as early as possible in the join order
• Use bind variables. Bind variables are key to application scalability
• Use Oracle hints where appropriate 
• Compare performance between alternative syntax for your SQL statement 
• Consider utilizing PL/SQL to overcome difficult SQL tuning issues
• Consider using third party tools to make the job of SQL tuning easier

Performing these steps is easy and provides a tremendous benefit and performance boost. Follow these simple steps and you can increase your system performance.

More --> Oracle SQL Tuning Tips

Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you
https://twitter.com/datastage4you
For WHATSAPP group , drop a msg to 91-88-00-906098