Oracle 学习之性能优化（六）访问路径

2025-04-16 技术教程

访问路径是指Oracle找到用户需要的数据的方法，这些方法很少，包括：

声名狼藉的全表扫描--人们不惜一切视图避免的（曲解的）访问路径。

各种类型的索引扫描--这是人们感觉良好的访问路径（多数情况下是被曲解的）。

通过hash或者rowid的方式直接访问，通常对于单数据行来说，是最快的。

并没有一种访问路径是最好的，如果有，那么Oracle只需提供这一种访问路径就好了。

全表扫描

全扫描就是顺序的读取表中的所有数据块。采用多块读的方式，从头开始扫描表中的块，直到高水位线。全扫描是处理大数据量行之有效的方法。需要牢记：全扫描并不邪恶，多数情况下全扫描是获得结果的最快方法。

全扫描每次读取的块数由参数db_file_multiblock_read_count指定

SQL>showparameterdb_file_muNAMETYPEVALUE---------------------------------------------------------------------------------------------------db_file_multiblock_read_countinteger128

2.rowid 访问

rowid是一行数据的物理位置，访问单行数据的速度是最快的。

SQL>select*fromempwhererowid='AAASZHAAEAAAACXAAN';7934MILLERCLERK77821982/01/2300:00:00130010

通过索引的方式访问数据，其实也是通过索引，先找到这行数据的rowid，然后再通过rowid访问数据。

