作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

 

 

1. 问题和需求

“你们Oracle公司有这样的自动扫描SQL语句工具吗？通过这个工具，把我们的应用软件输进去，就能扫出SQL语句的大部分问题。这样就可以减少我们测试和性能优化工作量，更能避免投产之后才暴露性能问题。” — 来自某移动客户的需求。

“老罗，XX移动公司希望我们Oracle公司提供自动扫描SQL工具，我们有吗？听说第三方公司有这样的产品，已经在客户那儿试用了。” — 来自Oracle服务销售同事的担忧。

是啊，客户的需求再合理不过。但据我所知，Oracle公司好像没有这样包治百病的神奇工具。第三方公司居然有这样的工具，太吸引客户眼球了，一方面让人感到质疑，另一方面也令人感到一种竞争压力。

 

2. 初识庐山真面目

于是，我和销售同事趁去该客户现场拜访、调研的机会，对该客户的上述需求和第三方公司的自动化工具一探究竟了。客户的需求不必多言了，我们关键是对所谓自动化工具充满好奇。因商务因素，客户并没有给我们直接展示该工具的使用过程和界面，但告诉我们大致原理：原来该工具首先通过定义一组评分规则，例如：SQL语句是否使用绑定变量；条件字段前是否有函数；多表连接是否超过4个表… …，然后将输入的SQL语句进行评判，若违反这些规则，扣分！最后给该SQL语句和整个应用模块打分。

原来如此！这些规则在大部分情况下不无道理，例如，条件字段加函数，特别是在日期字段前加to_char函数：

to_char(DJ_SZ.JDRQ, ‘YYYY.MM.DD’) BETWEEN ‘2014.04.01’ AND ‘2014.04.17’

就是一种非常初级、业余、错误的编程方式。正确方式应该是：

DJ_SZ.JDRQ BETWEEN to_date(‘2014.04.01’,’YYYY.MM.DD’) AND to_date(‘2014.04.17’,’YYYY.MM.DD’)

但是，更多的规则值得商榷。例如，在Oracle公司推荐的编程规范中，并不是所有SQL语句都应该使用绑定变量的，而只是针对并发量大的小事务SQL语句才应该使用绑定变量，而针对并发量小的大事务SQL语句，特别是非常复杂SQL语句，Oracle公司建议是不要使用绑定变量。第三方的自动工具能分析出SQL语句是高并发量还是低并发量访问，以及大事务和小事务吗？值得怀疑。

更为典型的例子是，其实Oracle公司从来没有官方正式建议：一个SQL语句不能超过4个表的连接。的确，多表连接可能导致性能不佳，但问题不在于连接表的多和少，而在于编程人员是否理解了Oracle的Nested Loop、Hash Join等多种表连接技术原理和适应场景，以及在表连接中索引的设计原理。以下就是一个国内著名财务软件的典型SQL语句：

select *

from (select rownum num, temp.*

from (select a.fid,

… …

a.playdeptname as playdeptNameCode

from t_claim_remittancerecord a

left join t_pay_remittype b on a.remittype = b.fid

left join t_pay_fundtype c on c.fid = a.amountscategory

left join t_org_department d on a.remitdepart =

d.finasyscode

left join t_org_department y on a.playdeptname =

y.finasyscode

and y.status = 1

left join t_org_employee f on f.empcode = a.addperson

left join t_org_department k on f.deptid = k.id

left join t_org_employee g on g.empcode = a.updateperson

left join t_org_employee h on h.empcode = a.claimman

left join t_bd_customer cus on cus.fnumber = a.customer

left join V_LMS_SUPPLIER s on s.snumber = a.supplier

left join t_deposit_printer i on i.codenum = a.codenum

left join t_org_employee j on i.createuser = j.empcode

WHERE 1 = 1

and a.accountName like ‘%’ || :1 || ‘%’

and a.claimState like ‘%’ || :2 || ‘%’

and a.writeOffState like ‘%’ || :3 || ‘%’

and a.reachAmountDate between :4 and :5

and a.repealstate != 1

order by addTime desc, codeNum) temp) t

WHERE t.num <= :6

and t.num > :7

 

哇！该语句好复杂哦，连接的表多达10多个。若采用第三方公司的SQL自动扫描工具。该语句一定被扣分甚至彻底枪毙了。可是，该语句实际运行情况如何呢？以下就是该语句的执行计划：

