Use Oracle9i full index scan to quickly access data

To fulfill Oracle's commitment to adding SQL-optimized query intelligence, Oracle9i enhances the full-index SQL Execution Plan to support function-based indexes based on functions ). In Oracle8, the SQL optimizer adds intelligence to determine whether a query can be specifically implemented using an existing index. Once an index exists, Oracle will bypass table access. The index organization table index-organized table, IOT) structure is an example. In the IOT structure, all the data is loaded into the B-tree structure of the index, so that the table) becomes a superfluous thing.

Once the Oracle SQL optimizer detects that the query does not require table access, Oracle calls full index scan and quickly reads each index block without touching the table itself. One thing is very important: full index scan does not read the index node, but performs a scan in one piece and quickly captures the index node. It is recommended that Oracle call the multi-block READ function and call multiple processes to read tables.

Oracle and multi-block read

To speed up table and index access, Oracle uses the default db_file_multiblock_read_count parameter 8) to help send the data blocks obtained by full table scan and full index scan to the data buffer as soon as possible. However, this parameter is only available when the SQL query performs a full table scan. In most cases, the query uses indexes to access the table.

Oracle has the following restrictions on full index scanning:

All columns in the SQL request must reside in the index tree. That is, all data columns in the SELECT and WHERE statements must exist in the index.

Query to access a large number of rows ). According to the range of your query, the ratio ranges from 10% to 25%. The setting of the ratio parameter db_file_multiblock_read_count and the parallel query degree greatly affect this ratio.

The index nodes are not listed in the index order, so the columns are not listed in the order. In this way, the order by clause requires additional sorting operations.

Oracle provides an SQL prompt hint) to force full index scanning. You can also specify the index_ffs prompt to force fast index scanning, which is often combined with the parallel_index prompt to improve performance. For example, the following query forces parallel fast full index scanning:

select distinct /*+ index_ffs(c,pk_auto) parallel_index_ (automobile, pk_auto) color, count(*) from automobiles group by color;

Because all the variables are involved, it is not easy to answer whether the full index will accelerate the query speed. Therefore, most experienced SQL debugs, tuner) perform manual timing on queries that meet the fast full index scan standard to see if the full index scan reflected time is reduced.

Before Oracle9i, full index scan can be used only when the created index has no NULL value. That is to say, an not null clause must be used to create an index in Oracle. As Oracle9i supports the use of function-based index to implement the unique index scan, this situation has greatly improved.

In a simple regression, function indexing is an important improvement of Oracle8 because it provides an effective mechanism to eliminate unnecessary full scanning of long tables, since function indexes can be precisely replicated in the WHERE clause of any query statement, Oracle always uses an index to match the WHERE clause of the preceding SQL query.

Now, I will use a student table to give a simple example to explain how full index scanning works with function indexes.

Create a student table:

(student_name varchar2(40), date_of_birth date);

Use this table to create function indexes associated with all columns in the table. In this example, the function is initcap, that is, the first letter of each word in uppercase.) And to_char convert a number into a character ):

create index whole_student on student (initcap(student_name), to_char(date_of_birth,’MM-DD-YY’));

After defining the function index, any SQL statement in Oracle9i that can reference these columns can use full index scanning. The following is an example of SQL matching function index query:

select * from student where initcap(student_name) = ‘Jones’; select * from student where to_char(date_of_birth,’MM-DD=YY’) = ’04-07-85’;

Function Index to call full index Scan

Oracle9i will use the function index whenever possible and call the full index Scan Based on the function index. If the statistical results of the SQL optimizer show that the full index scan speed will exceed the access speed through the index B-tree, then Oracle9i will do so.

The following is the rule for using function indexes to call a unique scan. All SQL predicates match the columns in the index. The query must return enough rows from the table to enable the cost optimizer (cost-based optimizer) to determine whether full index scanning is faster than traditional index access methods. Whether to call full index scan depends on the following parameter settings:

Appropriate statistics for the cost optimizer-schema of this Plan) have been analyzed recently, and the optimizer_mode parameter cannot be set to RULE.

The degree of parallelism of indexes-note that the degree of parallelism of indexes is set independently; the degree of parallelism of indexes does not inherit the table. Optimizer_index_cost_adj settings -- it controls whether the cost optimizer tends to perform full index scanning. Db_file_multiblock_read_count setting -- this parameter affects the cost of full index scanning. The higher the value, the more expensive the full index scan will be ". The histogram representation of the index-for the offset skewed) index, which helps the cost optimizer evaluate the number of rows returned by the query.

An important improvement of Oracle

Fast full index scanning based on function indexes is another way to improve the performance of Oracle9i. When the database is migrated to Oracle9i, many databases automatically start to use this new execution plan. However, when the SQL cost optimizer determines whether to choose full index scanning, several factors should be considered. Oracle professionals need to set parameters appropriately to ensure that the cost optimizer does not use an inappropriate method for fast full index scanning-this requires special attention.

(