sakthiganesht

 Profile Answers by sakthiganesht Questions by sakthiganesht

• Feb 7th, 2008

 

Performance tuning and optimization of a query involves collecting statistics on join columns, avoiding cross product join, selection of appropriate primary index (to avoid skewness in storage) and using secondary index.

Avoiding NUSI is advisable.

debashish.munshi

 Profile Answers by debashish.munshi 

• Sep 18th, 2012

 

Optimization of queries improves performance of TD and helps the PE to generate the most efficient execution plan. To Optimize a query, choose the Primary Index to avoid skrew, Collect statistics on the joining columns, avoid using CAST, SUBSTR, CASE, POSITION, as these functions consumes a lot of resource. If two huge tables are joined almost regularly, create Joined Index.

tdguy

• May 22nd, 2013

 

Performance tuning:
1. Look at the plan generated by PE.
2. Check for the recommendations of stats to be collected for the columns.
3. Try to avoid product joins and unnecessary joins.
4. Try to avoid casting in join conditions
5. Use secondary index appropriately.
6. Use join index if necessary.