———————————————————————————————| Id  | Operation                                     | Name                      | Cost (%CPU)|

———————————————————————————————|   0 | SELECT STATEMENT                              |                           |    14 (100)|

|   1 |  FILTER                                       |                           |            |

|   2 |   VIEW                                        |                           |    14   (8)|

|   3 |    COUNT                                      |                           |            |

|   4 |     VIEW                                      |                           |    14   (8)|

|   5 |      SORT ORDER BY                            |                           |    14   (8)|

|   6 |       FILTER                                  |                           |            |

|   7 |        NESTED LOOPS OUTER                     |                           |    13   (0)|

|   8 |         NESTED LOOPS OUTER                    |                           |    12   (0)|

|   9 |          NESTED LOOPS OUTER                   |                           |    11   (0)|

|  10 |           NESTED LOOPS OUTER                  |                           |    10   (0)|

|  11 |            NESTED LOOPS OUTER                 |                           |     9   (0)|

|  12 |             NESTED LOOPS OUTER                |                           |     8   (0)|

|  13 |              NESTED LOOPS OUTER               |                           |     7   (0)|

|  14 |               NESTED LOOPS OUTER              |                           |     6   (0)|

|  15 |                NESTED LOOPS OUTER             |                           |     5   (0)|

|  16 |                 NESTED LOOPS OUTER            |                           |     4   (0)|

|  17 |                  NESTED LOOPS OUTER           |                           |     3   (0)|

|  18 |                   NESTED LOOPS OUTER          |                           |     2   (0)|

|  19 |                    TABLE ACCESS BY INDEX ROWID| T_CLAIM_REMITTANCERECORD  |     1   (0)|

|  20 |                     INDEX RANGE SCAN          | IDX_TCR                   |     1   (0)|

|  21 |                    TABLE ACCESS BY INDEX ROWID| T_PAY_REMITTYPE           |     1   (0)|

|  22 |                     INDEX UNIQUE SCAN         | PK_REMITTYPE_FID          |     1   (0)|

|  23 |                   TABLE ACCESS BY INDEX ROWID | T_PAY_FUNDTYPE            |     1   (0)|

|  24 |                    INDEX UNIQUE SCAN          | PK_FUNDTYPE_FID           |     1   (0)|

|  25 |                  TABLE ACCESS BY INDEX ROWID  | T_DEPOSIT_PRINTER         |     1   (0)|

|  26 |                   INDEX UNIQUE SCAN           | PK_T_DEPOSIT_PRINTER      |     1   (0)|

|  27 |                 TABLE ACCESS BY INDEX ROWID   | T_BD_SUPPLIER             |     1   (0)|

|  28 |                  INDEX RANGE SCAN             | IDX_BD_SUPPLIER_NUM       |     1   (0)|

|  29 |                TABLE ACCESS BY INDEX ROWID    | T_ORG_DEPARTMENT          |     1   (0)|

|  30 |                 INDEX RANGE SCAN              | IDX_T_ORG_DPT_FINASYSCODE |     1   (0)|

|  31 |               TABLE ACCESS BY INDEX ROWID     | T_ORG_DEPARTMENT          |     1   (0)|

|  32 |                INDEX RANGE SCAN               | IDX_T_ORG_DPT_FINASYSCODE |     1   (0)|

|  33 |              TABLE ACCESS BY INDEX ROWID      | T_BD_CUSTOMER             |     1   (0)|

|  34 |               INDEX RANGE SCAN                | IDX_BD_CUSTOMER_NUM       |     1   (0)|

|  35 |             TABLE ACCESS BY INDEX ROWID       | T_ORG_EMPLOYEE            |     1   (0)|

|  36 |              INDEX UNIQUE SCAN                | UK_EMPLOYEE_EMPCODE       |     1   (0)|

|  37 |            TABLE ACCESS BY INDEX ROWID        | T_ORG_DEPARTMENT          |     1   (0)|

|  38 |             INDEX UNIQUE SCAN                 | SYS_C00797036             |     1   (0)|

|  39 |           TABLE ACCESS BY INDEX ROWID         | T_ORG_EMPLOYEE            |     1   (0)|

|  40 |            INDEX UNIQUE SCAN                  | UK_EMPLOYEE_EMPCODE       |     1   (0)|

|  41 |          TABLE ACCESS BY INDEX ROWID          | T_ORG_EMPLOYEE            |     1   (0)|

