记得一个Oracle数据库实验——索引常用执行计划概述

今天主要介绍Oracle索引的常用执行计划：INDEXFULLSCAN：索引的全量扫描，单块读取，有序INDEXRANGESCAN：范围索引扫描INDEXFASTFULLSCAN：索引快速全扫描，多块读取，无序INDEXFULLSCAN(MIN/MAX)：查询MAX(),MIN()函数INDEXSKIPSCAN：查询条件不不使用复合索引的第一列，重复度高时可能会使用复合索引的第一列。这里使用一个简单的测试用例来体验索引使用这些执行计划的场景。1.准备测试环境创建测试表和索引：droptabletest_objects;createtabletest_objectsasselect*fromall_objects;createindexidx_test_objects_1ontest_objects(owner,object_name,subobject_name);createindexidx_test_objects_2ontest_objects(object_id);desctest_objects;查看测试表上的索引信息：selectindex_name,column_name,column_positionfromuser_ind_columnswheretable_name='TEST_OBJECTS';分析表并清除测试环境的shared_pool和buffer_cache：analyzetabletest_objectscomputestatistics;altersystemflushshared_pool;altersystemflushbuffer_cache;2.准备SQL语句根据不同的执行计划场景，编写SQL语句：--INDEXRANGESCAN（索引范围扫描）SELECTowner,object_nameFROMtest_objectsWHEREowner='SYS'ANDobject_name='DBMS_OUTPUT';--INDEXSKIPSCAN（查询MAX(),MIN()函数）SELECTowner,object_nameFROMtest_objectsWHEREobject_name='DBMS_OUTPUT';--INDEXFASTFULLSCAN（索引快速全扫描，多块读取，无序）SELECTowner,object_nameFROMtest_objects;--INDEXFULLSCAN（索引全扫描，单块读取，有序）SELECTowner,object_nameFROMtest_objectsorderby1,2;--INDEXFULLSCAN(MIN/MAX)（查询MAX(),MIN()函数）SELECTmax(object_id)FROMtest_objects;3.实验证明结果如下：(1)INDEXRANGESCANsetautotracetraceonlySELECTowner,object_nameFROMtest_objectsWHEREowner='SYS'ANDobject_name='DBMS_OUTPUT';(2)INDEXSKIPSCANSELECTowner,object_nameFROMtest_objectsWHEREobject_name='DBMS_OUTPUT';(3)INDEXFROMtest_objects;FULLSCAN_objects;FULLSCANSELECTowner,object_nameFROMtest_objectsorderby1,2;(5)INDEXFULLSCAN(MIN/MAX)SELECTmax(object_id)FROMtest_objects;