SQL优化涉及到的基本概念（一）(转)

SQL优化涉及到的基本概念（一）

参考：
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#721

Overview of SQL Processing
1. parser 进行语意和语法分析
2. Optimizer 利用RBO,CBO等方法决定产生查询结果的最有效路径
3. Row Source Generator 从2中接受优化后的方案，并产生SQL的Execution Plan
4. SQL Execution Engine运行该执行计划，并产生查询结果

Features that Require the CBO
1. Partitioned tables and indexes
2. Index-organized tables
3. Reverse key indexes
4. Function-based indexes
5. SAMPLE clauses in a SELECT statement
6. Parallel query and parallel DML
7. Star transformations and star joins
8. Extensible optimizer
9. Query rewrite with materialized views
10. Enterprise Manager progress meter
11. Hash joins
12. Bitmap indexes and bitmap join indexes
13. Index skip scans
即使OPTIMIZER_MODE＝true，这些features仍然会使用CBO

Components of the CBO

Query Transformer
数据来自于parse后的SQL,是一系列的query block。其目标是测定SQL的形式是否有利于产生好的query plan。有以下4种：
1. View Merging
SQL 中的view 被扩展到单独的query block中。Optimizer会单独分析view query block，这样通常会导致在整体优化上得不到最优的结果。因此query transformer会将大部分view和其他的query block 合并，这样可以在整体上统一优化。
2. Predicate Pushing
针对没有被merge的view, push the relevant predicates from the containing query block into the view query block, which can be used either to access indexes or to act as filters
3. Subquery Unnesting
子查询是nested在主查询中的，这样很难得到好的优化结果。所以将他们unnested，变成join
4. Query Rewrite with Materialized Views
如果查询与某个物化视图符合的化，则会按照物化视图重写这个查询，因为物化视图的结果都是事先计算好的。

Estimator
会产生３中度量标准：
1. Selectivity
表示有多少rows可以通过谓词被选择出来，大小介于0.0~1.0，0表示没有row被选择出来。
如果没有statistics，estimator会使用一个默认的selectivity 值，这个值根据谓词的不同而异。比如’=’的selectivity小于’如果有statistics，比如对于last_name = 'Smith'，estimator使用last_name列的distinct 值的倒数，作为selectivity。
如果last_name列上有histogram，则使用住状图中'Smith'值的分布情况作为selectivity
2. Cardinality
表示一个row set的行数。
Base cardinality：base table的行数。如果analyze了，则直接使用分析数据。如果没有，则使用占用的extents的多少来估计。
Effective cardinality：有效行集，指从基表中选择出来的行数。是Base cardinality和表上组合谓词的Selectivity的乘积。
Join cardinality：两表join后产生的行数。是两表cardinality的承积(Cartesian)，再承以Join谓词的selectivity
Distinct cardinality：顾名思义，不同值数据的行数
Group cardinality：表示能分几组
group cardinality lies between max ( dist. card. colx , dist. card. coly )
                               and min ( (dist. card. colx * dist. card. coly) ,
                               num rows in row set )
3. Cost
Disk I/O, CPU, Memory used的综合考虑。
Clustering Factor:
index的一种属性，表示被索引的行在数据块中的分布情况，表征表中数据的存储顺序和某索引字段顺序的符合程度。直接影响使用rowid 找到row的cost。大小介于block数和rownum之间。
（以下来自biti_rainy http://blog.itpub.net/post/330/2970 ）
oracle 按照索引块中存储的rowid 来识别相临的索引中记录 在 表block中是否为相同块,如果索引中存在记录 rowid a,b,c,d……,若b 和 a 是同一个block，则比较 c 和 b，若这时不是同一个block，则clustering_factor + 1 ,然后比较 d 和 c，若还不是同一个 block，则clustering_factor + 1……
若clustering_factor值越小越接近block数，越好，则说明表中数据具有比较好的跟索引字段一样排序顺序的存储，通过索引进行 range scan 的代价比较小，需要读取的表块可能比较少。
若clustering_factor 接近 row数量，则说明表中数据和索引字段排序顺序差异很大，杂乱无张。则通过索引进行 range scan 的代价比较大，需要读取的表块可能更多。
在oracle 920 开始，对于clustering_factor 比较接近表块数量的根据索引的大范围查询做了特别的处理，不再是读一个索引记录去搜索一个表记录了，而是成批处理(通过索引块一批rowid一次去表块中获得一批记录)，这样就大大节约了读的成本( consistent gets)。
Join Cost:
Nested loop join cost = outer access cost + (inner access cost * outer cardinality)
(注：因为outer table相当于外层循环，对于每个row都要遍历一遍内表的所有row)
Sort merge join cost = outer access cost + inner access cost + sort costs (outer and inner, if sort is used)
Hash join cost = (outer access cost * # of hash partitions) + inner access cost
首先理解hash table 的数据结构：
可以把hash table 看做一个2维数组a[200][100]，现有1000个无序数字用来被查询。我们考虑把这1000个数字除以200，根据其余数放在a[200][100]中，余数就是数组的第一维下标。这样平均一个a[i]只放5个数字。当查询的时候，对数字除以200（这就是一个简单的hash 算法），根据余数i去a[i]中查找，大约遍历5次就能找到。
Inner row（小表）被hash在内存中，并且通过join key建立hash table（作为第一个下标），然后scan outer table，到hash table中查找joined rows（通过hash 算法）。
hash table会按照multiblock_IO 决定分成几个partitions。如果hash table 太大超出了hash_area_size，则将超出部分的partitions放到temporary segments中。
可以通过10104 events 查看hash join 的statistics
ALTER SESSION SET EVENTS '10104 trace name context forever, level 10'; 比如：
Total number of partitions:
Number of partitions which could fit in memory:
如果后者大于前者，则说明一些partitions 因为超出了hash_area，要被放置到临时表空间中。

Plan Generator
作用是尽可能产生不同的执行计划，选择cost最低的一种。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/50285/viewspace-785814/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/50285/viewspace-785814/