|  42 |           INDEX UNIQUE SCAN                   | UK_EMPLOYEE_EMPCODE       |     1   (0)|

|  43 |         TABLE ACCESS BY INDEX ROWID           | T_ORG_EMPLOYEE            |     1   (0)|

|  44 |          INDEX UNIQUE SCAN                    | UK_EMPLOYEE_EMPCODE       |     1   (0)|

 

 

大家看到上述执行计划，首先不应感到畏惧，而应该从外观上感慨一下，那就是数据库的美感！大家看这个执行计划的形状多么对称和富有韵律感，也多像一把打开的美丽扇子。其次，大家一定要相信，外观充满美感的东西，本质上也应该不错，呵呵。的确，回到技术本质，我们发现虽然该语句涉及10多张表的连接，但实际运行效率效果非常高，例如Cost才14，当然Cost有不准确的时候。更重要的是，该语句每次表连接都非常漂亮地采用了Nested Loop连接技术，并且都合理地采用了被连接字段的索引。正是因为设计开发人员非常了解Oracle表连接原理以及索引设计规范，所以才设计出了这样“又好吃、又好看”的SQL语句。

可是，第三方公司的SQL自动扫描工具却很可能滥杀无辜了。大家一定能相信一个原理：世界上一件事物的好坏不在于多和少，而在于其本身的对和错。若将此原理运用在多表连接技术方面，那就是：多表连接的好坏不在于连接表的多和少，而在于每次表连接的对和错。因为Oracle表连接每次都是两个表进行连接，然后再进行第三个、第四个表的连接。若充分理解了Oracle各种表连接技术、索引设计规范等，每次表连接都是高效的，再多的表连接也是合理的。反过来，若不了解Oracle表连接技术和适应场景，即便是两个表的连接都会出问题。

 

3. 少一点噱头，多一点务实

此标题有点刺耳，甚至刻薄，但的确是本人有感而发。国内IT市场也的确存在这种不太正常现象：面对客户某些看似合理，实则很难实现的需求，某些公司不是去合理引导客户，反而是一味迎合客户，甚至是推波助澜，更实质的目的还是出于商业考虑。但是，大家不知这是一种非常短视的行为吗？难道客户不会很快就验证出这种所谓SQL自动扫描工具的有效性，甚至真伪性吗？既然如此，大家何必去费尽心机，去讨客户这种“好”？实际上很可能是既让客户失望，也毁自己声誉的事情。

性能分析和优化，特别是SQL语句性能分析和优化，怎么可能只做静态的形式分析？而不做与实际系统和数据相关联的动态神式分析？记得有一年参加一个数据库技术大会，一位国外性能优化大师的演讲曾经让我非常震撼，他的演讲主题是性能优化与应用数据的关联性，整个演讲中，他未展现一个SQL语句优化技术，而是大谈数据分布对SQL语句访问性能的重要性，诸如按字段分析最大值、最小值、分组统计等，以及何时需要按Bucket方式收集统计信息等。所谓的SQL语句自动扫描工具，可能连客户实际系统都不连接，执行计划也不分析，客户数据更不了解，就能扫描出SQL语句质量？的确有点像个乌托邦的东西。

再回到本文开头一个问题：“你们Oracle公司有这样的自动扫描SQL语句工具吗？”准确地回答是：Oracle的确没有这种不看数据、不看执行计划的所谓自动扫描SQL语句工具，但Oracle公司自10g开始就提供了大量内置的SQL优化工具，例如：ADDM、SQL Access Advisor、SQL Tuning Advisor、Automatic SQL Tuning、SQL Profile、SPA（SQL Performance Analyzer）、SPM（SQL Plan Management）… …这些工具一个共同特点是不仅分析SQL语句执行计划，而且分析统计信息，分析数据分布情况，分析索引设计情况等，综合各方面情况，给出一些更合理的SQL语句优化建议。例如，11g的Automatic SQL Tuning就是分析SQL语句所访问表的统计信息是否过期、是否缺乏索引、是否可产生有效的SQL Profile信息、语句编写是否合理等。

再者，虽然Oracle自动优化工具能有效分析和解决很多SQL性能问题，但更多基础性，特别是与应用数据紧密相关的问题，还是需要应用设计开发人员从数据库模型规范化设计、 基础技术掌握、SQL设计开发规范、应用软件质量控制、加强设计开发管理等层面和角度去加以解决。

