影响查询优化器的有很多参数:
CURSOR_SHARING: 默认使用绑定变量
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_MODE
OPTIMIZER_INDEX_CACHING:索引块在缓存中的概率
OPTIMIZER_INDEX_COST_ADJ:这个数反映执行多块IO(全表扫描)的成本与执行单个IO(索引读取)的成本。保持为100,则多块IO与单块IO成本相同。设为50优化程序认为访问单块IO的成本为多块IO的一半。
PGA_AGGREGATE_TARGET: 分配给sorts, hash join的内存
STAR_TRANSFORMATION_ENABLED
查询优化器要:
1. Transform. queries:
View merging: 将视图的查询语句merge到主查询中,和主查询一起优化。
Predicate pushing:对于不merge的view,将主查询中的限定词拿到view中
Subquery unnesting:通过把子查询挪到外层,进行join
Query rewrite with materialized views: 使用物化视图
2. Estimating:
Selectivity: 限定词对行集的过滤情况。0:没有行符合;1:所有行都符合。直方图可以很好的提供这个信息。
Cardinality: 一个行集的行数目。
Cost: 对disk i/o, cpu, 内存的估计。表扫描或者快速索引扫描时,单个i/o会从磁盘读取多个块。因此扫描的代价取决于扫描多少个block以及multi block read count。
一个索引扫描取决于b树高度,扫描索引叶节点的块,和用索引键中的rowid取得的行数。根据rowid取得行数据,依赖于索引聚簇因子。
3. Generating Plans
比较多个可能的执行计划的代价,选择最少代价的。
访问路径;
1. Full table scans:高水位线下的数据都被扫描。使用multiblock read,很高效。
在没有索引;或者selectivity很高;或者表很小,比如表块< multiblock read count,一个i/o read就可以读完;或高并行性。
2. Row id scans:通过where, index scan等得到row id,然后访问。
3. Index scan:通过索引扫描数据。如果数据列已经在索引中,则直接取得数据;否则通过rowid访问表数据。
3.1 assessing i/o for blocks, not rows
对i/o的估算是针对block,而不是行。聚簇因子是和具有相当索引值列的行是否在一个数据块中相关的。高聚簇因子表示行更随机的散落在块中。
3.2 index unique scans
Unique or primary key ->返回最多一行
3.3 index range scans
返回结果按index升序。
何时使用?在一个索引的第一个或前几个列被用于以下情况:=,以及这些条件的组合
3.4 index range scans descending
按index降序返回数据
3.5 index skip scans
查询条件中没有指定索引的第一列,适用于第一列distinct value较少,其他列的distinct value较多的情况。
3.6 full scan
查询列都在索引列中,直接访问索引即可。并且索引列中至少有一列not null 。单块读。
3.7 fast full index scans
多块读。所以比full scan快,但不排序。
3.8 index joins
查询列涉及到多个索引键,所以对多个索引进行hash join。这时候不需要访问表,因为所有列值都在索引键中了。一个index join不会默认进行排序。
3.9 bitmap indexes
假设数据库表中有一列其选择性非常窄,例如性别列,该用什么类型的索引?你可能会考虑对其使用位图索引。因为位图索引正是为相异值很少的列而创建的。但需要考虑的因素还不只这些。一般而言,只有当你对表中值相宜度较小的多个不同的列都使用位图索引,这样位图索引才有用,因为你可以一起使用这些索引才能对列产生更大的选择性,否则你还是需要对这些列进行一次全表扫描。例如,对于性别列,其索引只能有两个唯一值,那么用这个索引对表的任何搜索有可能都返回一半的记录。其次,这些索引是为数据仓库而设计的,所以其假定条件是数据不会发生很大的改变。这些索引不能用来满足事务数据库或更新频繁的数据库。应该说,对位图索引的表进行更新根本没有一点效率。
位图连接索引比位图索引更进了一步。这些索引将位图化的列完全从表数据中抽取出来,并将其存储在索引中。其假定条件是这些列集合必须一起查询。同样的,这也是为数据仓库数据库而设计的。除了在句法最后有一个WHERE子句之外,位图连接索引的创建指令就像创建位图索引的CREATE BITMAP INDEX一样。
4. Cluster access
(索引聚簇表)先扫描聚簇的索引获得row id,然后用row id访问表数据。
5. Hash access
(哈希聚簇表)根据语句中的cluster key value应用哈希函数,得到哈希值,然后去访问拥有这个哈希值得数据块。
6. Sample table scan
随机的选择一些数据。
SELECT * FROM employees SAMPLE BLOCK (1); --随机访问数据块,获得1%的数据。
优化器怎样选择访问路径?
可用的访问路径;预测的执行代价。会被hints影响。
连接
优化器需要确定:访问路径,连接的方式;连接的次序。
连接方式的执行开销:
Nested loop: 基于读取外围表的每一行,和与其匹配的内围表到内存的开销。
Sort merge join:基于读取所有行到内存,并排序的开销。
Hash join:基于对内围表建立哈希表,然后用外围表去窥测的开销。
还会考虑其它一些因素:
比如sort area小,会导致sort merge join开销变大。
一个比较大的multiblock read count会导致一个nested loop join不比full table scan好太多。
Nested loop joins
适用于小数据子集做连接,并且连接方式可以高效的访问第二个表。即outer table可以比较好的筛选inner table。因此,如果两个表之间没有依赖关系,最好不要适用nested loop join。
Driving table -> outer table
对于outer table中的每一行,访问inner table的所有行。
NESTED LOOPS
Outer_loop
Inner_loop
Hash joins
用于连接大表。用稍小的那个表在内存中建立一个哈希表,然后扫描稍大的标,探测哈希表来找到连接的行。
只能用于=
Sort Merge Join
连接两个独立的表。通常情况下,hash join比sort merge join效率好,除非:
连接的表已排序;并且
不需要再排序
适用于连接条件是, >=
步骤是:两个表根据join key排序;排序的表被merge。
Cartesian Joins
笛卡儿积
Outer Joins
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11903161/viewspace-683575/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11903161/viewspace-683575/