Shulou(Shulou.com)05/31 Report--

This article will explain in detail what is the common implementation plan of the index in the Oracle database. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Today, we will focus on the following common execution plans for Oracle indexes:

INDEX FULL SCAN: full scan, single block read, ordered index

INDEX RANGE SCAN: range scan of the index

INDEX FAST FULL SCAN: fast full scan of index, multi-block read, disorder

INDEX FULL SCAN (MIN/MAX): query for the MAX (), MIN () function

INDEX SKIP SCAN: the query condition does not use the first column of the composite index, which may be used when the first column of the composite index is highly repetitive.

Here we use a simple test case to experience the scenario in which the index uses these execution plans.

1. Prepare the test environment

Create test tables and indexes:

Drop table test_objects; create table test_objects as select * from all_objects; create index idx_test_objects_1 on test_objects (owner, object_name, subobject_name); create index idx_test_objects_2 on test_objects (object_id); desc test_objects

View the index information on the test table:

Select index_name, column_name, column_position from user_ind_columns where table_name = 'TEST_OBJECTS'

Analyze the table and empty the shared_pool and buffer_cache of the test environment:

Analyze table test_objects compute statistics; alter system flush shared_pool; alter system flush buffer_cache

two。 Prepare SQL statement

Write SQL statements according to the scenarios of different execution plans:

-- INDEX RANGE SCAN (range scan of index) SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name =' DBMS_OUTPUT';-- INDEX SKIP SCAN (query for MAX (), MIN () function) SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT';-- INDEX FAST FULL SCAN (fast full scan of index, multi-block read, unordered) SELECT owner, object_name FROM test_objects -- INDEX FULL SCAN (full scan of index, single-block read, ordered) SELECT owner, object_name FROM test_objects order by 1,2;-- INDEX FULL SCAN (MIN/MAX) (query for MAX (), MIN () function) SELECT max (object_id) FROM test_objects

3. Experimental demonstration

The results are as follows:

(1) INDEX RANGE SCAN

Set autotrace traceonly SELECT owner, object_name FROM test_objects WHERE owner = 'SYS' AND object_name =' DBMS_OUTPUT'

(2) INDEX SKIP SCAN

SELECT owner, object_name FROM test_objects WHERE object_name = 'DBMS_OUTPUT'

(3) INDEX FAST FULL SCAN

SELECT owner, object_name FROM test_objects

(4) INDEX FULL SCAN

SELECT owner, object_name FROM test_objects order by 1, 2

(5) INDEX FULL SCAN (MIN/MAX)

SELECT max (object_id) FROM test_objects

This is the end of the common execution plan of the index in the Oracle database. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.