总之，性能优化工作，特别是应用性能分析和优化工作，还是需要大家踏踏实实、一点一滴地做起，即便需要所谓自动化的工具，也建议大家优先考虑Oracle公司本身自带的工具，毕竟这些工具是Oracle产品的一部分，经过了严格测试，也为全球广大客户的大量实践所验证，是具有普遍适用性的东西。

还是以本节标题作为本文结尾：

少一点噱头，多一点务实！

作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

 

 

 

1. 多年前的一段往事

记得多年以前在一个10g平台的数据仓库项目上遇到一个非常难优化的SQL语句，当时即便我采集了统计信息、甚至在语句中增加了HINT，Oracle产生的执行计划都不如人意。最后，不得不通过SR寻求老外高手的指点，他建议我采用10g刚出炉的一个新技术，即让我为该语句生成SQL Profile信息，然后再执行该语句。一切OK了，太神了！

也记得当时我问老外，以后是不是遇到非常复杂的、优化难度很大的SQL语句，就扔给Oracle，特别是产生一遍SQL Profile来辅助优化器时？鬼子不无得意地回答：“That’s right!”

 

2. 再次感叹SQL Profile的牛！

若干年之后的2014年，在面对一条将近200行的SQL语句进行优化时，发现该语句执行计划已经基本找不出明显问题，例如既没有全表扫描，也没有全索引扫描，甚至语句的Cost也非常低（当然Cost并不十分准确）。但是语句执行效率并不高，达到30秒，资源消耗也非常高，例如Buffer Gets达到1,246,155次。客户当然不满意，如何进一步优化？

山穷水尽之际，想起了上述多年前的往事，更想起了神奇的SQL Profile技术。于是，在搜索到最新的11g文档《Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)》之后，照猫画虎般地开练了。效果如何？以下就是优化前后的对比：

这是优化之前的各项指标：

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	30,273	30,272.96	17.76
CPU Time (ms)	29,968	29,968.19	17.79
Executions	1
Buffer Gets	1,246,155	1,246,155.00	14.68
Disk Reads	5,437	5,437.00	0.80

这是优化之后的各项指标：

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (ms)	4,653	4,652.71	3.00
CPU Time (ms)	4,470	4,470.23	2.90
Executions	1
Buffer Gets	303,480	303,480.00	2.32
Disk Reads	9,740	9,740.00	1.39

可见，语句响应速度从30秒下降到4秒多，Buffer Gets从1,246,155下降到303,480！我对语句没做任何改动，也没创建新的索引，执行计划就更好了，实际效果更是如此的好！SQL Profile牛啊！

 

3. 实施细节

下面就是11g自动优化工具和SQL Profile技术综合运用的详细过程：

• 生成自动优化任务

declare

my_task_name VARCHAR2(30);

my_sqltext CLOB;

begin

my_sqltext := ‘<欲调优的SQL语句文本>’;

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => my_sqltext,

user_name => ‘<用户名>’,

scope => ‘COMPREHENSIVE’,

time_limit => 60,

task_name => ‘test1’,

description => ‘Task to tune a query on a specified table’);

end;

/

• 执行自动优化任务

begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘test1’);

end;

/

• 查询Oracle产生的自动优化报告

set long 10000

set longchunksize 1000

set linesize 100

set heading off

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘test1’) from DUAL;

set heading on

• 接受Oracle自动优化任务产生的SQL Profile

DECLARE

my_sqlprofile_name VARCHAR2(30);

begin

my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (

task_name => ‘test1’,

name => ‘test1’);

end;

/

OK了，可以运行需要调优的语句，并观察优化效果了。效果就是上面显示的那组令人激动不已的数据，而且在Oracle产生的新执行计划中，明白无误地显示采用SQL Profile了：

“SQL profile “test1” used for this statement ”

 

各位读者现在只需将你需要优化的语句和所属用户名填入上述脚本之中，也可以照葫芦画瓢开练了。

 

4. SQL Profile到底是什么东西？

SQL Profile信息存储在Oracle数据字典之中，除了dba_sql_profiles视图显示的有限信息之外，的确有种看不见、摸不着的讳莫如深的感觉。SQL Profile到底是什么东西？其实SQL Profiling可以与表和统计信息的关系相类比，SQL Profile就是一条SQL语句的统计信息。例如：当我们遇到一个复杂且资源消耗非常大SQL语句时，Oracle可通过一些取样的数据，或者可以执行该语句一个片段，以及分析该语句的历史执行情况，来评估整体执行计划是否最优化。而这些辅助信息，就是SQL Profile信息，并保存在数据字典之中。

