Oracle Optimizer: Migrating to using the cost-based optimizer-----series 1.3

oracle| optimization
Oracle Optimizer: Migrating to using the cost-based optimizer-----series 1.3



3.2.3 Available CBO mode

The CBO has two modes of operation: All_rows and First_rows.

The goal of First_rows is to return rows as quickly as possible. This pattern tells the optimizer that response time is most important. It prefers nested join methods. First_rows also deals with rows with cost as the primary rule. The rule is to use the plan of the index instead of using a full table scan as the access path, the ORDER BY clause causes index access, and so on

From the 9i version, the number of rows in the Frist_rows_n can be set in the parameters, and (N can be 1,10,100 or 1000), set as required by different applications

All_rows handles all rows for a given query before returning the output result. It forces the optimizer to consider minimal resource consumption and optimal throughput. All_rows prefers sort merge operations

For an OLTP system, first_rows is an ideal option for fast response times.

An application for batch orientation means the use of all_rows. Note that a plan that returns the nth row with the fastest response time may not be an ideal plan if the system requirements get the full results. Therefore, it should be determined according to the needs of the application.

When the database size changes, the CBO dynamically adjusts its execution path, so if you have the same query for the same application, a database setup that works well and runs poorly in another database, you don't have to bother. This will happen if you set up and have different statistics in two databases. To prevent this from happening. Consider using optimizer persistence. The section will be mentioned later.



3.2.3 CBO Basic terminology

The following terms will be used frequently when the CBO analyzes statements



Cost (costs)

In the CBO, cost calculation is involved in each unit of operation. How much of the cost of logic is actually not documented or published to the outside world, or even changes with different versions.



Cardinality (Base)

The number of single rows in the index and the number of rows in the table. The query base is the number of rows you want to return.



Selectivity (optional)

The number of single values that are treated as selectivity by a single value of the indexed column. For example, if a table has 10000 rows, an index created has 4000 single values in one column, the index is 4000/10000*100=40%, and the unique index on a column that is not empty is optional to 100 %.



Transitivity (transitivity)

For queries, the CBO generates the processing of additional predicates. It enables the optimizer to consider additional execution paths. For example, if you provide a predicate for a=b and b=c in a query, the optimizer may add additional predicates to imply a=c.



Statistics (Statistical data)

Gather a lot of the necessary information for a variety of different data to support the object. It is critical to the CBO's implementation plan.





Join Methods (Join method)

Oracle joins with a hash, a merge sort, and a nested connection. A query might run faster compared to other methods. The connection method should be evaluated for a single query.



FTS (full table Scan)

Full table scans involve queries, from the first block to the last allocated block scans. For large tables It is extremely expensive and should be avoided.



Index Scan (indexed scan)

involves random access on a table by using one or more indexes on the table.