SQL>setautotraceontraceonlySQL>select*fromempwhereempno=7934;ExecutionPlan----------------------------------------------------------Planhashvalue:2949544139--------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|1(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMP|1|38|1(0)|00:00:01||*2|INDEXUNIQUESCAN|PK_EMP|1||0(0)|00:00:01|--------------------------------------------------------------------------------------

rowid还可以进行范围扫描。

SQL>select*fromempwhererowidbetween'AAASZHAAEAAAACXAAA'and'AAASZHAAEAAAACXAAN';7369SMITHCLERK79021980/12/1700:00:00800207499ALLENSALESMAN76981981/02/2000:00:001600300307521WARDSALESMAN76981981/02/2200:00:001250500307566JONESMANAGER78391981/04/0200:00:002975207654MARTINSALESMAN76981981/09/2800:00:0012501400307698BLAKEMANAGER78391981/05/0100:00:002850307782CLARKMANAGER78391981/06/0900:00:002450107788SCOTTANALYST75661987/04/1900:00:003000207839KINGPRESIDENT1981/11/1700:00:005000107844TURNERSALESMAN76981981/09/0800:00:0015000307876ADAMSCLERK77881987/05/2300:00:001100207900JAMESCLERK76981981/12/0300:00:00950307902FORDANALYST75661981/12/0300:00:003000207934MILLERCLERK77821982/01/2300:00:0013001014rowsselected.

3. 索引扫描

索引扫描是最常见的数据访问之一，例如

我们下面主要以b-tree索引为例

索引唯一性扫描

优化器知道索引列的值是唯一的，查询结果只返回一行。这种索引的访问速度最快，找到一行数据就不再继续扫描索引，直接返回。

SQL>select*fromempwhereempno=7934;7934MILLERCLERK77821982/01/2300:00:00130010ExecutionPlan----------------------------------------------------------Planhashvalue:2949544139--------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|1(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMP|1|38|1(0)|00:00:01||*2|INDEXUNIQUESCAN|PK_EMP|1||0(0)|00:00:01|--------------------------------------------------------------------------------------

实际上Oracle中并没有非唯一索引，在非唯一索引中，Oracle将数据的rowid添加到索引键中使其唯一。

索引范围扫描

SQL>setautottraceonlySQL>selectempnofromempwhereempno<5000;norowsselectedExecutionPlan----------------------------------------------------------Planhashvalue:1567865628---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||1|4|1(0)|00:00:01||*1|INDEXRANGESCAN|PK_EMP|1|4|1(0)|00:00:01|---------------------------------------------------------------------------

有取的是，索引可以按照两个方向去扫描索引

SQL>selectempnofromempwhereempno<5000orderbyempno;norowsselectedExecutionPlan----------------------------------------------------------Planhashvalue:1567865628---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||1|4|1(0)|00:00:01||*1|INDEXRANGESCAN|PK_EMP|1|4|1(0)|00:00:01|---------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("EMPNO"<5000)SQL>selectempnofromempwhereempno<5000orderbyempnodesc;norowsselectedExecutionPlan----------------------------------------------------------Planhashvalue:2474278666--------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|4|1(0)|00:00:01||*1|INDEXRANGESCANDESCENDING|PK_EMP|1|4|1(0)|00:00:01|--------------------------------------------------------------------------------------

这个的好处是避免排序操作

如果你建立的是非唯一索引，即便你使用=查询，也是范围扫描

SQL>createindexind_emp_enameonemp(ename);Indexcreated.SQL>select*fromempwhereename='KING';ExecutionPlan----------------------------------------------------------Planhashvalue:2929622481---------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|38|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMP|1|38|2(0)|00:00:01||*2|INDEXRANGESCAN|IND_EMP_ENAME|1||1(0)|00:00:01|---------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("ENAME"='KING')

索引全扫描

索引全扫描，并不是扫描全部的索引。它实际上只需扫描索引的叶子节点。但是为了找到叶子节点的位置，也会扫描部分的分支节点。

我们看如下查询

SQL>selectempno,enamefromemp;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:3956160932--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||14|140|3(0)|00:00:01||1|TABLEACCESSFULL|EMP|14|140|3(0)|00:00:01|--------------------------------------------------------------------------

查询列ename并不在索引中，所以走的是全表扫描。但是如果我们将语句做如下修改。

SQL>selectempno,enamefromemporderbyempno;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:4170700152--------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------|0|SELECTSTATEMENT||14|140|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMP|14|140|2(0)|00:00:01||2|INDEXFULLSCAN|PK_EMP|14||1(0)|00:00:01|--------------------------------------------------------------------------------------

Oracle为了避免排序操作，而使用了索引全扫描。因为索引是有序的数据，并且索引全扫描是按顺序的单块读操作。

max和min

SQL>selectmax(empno)fromemp;ExecutionPlan----------------------------------------------------------Planhashvalue:1707959928-------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|4|1(0)|00:00:01||1|SORTAGGREGATE||1|4||||2|INDEXFULLSCAN(MIN/MAX)|PK_EMP|1|4|1(0)|00:00:01|

该扫描使用了索引全扫描，但其实并非真正的全扫描，max和min限定词使得Oracle知道何时停止，它只是扫描最高块或者最低块。

索引跳跃扫描

通常要使用索引，索引的前置列一定要出现在查询条件中。

SQL>createtablet(aint,bint,cint,dint,eint,fint,gint);SQL>createindext_idxont(a,b,c);

通常情况下只有如下的查询才会使用索引

select*fromtwherea=:a;select*fromtwherea=:aandb=:b;select*fromtwherea=:aandb=:bandc=:c;

但是如下查询不会使用索引（除了使用hint强制索引全扫描）

select*fromtwhereb=:b;select*fromtwherec=:c;select*fromtwhereb=:bandc=:c;

Oracle 9i后实现了跳跃索引扫描，条件如下：

谓词中使用了索引中其他的列。

前置列值的DISTINCT_NUM比较少。

我们看看如下示例

SQL>createtabletas2selectmod(rownum,3)a,rownumb,rownumc,object_named3fromall_objects;Tablecreated.SQL>createindext_idxont(a,b,c);Indexcreated.SQL>analyzetabletcomputestatistics;Tableanalyzed.SQL>select*fromtwhereb=1andc=1;ExecutionPlan----------------------------------------------------------Planhashvalue:2053318169-------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|34|5(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|T|1|34|5(0)|00:00:01||*2|INDEXSKIPSCAN|T_IDX|1||4(0)|00:00:01|-------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("B"=1AND"C"=1)filter("B"=1AND"C"=1)Statistics----------------------------------------------------------1recursivecalls0dbblockgets8consistentgets0physicalreads0redosize724bytessentviaSQL*Nettoclient523bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed

因为a的值比较少，只有3个，Oracle把索引(a,b,c) 看成3个小索引。

索引快速全扫描

索引快速全扫描与索引全扫描明显的不同，它有如下特征

它读取索引中的每个块，包括所有分支块。

它采用多块读，像全表扫描一样。

它不按排序顺序扫描索引。

我们先建立一个表，并插入大量数据。

SQL>createtablebig_tableasselect*fromdba_objects;Tablecreated.SQL>insertintobig_tableselect*frombig_table;74577rowscreated.SQL>insertintobig_tableselect*frombig_table;223731rowscreated.SQL>/447462rowscreated.SQL>commit;Commitcomplete.SQL>altertablebig_tablemodifyobject_idnotnull;Tablealtered.SQL>createindexidx_big_table_objidonbig_table(object_id);Indexcreated.SQL>analyzetablebig_tablecomputestatistics;Tableanalyzed.

执行如下查询

SQL>setautottraceonlySQL>selectobject_idfrombig_table;894924rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:205523069--------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||894K|3495K|544(2)|00:00:07||1|INDEXFASTFULLSCAN|IDX_BIG_TABLE_OBJID|894K|3495K|544(2)|00:00:07|--------------------------------------------------------------------------------------------Statistics----------------------------------------------------------15recursivecalls0dbblockgets61534consistentgets2physicalreads0redosize15755358bytessentviaSQL*Nettoclient656794bytesreceivedviaSQL*Netfromclient59663SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)894924rowsprocessed

查询使用的是索引快速全扫描。

有心的人可以思考一下，如下查询为啥没有使用索引快速全扫描，而使用了索引全扫描。

SQL>selectempnofromemp;14rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:179099197---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||14|56|1(0)|00:00:01||1|INDEXFULLSCAN|PK_EMP|14|56|1(0)|00:00:01|---------------------------------------------------------------------------Statistics----------------------------------------------------------0recursivecalls0dbblockgets2consistentgets0physicalreads0redosize686bytessentviaSQL*Nettoclient523bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)14rowsprocessed

索引连接

索引连接（index join）是在表中存在多个索引时针对某个查询所选中的索引路径。

我们看如下例子

SQL>createtablet1asselect*fromdba_objects;Tablecreated.SQL>createindext1_idx1ont1(object_id);Indexcreated.SQL>createindext1_idx2ont1(owner,object_type);Indexcreated.SQL>analyzetablet1computestatistics;Tableanalyzed.SQL>setautottraceonlySQL>selectobject_id,owner,object_typefromt12whereobject_idbetween100and20003andowner='SYS';1478rowsselected.ExecutionPlan----------------------------------------------------------Planhashvalue:2563395799---------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||69|1173|18(6)|00:00:01||*1|VIEW|index$_join$_001|69|1173|18(6)|00:00:01||*2|HASHJOIN|||||||*3|INDEXRANGESCAN|T1_IDX1|69|1173|7(15)|00:00:01||*4|INDEXRANGESCAN|T1_IDX2|69|1173|12(9)|00:00:01|---------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("OBJECT_ID"<=2000AND"OWNER"='SYS'AND"OBJECT_ID">=100)2-access(ROWID=ROWID)3-access("OBJECT_ID">=100AND"OBJECT_ID"<=2000)4-access("OWNER"='SYS')Statistics----------------------------------------------------------1recursivecalls0dbblockgets215consistentgets0physicalreads0redosize32014bytessentviaSQL*Nettoclient1601bytesreceivedviaSQL*Netfromclient100SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1478rowsprocessed

优化器通过扫描T1_IDX1，T1_IDX2得到结果集，用两个结果集的rowid进行join运算，得到返回集。

这样避免扫描表。