- Optimize access structures:
- Database design and normalization.
- Tables: heap or index-organized tables, and table or indexed clusters.
- Materialized views.
- Partitioning schemes.
- Statistics, including a comprehensive refresh strategy.
- Rewrite SQL statements:
- Exclude projections that are not required.
- Minimize the amount of work done more than once.
- Factor subqueries that are used multiple times in the same statement.
INbecause the former stops processing once it has found a match.
DECODEto avoid having to scan the same rows over and over again, especially for aggregation functions that act on different subsets of the same data.
- Use analytic functions to do multiple or moving/rolling aggregations with a single pass through the data.
- Avoid scalar subqueries in the
- Use joins instead of subqueries, as it gives the optimizer more room to play around in.
- Say what you mean and pick the right join: if you only need an inner join don’t write an outer join.
- Add logically superfluous predicates that may still aid in the search for an optimal execution plan, particularly for outer joins.
- Avoid implicit conversions of data types, especially in the
WHEREclause predicates with a close eye on the indexes available, including the leading edge of a composite index.
- Avoid, whenever possible, comparison operators such as
NOT EXISTS, and
LIKEwithout a leading
'%'for indexed columns in predicates.
- Do not apply functions on indexed columns in the
WHEREclause when there is no corresponding function-based index.
- Don’t abuse
HAVINGto filter rows before aggregating.
- Avoid unnecessary sorts, including when
UNION ALLrather than
DISTINCTunless you have to use it.
- Use PL/SQL, especially packages with stored procedures (and bind variables) and shared cursors to provide a clean interface through which all data requests are handled.
- Add hints once you have determined that it is right and necessary to do so.
krishna kumar on How to create virtual host in… vipul on To get current location using… How to create virtua… on How to create virtual host in…
- March 2018
- December 2017
- October 2017
- July 2017
- June 2017
- May 2017
- April 2017
- March 2017
- February 2017
- January 2017
- December 2016
- October 2016
- September 2016
- July 2016
- June 2016
- May 2016
- March 2016
- February 2016
- January 2016
- December 2015
- November 2015
- October 2015
- July 2015
- March 2014
- September 2013
- June 2013