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.

Hints:

/* 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;

OPT_PARAM: 

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.