Different Optimization Techniques

{\rtf1\ansi\ansicpg1252\deff0\deflang16393{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fswiss\fprq2\fcharset0 Tahoma;}{\f2\fswiss\fprq2\fcharset128 Arial Unicode MS;}{\f3\fnil\fcharset0 Calibri;}}
{\colortbl ;\red0\green0\blue204;\red0\green0\blue0;}
{\stylesheet{ Normal;}{\s1 heading 1;}{\s2 heading 2;}{\s3 heading 3;}{\s4 heading 4;}{\s5 heading 5;}{\s6 heading 6;}{\s7 heading 7;}{\s8 heading 8;}}
{\*\generator Msftedit 5.41.21.2509;}\viewkind4\uc1\pard\lang1033\f0\fs22\par
\pard\li720\b Optimizer:\par
\b0 Optimization is the process of choosing the most efficient way to execute an SQL statement.\cf1\f1\fs18 \cf0\f0\fs22 When tuning SQL, it\rquote s very important to understand WHY and WHEN a particular type of optimization should be used.\cf1\f1\fs18 \cf2\v\f0\fs22\par
\pard\li1440\cf0\v0\line\cf2\v\par
\pard\li720\cf0\v0 When a SQL Select, Update, Insert or Delete statement is processed, Oracle must access the data referenced by the statement. The Optimizer portion of Oracle is used to determine an efficient path to reference data so that minimal I/O and Processing time is required. For statement execution the Optimizer formulates execution plans and chooses the most efficient plan before executing a statement.\par
The Optimizer uses one of two techniques:\par
\b Rule Based Approach:\b0 Using the rule-based approach, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths. If there is more than one way to execute an SQL statement, the rule-based approach always uses the operation with the lower rank. Usually, operations of lower rank execute faster than those associated with constructs of higher rank. The rule-based optimizer is no longer being enhanced by Oracle. The rule-based optimizer (RBO) Uses a fixed ranking, therefore, it\rquote s essential to list the correct Table Order in the FROM clause. Normally, the (LAST or Right Most) Table listed in the FROM clause is the driving table. The data distribution and number of records is not taken into account. The RBO execution plan can\rquote t be changed by hints.\par
\cf2\b\v\f2\par
\par
\f0\par
\par
\cf0\v0 Cost Based Approach:\cf1\b0\f1\fs16 \cf0\f0\fs22 Using the cost-based approach, the optimizer determines which execution plan is most efficient by considering available access paths and factoring in information based on statistics in the data dictionary for the schema objects (tables, clusters or indexes) accessed by the statement. The cost-based approach also considers hints, or optimization suggestions placed in a Comment in the statement. By default, the goal of the cost-based approach is the best throughput, or minimal resource use necessary to process all rows accessed by the statement. The cost-based approach uses statistics to estimate the cost of each execution plan. These statistics quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. These statistics are generated by using the ANALYZE command. The cost-based optimizer was built to make tuning easier by choosing the best paths for poorly written queries. Normally, the (FIRST or Left Most) Table listed in the FROM clause is the driving table. Just opposite of RBO. The CBO decisions are only as good as the type and frequency of analyzes made on the Table and associated Indexes. To Tune SQL Queries using CBO you must also have an understanding of Table Access Methods, Table Join Methods and Hints.\par
\pard\keepn\s8\fi720\b The DRIVING Table\v\par
\pard\li720\par
\pard\v0\par
\pard\li720\b0 In Oracle the cost-based approach uses various factors in determining which table should be the Driving Table (the table that has the most \ldblquote restricted\rdblquote rows). It is the table that drives the query in a multi-table join query. The Driving Table should be listed First in Cost Based and listed Last in Rule Based Optimization. The best thing to remember is that you have control over which table will drive a query through the effective use of the ORDERED hint when using the CBO. The easiest way to determine if your query is using the correct Driving Table is to set the SQL Select to find Zero records. If the return is slow you know the Driving Table is incorrect!\par
\pard\sa200\sl276\slmult1\lang9\f3\par
}