SQL Profiling工作原理如下图：

即上图上半部分显示11g自动优化工具SQL Tuning Advisor在针对某条SQL语句产生SQL Profile信息之后，在上图的下半部分，当Oracle正式需要执行该SQL语句时，优化器不仅利用该语句所访问对象的统计信息，而且利用SQL Profile信息，来产生整体上更优的执行计划。

 

5. 什么时候该使用自动调优工具和SQL Profile？

Oracle 11g的自动调优工具和SQL Profile的确像潘多拉盒子一样充满魔力。继续上述优化案例，尽管该语句被Oracle优化了，但我仔细对照了优化前后50多步的执行计划，怎么也没找出到底是哪些步骤被Oracle优化得效果如此之好，真是太神奇了!

是否一遇到复杂语句就依靠自动调优工具和SQL Profile进行优化呢？且慢，首先，尽管应用性能问题很多，但最主要的问题还是一些传统的、基础性问题。例如：缺乏合适的索引；复合索引设计不合理，特别是索引顺序不对，导致索引效率不高；SQL语句中错误地使用函数，导致索引无法使用；等等。针对这些问题，合理运用20%的基础技术，特别是索引技术，其实能解决80%的问题。这些技术也是DBA和应用开发人员的基本功和基本设计开发规范，过度依赖自动化工具反而会让我们自己的基本技能退化的。其次，自动调优工具和SQL Profile也非包治百病的灵丹妙药，也有看走眼的时候。Oracle自动工具怎么可能比你更了解你的数据模型和数据分布情况，进而给出更准确的优化策略呢？第三，Oracle自动工具使用起来也并不简单，而且需要DBA与开发人员紧密配合，针对大部分基础性问题，有经验的DBA和开发人员其实一眼就能看出问题，何必杀鸡用牛刀呢？

那何时使用自动调优工具和SQL Profile进行优化呢？本人的经验：当针对一些复杂SQL语句，运用传统的、人工分析方法难以奏效时，建议尝试使用这些新技术。

 

无论如何，Oracle 11g的自动调优工具和SQL Profile还是牛！不得不服！

学完本课后，应能完成以下工作：

• 定义星形方案
• 展示没有转换的星形查询计划
• 定义星形转换要求
• 展示转换后的星形查询计划

 

星形方案模型

星形方案是最简单的数据仓库方案。之所以将其称作星形方案，是因为此方案的实体关系图类似于星形：多个点呈放射状围绕在中心表周围。星形的中心由一个或多个事实表组成，星形的点是维表。星形方案的特点如下：具有一个或多个非常大的事实表，这些表包含数据仓库中的主要信息，同时还具有许多较小的维表（或查找表），每个维表都包含有关事实表中某个特定属性的多项信息。星形查询是事实表和众多维表之间的联接。每个维表都使用主键联接到事实表的外键，但维表彼此之间没有联接。基于成本的优化程序 (CBO) 可识别星形查询，并为其生成高效的执行计划。事实表通常包含关键字和度量。例如，在销售历史方案中，sales 事实表包含 quantity_sold、amount 和 cost 度量以及 cust_id、time_id、prod_id、channel_id 和 promo_id 关键字。维表是 customers、times、products、channels 和 promotions。例如，products 维表包含事实表中的每个产品编号的信息。

注：可以很容易地将此模型扩展成包括多个事实表。

 

雪花方案模型

 

雪花方案是一种比星形方案更复杂的数据仓库模型，是星形方案的一种类型。之所以将其称作雪花方案，是因为此方案的关系图类似于雪花。雪花方案对维进行了规范化，以消除冗余。也就是将维数据划分到多个表中，而不是放在一个大表中。例如，在雪花方案中，星形方案中的产品维表在规范化后，可能变成一个 products 表、一个 product_category 表，以及一个 product_manufacturer 表，或如幻灯片所示，可以使用 countries 表来规范化 customers 表。虽然这样可节省空间，但增加了维表的数量，因此需要更多的外键联接。导致的结果是查询的复杂度增加，并且查询性能有所降低。

注：建议您优先选择星形方案，除非有明确的理由，否则不要选择雪花方案。

 

星形查询：示例

 

