oracle access path,Oracle学习系列—数据库优化—Access Path和join学习

呵呵,边学习边翻译,权当笔记了...

CBO的访问路径(Access

Path)

访问路径是从数据库中查询数据的方式.主要包括

Ø全表扫描

ØROWID扫描

Ø索引扫描

Ø聚簇扫描

ØHASH扫描

Ø样例表扫描

全表扫描

全表扫描时,读取该表所有的记录,然后过滤掉不满足要求的数据.仅扫描和读取高水平线一下的块.

全表扫描时,所有块被顺序读取.因为块一半都是毗邻的,为了加速处理,通常I/O调用比单块要大些.每次块读取的数量是由DB_FILE_MULTIBLOCK_READ_COUNT参数决定的

为什么选择全表扫描

全表扫描的成本要比索引方式访问离散的块要小,这是因为全表扫描使用较大的I/O调用,而少数的大I/O调用要比多次调用成本更低

什么时候选择全表扫描

Ø缺乏索引如采用了函数,却没有创建函数索引

Ø大批量数据优化器认为查询需要访问表中的多数快

Ø小表表的(HWM下)总块数小于DB_FILE_MULTBLOCK_READ_COUNT块

Ø高度并行

Ø强制(HINT)全表扫描 /*+ FULL(Table_Name) */

ROWID扫描

行中的ROWID确定了包含该记录的数据文件和数据块,通过ROWID方式定位一条记录是访问单记录最快的方式,因为它直接给出了数据库中该记录最准确的位置.

索引扫描

包括以下几种类型:

Ø唯一索引扫描(Index

Unique Scans)

通常是Unique Index 或者主键,最多返回单行记录

Hint强制 INDEX(alias index_name)

Ø范围索引扫描(Index

Range Scans)

范围索引扫描是访问数据最常用的方法,可以有或者没有边界值.数据将以索引字段的升序返回,相通值以Rowid升序返回.

Col1 = :b1

Col1 < :b1

Col1 > :b1

Col1 like ‘XX%’

Hint强制 INDEX(alias index_name)

Ø范围索引降序扫描(Index

Range Scans Descending)

范围索引降序扫描和范围索引扫描是一样的,除了数据以降序形式返回.

Hint强制 INDEX_DESC(alias index_name)

Ø跳转索引扫描(Index

Skip Scans)

跳转索引扫描能够改进非前导字段的索引扫描,访问索引块通常要好于访问数据快.例如组合索引中的前导列可能没有用于查询.通常适用于前导列选择性比较高,后续列选择性较低的查询

例如

Employee(sex,employee_id,address)或者Employee(sex,employee_id)

Ø全扫描(Full Scans)

全扫描适用于:所有待查询列均包含在索引中,至少一个列非空.可以消除排序

Ø快速全索引扫描(Fast Full Index Scans)

和全扫描相互替代,适用于待查询字段,快速全索引扫描扫描所有的,并且不能消除排序

同时需要指定OPTIMIZER_FEATURES_ENABLE参数

Hint强制INDEX_FFS(alias index_name)

Ø索引连接(Index

Joins)

索引连接是几个包含查询时所有字段索引的Hash连接

Hint强制 INDEX_JOIN(alias index_name)

Ø位图连接(Bitmap

Joins)

位图连接使用位图,并把位图位置转换成Rowid,位图能够有效合并索引,满足AND和OR条件的Boolean操作.

连接(Joins)

优化器评估每种连接的成本,然后选择最低成本的方式.假如一个连接返回多行,优化器将会考虑以下三种情况.

嵌套循环连接当一个连接返回大批记录时(例如,超过10000行),嵌套循环是没有效率的,优化器可能不会使用.嵌套循环的成本根据以下公式进行计算:

cost= access cost of A

+ (access cost of B * number of rows from A)

HASH连接使用CBO时,当连接返回大批记录时,HASH连接是最有效的.HASH连接的成本通过以下公式计算:

cost= (access cost of

A * number of hash partitions of B) + access cost of B

排序合并连接使用CBO时,当一个连接返回大批数据时,Merge连接也是最有效的.Merge连接的成本通过以下公式计算:

cost= access cost of A

+ access cost of B +(sort cost of A + sort cost of B)

如果数据已经预排序,排序成本则为0

SORT_AREA_SIZE(适用于shared server)和PGA_AGGREGATE_TARGET(被推荐使用)

反连接(Anti-join)返回左侧断言的行集,一般而言就是返回那些没有匹配在右侧的行集.可以用MERGE_AJ,HASH_AJ,NL_AJ强制hint

半连接(Semi-join)返回那些没有重复行的Exist子查询结果

可以使用MERGE_SJ,HASH_SJ,NL_SJ强制hint

星型查询

数据仓库被设计成星型模式,包含一个大的事实表和几个小的维度表.星型查询是事实表和查找表(Lookup表)的连接,CBO能够识别星型查询,并且产生有效的执行计划.

USE_HASH,USE_NL,USE_MERGE

USE_HASH使用HASH_AREA_SIZE和HASH_JOIN_ENABLED参数(shared server)或者PGA_AGGREGATE_TARGET(推荐)

USER_MERGE使用HASH_AREA_SIZE和SORT_AREA_SIZE参数(shared server)或者PGA_AGGREGATE_TARGET(推荐)

嵌套循环连接适用于一个大表和一个小表(volume<20000)的连接,通常以有条件限制的表为驱动表.

Hash连接适用于一个大表和一个较小表(volume>20000)的连接.通常以较小表作为驱动表,一般条件为等式连接.

排序合并连接适用于两个相对独立的行源,条件为行源已排序或者无需排序.或者适用于非等式连接.

影响CBO的参数

ØCURSOR_SHARING

影响绑定变量的执行计划

ØDB_FILE_MULTIBLOCK_READ_COUNT

影响全表扫描和索引的判断

ØHASH_AREA_SIZE

影响HASH连接的操作

ØHASH_JOIN_ENABLED

启用和失效HASH连接操作

ØOPTIMIZER_INDEX_CACHING

控制嵌套循环时索引探测成本

ØOPTIMIZER_INDEX_COST_ADJ

调整索引访问的成本

ØOPTIMIZER_MAX_PERMUTATIONS

ØOPTIMIZER_MODE

控制优化器的缺省优化方式

ØPARTITION_VIEW_ENABLED

分区视图探测

ØQUERY_REWRITE_ENABLED

物化视图的查询重写功能

ØSORT_AREA_SIZE

影响排序性能

ØSTAR_TRANSFORMATION_ENABLED

增强星型转换方式