Friday, June 24, 2011

Oracle Sql Optimization with Hints

SQL Optimization:

  • Check all tables must have joins in the where clause.
  • If possible you can put more joins too.
  • Check all the columns what id using in where clause for joins or filters has indexes. If not try to create indexes.
  • Try to reduce the in class with exists.
  • Check all sub queries has joins with main tables.
  • Then get the explain plan and check which table has full scan. If the table has look ups that has full join not a problem. If other table like millions of records and going through full join then try to crate a index on that table.
  • You can use Hints finally to improve the performance. By hint you are forcefully run the query using that hint.

/* Leading(LINES, HEADS) */
Instructs the optimizer to use the specified set of tables as the prefix in the execution plan.

select /* Leading(OL) */  
from oe_order_lines ol, oe_order_headers oh where ol.order_header_id = oe.order_header_id. 

/* Index(lined linx_id)*/ 
Explicitly chooses an index scan for the specified table. You can use the INDEX hint for domain, B*-tree, and bitmap indexes. However, Oracle recommends using INDEX_COMBINE rather than INDEX for bitmap indexes because it is a more versatile hint

SELECT /*+ index(hr_emp emp_id_idx)*/ last_name
FROM employees hr_emp;

lets you set an initialization parameter for the duration of the current query only. 
this hint is valid only for the  following parameters: optimizer_dynamic_sampling, optimizer_index_caching, 
optimizer_index_cost_adj, optimizer_secure_view_merging, and star_transformation_enabled.for example, 
the following hint sets the parameter star_transformation_enabled to true for the statement to which it is added.
select /* opt_param('optimizer_index_cost_adj',0) opt_param('optimizer_index_caching',99) */  from oe_order_lines ol, oe_order_headers oh where ol.order_header_id = oe.order_header_id. 

No comments:

Post a Comment