星形查询：示例
请考虑幻灯片中的星形查询。为了运行星形转换，假定销售历史方案的 sales 表在 time_id、channel_id 和 cust_id 列上有位图索引。

SELECT ch.channel_class, c.cust_city,  
       t.calendar_quarter_desc, 
       SUM(s.amount_sold) sales_amount 
 
FROM sales s,times t,customers c,channels ch 
WHERE s.time_id = t.time_id AND  
      s.cust_id = c.cust_id AND 
      s.channel_id = ch.channel_id AND  
      c.cust_state_province = 'CA' AND  
      ch.channel_desc IN ('Internet','Catalog') AND  
      t.calendar_quarter_desc IN ('1999-Q1','1999-Q2') 
 
GROUP BY ch.channel_class, c.cust_city,  
         t.calendar_quarter_desc; 

没有星形转换的执行计划

首先观察一下不进行星形转换的情况下星形方案中的联接的处理过程，然后再来了解此转换的优点。
幻灯片中计划的基本问题是查询总是启动 SALES 表到维表的联接。这会导致大量的行，这些行只能由执行计划中的其它父联接进行缩减。

星形转换

 

• 在事实表外键上创建位图索引。
• 将 STAR_TRANSFORMATION_ENABLED 设置为 TRUE。
• 至少需要两个维表和一个事实表。
• 收集所有相应对象的统计信息。
• 执行过程分两个阶段：

–首先，基于维过滤器使用位图索引识别相关的事实行。

–将它们联接到维表。

 

要使星形查询获得尽可能高的性能，必须遵循一些基本准则：

• 位图索引应构建在事实表或表的各个外键列之上。
• STAR_TRANSFORMATION_ENABLED 初始化参数应设置为 TRUE。这会为星形查询启用一个重要的优化程序功能。为了实现向后兼容，其默认设置为 FALSE。

如果数据仓库满足这些条件，则在其中运行的大部分星形查询都会使用称为“星形转换”的查询执行策略。借助星形转换，星形查询可以具有很高的查询性能。

星形转换是一种功能强大的优化技术，该技术基于隐式重写（或转换）原始星形查询的 SQL。最终用户不需要了解星形转换的任何细节。系统的 CBO 会自动选择适合进行星形转换的地方。Oracle 处理星形查询的过程分两个基本阶段：

• 第一阶段仅从事实表（结果集）检索必要的行。由于此检索利用了位图索引，因此非常高效。
• 第二个阶段将此结果集联接至维表。此操作称为半联接。

注：查询中至少使用三个表（两个维表和一个事实表）。

星形转换：注意事项

• 不会转换包含绑定变量的查询。
• 不会转换引用远程事实表的查询。
• 不会转换包含反联接表的查询。
• 不会转换引用未合并的未分区视图的查询。

星形转换不适用于具有以下任一特征的表：

• 查询具有与位图访问路径不兼容的表提示
• 查询包含绑定变量
• 表具有的位图索引过少。事实表列上必须有一个位图索引，优化程序才能为其生成子查询。
• 远程事实表。但在所生成的子查询中可以有远程维表。
• 反联接表。
• 表已用作子查询中的维表。
• 表实际上是未合并的视图，而不是视图分区。

 

星形转换：重写示例

系统分两个阶段来处理前面提到的查询。在第一阶段中，系统对维表使用过滤器，检索与过滤器匹配的维主键。之后系统使用这些主键来探测事实表外键列上的位图索引，以便仅识别和检索事实表中的必要行。即系统实际上使用幻灯片中经过重写的查询从 sales 表中检索结果集。
注：幻灯片中的 SQL 是理论上的 SQL 语句，用于表示在第一阶段发生的操作。

SELECT s.amount_sold 
FROM sales s 
 
WHERE time_id IN (SELECT time_id  
                  FROM times 
                  WHERE calendar_quarter_desc  
                      IN('1999-Q1','1999-Q2')) 
 
AND   cust_id IN (SELECT cust_id  
                  FROM customers  
                  WHERE cust_state_province = 'CA') 
 
AND channel_id IN(SELECT channel_id  
                  FROM channels  
                  WHERE channel_desc IN  
                            ('Internet','Catalog')); 

 

根据一个维表检索事实行

幻灯片显示了仅使用一个维表检索事实表行的过程。系统基于相应的维过滤谓词（类似于上一张幻灯片示例中的 t.calendar_quarter_desc IN (‘1999-Q1′,’1999-Q2’)）扫描维表，针对每个相应行探测相应的事实表位图索引，获取相应的位图。

