SQL Tuning Basics Overview 10

Tag: Time Condition number column arch OSI user CTE code

In section 1.5 of the SQL Tuning Basics Overview 05-oracle Index Type and introduction, several "Common index execution plans" are mentioned:

Index full Scan: Fully scanned indexes, block read, ordered
Index range Scan: Range sweep of index
Index fast full Scan: Fast all scans of indexes, multiple reads, unordered
INDEX full SCAN (Min/max): Query for MAX (), MIN () function
Index SKIP SCAN: The first column of the combined index is not used by the query condition, and the first column of the composite index is high in repetition, which may be used

This article uses a simple test case to understand the scenarios under which indexes use these execution plans:

• 1. Prepare the test environment
• 2. Writing SQL statements
• 3. Experimental environment Verification

1. Prepare the test environment

To create a test table and index:

conn jingyu/jingyudrop 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);

To view the test table structure:

sql> desc test_objects;    Name Null? Type-----------------------------------------------------------------------------------------------------------                                                       ----------OWNER not NULL VARCHAR2 (object_name)                                                             Not NULL VARCHAR2 (subobject_name) VARCHAR2 (+) object_id not NULL number Data_obje                                                                CT_ID number Object_type VARCHAR2 (+) CREATED not NULL DATE LAS                                                                  T_ddl_time not NULL DATE TIMESTAMP   VARCHAR2 STATUS                                                                  VARCHAR2 (7) Temporary VARCHAR2 (1) GENERATED Varc                                                         HAR2 (1) Secondary VARCHAR2 (1) NAMESPACE                                                               Not NULL number Edition_name VARCHAR2 (30)

To view index information on a test table:

SQL> select index_name, column_name, column_position from user_ind_columns where table_name = ‘TEST_OBJECTS‘;INDEX_NAME                     COLUMN_NAME                              COLUMN_POSITION------------------------------ ---------------------------------------- ---------------IDX_TEST_OBJECTS_1             OWNER                                                  1IDX_TEST_OBJECTS_1             OBJECT_NAME                                            2IDX_TEST_OBJECTS_1             SUBOBJECT_NAME                                         3IDX_TEST_OBJECTS_2             OBJECT_ID                                              1

Analyze the tables 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;

2. Writing SQL statements

Write SQL statements based on scenarios for different execution plans:

--INDEX RANGE SCAN（索引的范围扫描）SELECT owner, object_name FROM test_objects WHERE owner = ‘SYS‘ AND object_name = ‘DBMS_OUTPUT‘;--INDEX SKIP SCAN（针对MAX(),MIN()函数的查询）SELECT owner, object_name FROM test_objects WHERE object_name = ‘DBMS_OUTPUT‘;--INDEX FAST FULL SCAN（索引的快速全扫描，多块读，无序）SELECT owner, object_name FROM test_objects;--INDEX FULL SCAN（索引的全扫描，单块读，有序）SELECT owner, object_name FROM test_objects order by 1, 2;--INDEX FULL SCAN (MIN/MAX)（针对MAX(),MIN()函数的查询）SELECT max(object_id) FROM test_objects;

3. Experimental environment Verification

According to the SQL 2 in the experimental environment to verify, no problem, the results are as follows:

Sql> set Autot trace--1. INDEX RANGE scansql> SELECT owner, object_name from test_objects WHERE owner = ' SYS ' and object_name = ' dbms_output '; Execution Plan----------------------------------------------------------plan hash value:3492129186--------------- ------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |---------------------------------------------------------------------------------------| 0 |                    SELECT STATEMENT |     |    1 |     29 | 3 (0) | 00:00:01 | |  * 1 | INDEX RANGE scan|     Idx_test_objects_1 |    1 |     29 | 3 (0) | 00:00:01 |---------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------1-access ("OWNER" = ' SYS ' and "object_name" = ' dbms_output ') Statistics----------------------------------------------------------        Recursive calls 0 DB block gets 104 consistent gets physical reads 0 r Edo Size 676 Bytes sent via sql*net to client 519 bytes received via sql*net from client 2 sql*n ET roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 2 rows processed--2. INDEX SKIP scansql> SELECT owner, object_name from test_objects WHERE object_name = ' dbms_output '; Execution Plan----------------------------------------------------------plan hash value:1228438998--------------- ------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |---------------------------------------------------------------------------------------| 0 |                    SELECT STATEMENT |     |    2 |    58 | 27 (0) | 00:00:01 | |  * 1 | INDEX SKIP SCAN |     Idx_test_objects_1 |    2 |    58 | 27 (0) | 00:00:01 |---------------------------------------------------------------------------------------predicate information (identified By Operation ID):---------------------------------------------------1-access ("object_name" = ' dbms_output ') filte R ("object_name" = ' dbms_output ') Statistics----------------------------------------------------------2 recursive C Alls 0 db block gets consistent physical reads 0 redo size 684 by TES sent via sql*net to client 519 bytes received via sql*net from client 2 sql*net roundtrips to/from C Lient 0 Sorts (memory) 0 sorts (disk) 3 rows processed--3. INDEX FAST Full scansql> SELECT owner, object_name from test_objects;84311 rows selected. Execution Plan----------------------------------------------------------plan hash value:2324984732--------------- ----------------------------------------------------------------------------| Id |            Operation| Name | Rows | Bytes | Cost (%CPU) |   Time |-------------------------------------------------------------------------------------------| 0 |                    SELECT STATEMENT | |  84311 |   387k| 138 (0) |   00:00:02 | |  1 | INDEX FAST Full scan| Idx_test_objects_1 |  84311 |   387k| 138 (0) | 00:00:02 |-------------------------------------------------------------------------------------------        Statistics----------------------------------------------------------1 Recursive calls 0 db block gets       6097 consistent gets 480 physical reads 0 Redo size 3509341 bytes sent via sql*net to client 62339 bytes received via sql*net from client 5622 sql*net roundtrips to/from client 0 sorts (Memor Y) 0 Sorts (disk) 84311 rows processed--4. INDEX Full scansql> SELECT owner, object_name from Test_objects order by 1, 2;84311 rows selected. Execution Plan----------------------------------------------------------Plan Hash value:2751381935---------------------------------------------------------------- -----------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |---------------------------------------------------------------------------------------| 0 |                    SELECT STATEMENT | |  84311 |   387k| 505 (1) |   00:00:07 | |  1 | INDEX Full SCAN | Idx_test_objects_1 |  84311 |   387k| 505 (1) | 00:00:07 |---------------------------------------------------------------------------------------        Statistics----------------------------------------------------------1 Recursive calls 0 db block gets       6090 consistent gets 0 physical reads 0 Redo size 3509341 bytes sent via sql*net to client 62339 bytes received via sql*net from client 5622 sql*net roundtrips to/from client 0 sorts (Memor Y) 0 Sorts (disk) 84311 rows processed--5. INDEXFull SCAN (Min/max) sql> SELECT MAX (object_id) from test_objects; Execution Plan----------------------------------------------------------plan hash value:729623451---------------- ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |-------------------------------------------------------------------------------------------------| 0 |                    SELECT STATEMENT |     |     1 |     4 | 2 (0) |   00:00:01 | |  1 |                    SORT AGGREGATE |     |     1 |            4 |          |   ||   2 | INDEX full SCAN (Min/max) |     idx_test_objects_2 |     1 |     4 | 2 (0) | 00:00:01 |-------------------------------------------------------------------------------------------------           Statistics----------------------------------------------------------2 Recursive calls 0 db block gets      5 Consistent gets    2 physical reads 0 Redo size 534 Bytes sent via sql*net to client 519 bytes received via SQ          L*net from client 2 sql*net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

SQL Tuning Basic Overview