BITMAP KEY ITERATION 将其左侧输入的每个关键字作为右侧输入的索引的查找关键字，然后返回该索引获取的所有位图。请注意，在本例中左侧的输入提供了维表的联接关键字。

此树的最后一步将合并在以前步骤中获得的所有位图。此合并操作会生成一个位图，该位图可以表示事实表中与维表的相关行相联接的行。

注： 使用共享服务器模式时，BITMAP_MERGE_AREA_SIZE 在优化此操作性能上起到非常重要的作用。除非对实例配置了共享服务器选项，否则系统不建议使用 BITMAP_MERGE_AREA_SIZE 参数。系统建议您通过设置 PGA_AGGREGATE_TARGET 启用自动调整 SQL 工作区大小功能。保留 BITMAP_MERGE_AREA_SIZE 是为了实现向后兼容。

 

根据所有维表检索事实行

 

在第一阶段，针对各个维表重复上一幻灯片中提到的步骤。因此计划中的每个 BITMAP MERGE 会为一个维表生成一个位图。要从事实表中识别出所有相关的行，系统必须对生成的所有位图取交集。这样做是为了清除只与一个维表联接，但未与所有维表联接的事实行。这是通过对根据每个维表生成的所有位图执行非常高效的 BITMAP AND 操作实现的。最后生成的位图可以表示事实表中与维表的所有合格行相联接的行。

注：到现在为止，仅使用了事实表位图索引和维表。要进一步访问事实表，系统必须将生成的位图转换成行 ID 集。

 

将临时结果集与维表相联接

 

确定结果集之后，系统将进入星形转换算法的第 2 阶段。在此阶段，需要将与结果集对应的销售数据与用于对行分组且属于查询的选择列表的维表数据相联接。

请注意，幻灯片中的图显示了在事实表与其维表之间执行的散列联接。尽管从统计结果看，散列联接是最常用的一种联接星形查询中的行的技术，但可能并不总是如此，具体采用的技术由 CBO 评估。

 

星形转换计划：示例 1

 

此计划可以用来回答“没有星形转换的执行计划”部分中显示的查询。请注意，出于格式化方面的原因，仅显示了通道和时间维。可以很容易地将此案例扩展为 n 维。

注：假定 sales 没有分区。

 

星形转换：进一步优化

• 在星形转换执行计划中，对维表进行了两次访问；每个阶段访问一次。
• 如果维表很大，而选择率很低，这可能会造成性能问题。
• 如果创建临时表的成本较低，系统可能会决定使用此方式，而不访问同一维表两次。
• 在计划中创建临时表：

查看前面的执行计划时，您会发现每个维表都需要访问两次：
在第一阶段中，系统会访问一次维表来决定必要的事实表行；在第二阶段中，系统会再访问一次维表来将事实行与每个维表相联接。如果维表很大，且在访问这些维表时没有有助于解决问题的快速访问路径，则可能会造成性能问题。在此类情况下，系统可能会决定创建临时表，表中包含两个阶段所需的信息。如果创建一个由维表的谓词和联接列的结果集组成的临时表所花费的成本比访问维表两次的成本低，则系统会采用前一种方式。在前面的执行计划示例中，TIMES 和 CHANNELS 表很小，使用全表扫描对其进行访问的工作量并不是很大。

上面的执行计划显示了这些临时表的创建和数据插入方式。这些临时表的名称是系统生成的，并且不是固定的。幻灯片中显示了某执行计划的一部分，该执行计划对 CUSTOMERS 表使用了临时表。

注：另外，在下列情况下星形转换不使用临时表：

• 数据库处于只读模式。
• 星形查询所属的事务处理处于可串行化模式。

 

 

使用位图联接索引

 

• 减少了要联接的数据量
• 可以用于消除按位操作
• 在存储方面比 MJV 效率高

 

CREATE BITMAP INDEX sales_q_bjx

ON sales(times.calendar_quarter_desc)

FROM sales, times

WHERE sales.time_id = times.time_id

 

如果用作联接的联接索引已预先计算好，则会减少需要联接的数据量。

另外，包含多个维表的联接索引可以消除按位操作，这些操作在使用现有位图索引的星形转换中是必不可少的。

最后，位图联接索引在存储方面比实体化联接视图 (MJV) 效率高，因为 MJV 不对事实表的行 ID 进行压缩。

后面假设您已创建了幻灯片中提及的附加索引结构。

 

星形转换计划：示例 2

 

SORT GROUP BY 
 HASH JOIN 
    HASH JOIN 
       TABLE ACCESS BY INDEX ROWID SALES 
         BITMAP CONVERSION TO ROWIDS 
          BITMAP AND 
           BITMAP MERGE 
            BITMAP KEY ITERATION 
             BUFFER SORT 
              TABLE ACCESS FULL CHANNELS 
             BITMAP INDEX RANGE SCAN SALES_CHANNELS_BX 
           BITMAP OR 
             BITMAP INDEX SINGLE VALUE SALES_Q_BJX 
             BITMAP INDEX SINGLE VALUE SALES_Q_BJX 
       TABLE ACCESS FULL CHANNELS 
    TABLE ACCESS FULL TIMES 

对同一星形查询使用位图联接索引进行处理的过程类似于前面的示例。唯一的区别是系统在星形查询的第一阶段使用联接索引，而不是单个表的位图索引来访问 times 数据。
此计划与上一计划的区别在于，在对 times 维做位图索引扫描的内部部分没有第 1 阶段的重写查询中的子选择语句。这是因为 times.calendar_quarter_desc 的联接谓词信息可从 sales_q_bjx 位图联接索引获取。
请注意，系统会访问联接索引两次，因为相应查询的谓词是 t.calendar_quarter_desc IN (‘1999-Q1′,’1999-Q2’)

 

星形转换提示

 

• STAR_TRANSFORMATION 提示：使用包含星形转换的最佳计划（如果有）。
• FACT (<table_name>) 提示：应将提示表作为星形转换上下文中的事实表。
• NO_FACT (<table_name>) 提示：不应将提示表作为星形转换上下文中的事实表。
• FACT 和 NO_FACT 提示对于包含多个事实表的星形查询很有用。

 

• STAR_TRANSFORMATION 提示可使优化程序使用其中使用了转换的最佳计划。如果没有提示，优化程序可能会基于成本作出决定，使用已生成的没有转换的最佳计划，而不使用已转换查询的最佳计划，即使给出提示，也不能保证转换一定会发生。优化程序仅在适当情况下生成子查询。如果没有生成子查询，则没有转换的查询，此时不管是否有提示，都使用未转换查询的最佳计划。
• FACT 提示在星形转换上下文中使用，用于指示转换时应将提示表作为事实表，其它所有表无论大小都作为维表。
• NO_FACT 提示在星形转换上下文中使用，用于指示转换时不应将提示表作为事实表。

注：仅当星形查询要访问多个事实表时，才可能用到 FACT 和 NO_FACT 提示。

 

位图联接索引：联接模型 1

CREATE BITMAP INDEX bji ON f(d.c1)  

FROM f, d  

WHERE d.pk = f.fk;

SELECT sum(f.facts)

FROM d, f

WHERE d.pk = f.fk AND d.c1 = 1;

 

在接下来的三张幻灯片中，F 代表事实表，D 代表维表，PK 代表主键，FK 代表外键。

位图联接索引可以在幻灯片所示的 SELECT 语句中使用，用于避免联接操作。

位图联接索引类似于实体化联接视图，它预先计算联接，并将其存储为数据库对象。二者之间的区别在于：实体化联接视图会使联接实体化为一个表，而位图联接索引会将联接实体化成一个位图索引。

注： C1 是维表中的索引列。

 

 

位图联接索引：联接模型 2

本幻灯片中的模型是模型 1 的扩展形式，需要使用级联位图联接索引来表示。

请注意，本例中的 BJX 也可以用来回答下列选择语句：

select sum(f.facts) from d,f where d.pk=f.fk and d.c1=1

这是因为 D.C1 位于 BJX 的第一部分。

 

 

位图联接索引：联接模型 3

此模型也需要使用幻灯片中所示的级联位图联接索引。在本例中使用了两个维表。

位图联接索引：联接模型 4

本幻灯片显示了在两个或更多维表之间存在联接的雪花模型。可以用位图联接索引来表示它。位图联接索引可以是单个的，也可以是级联的，具体取决于维表中用于索引的列的数量。可以在 D1.C1 上创建一个位图联接索引，在 D1 和 D2 之间以及 D2 和 F 之间建立联接，如幻灯片中的 BJX 所示。