Oracle优化器：星型转换（Star Query Transformation ）

2021-12-08 21:22:12

Oracle优化器：星型转换（Star Query Transformation ）

Star query是一个事实表（fact table）和一些维度表（dimension）的join。每个维度表都跟事实表通过主外键join，且每个维度表之间不join。

星型模式设计的好处：

					1） 提供了直接、直观的业务实体与终端用户schema设计的映射。 
				
					2） 对典型的star query提供了高性能的优化。 
				
					3） 通过大量的商业智能工具的广泛支持，可能会期望甚至要求数据仓库架构包含维度表。

					星型模式是用于简单的数据集市和大型数据仓库。 
				

Oracle优化器：星型转换（Star Query Transformation ）

雪花状模式（snowflake schema）：

					Snowflake schema是star schema的一种，但更复杂。它的维度数据被分到多个表而不是一个大表。更多的维度表，更多的外键joins，使查询更复杂，查询性能下降。 
				

					Oracle建议使用star schema替代snowflake schema，除非你有别的原因。 
				

Oracle优化器：星型转换（Star Query Transformation ）

优化Star Query：

					1） 在fact table的每个外键列上建立bitmap index。 
				
					2） 初始化参数STAR_TRANSFORMATION_ENABLED 应被设为TRUE，默认false。

					星型转换（Star transformation）能为star query提供高效的查询性能。 
				

Oracle优化器：星型转换（Star Query Transformation ）

					合适的情况下，Oracle会自动选择star transformation技术，隐视重写star query SQL，提高star query效率。

					星型查询2个基本步骤： 
				
					1） 从fact table中检索出必要的结果集。（bitmap index会提高效率） 
				
					2） 结果集与维度表joins。

星型转换(bitmap index)

					通过bitmap AND操作将3个维度表bitmaps为一个单独的bitmap，然后与fact table通过bitmap indexes逻辑JOIN。 
				

					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-01', '1999-02') 
				
					 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

星型转换（a Bitmap Join Index）

					--建一个bitmap join index 
				
					CREATE BITMAP INDEX sales_c_state_bjix ON sales(customers.cust_state_province) 
				
					FROM sales, customers 
				
					WHERE sales.cust_id = customers.cust_id 
				
					LOCAL NOLOGGING COMPUTE STATISTICS;

					即使用bitmap join index代替与表customer的bitmap join。 
				

星型转换的限制

					1、 下面的情况不支持星型转换 
				
					?  查询中使用hint 
				
					?  查询包含绑定变量 
				
					?  Fact table上的bitmap indexes太少 
				
					?  远程fact tables 
				
					?  Anti-joined tables 
				
					?  Fact table是一个unmerged view 
				
					?  Fact table是一个partitioned view 
				
					2、 优化器不选择star transformation的情况 
				
					?  表有一个好的单表访问路径 
				
					?  表太小不值得转换 
				
					3、临时表在下面情况下不适用star transformation 
				
					?  数据库read-only模式 
				
					?  Star query是串行事务的一部分

优化星型查询

当你使用星型查询时，你需要考虑以下两点：

调整星型查询
使用星型转换

调整星型查询
为了获得星型查询的最佳性能，遵循一些基本准则是非常重要的：

应该为事实表的每一个外键列都创建位图索引。
初始化参数STAR_TRANSFORMATION_ENABLED应设置为TRUE。这将开启对星型查询的重要优化功能。为了向下兼容，它在默认情况下设置为FALSE。

当一个数据仓库满足这些条件，在数据仓库中运行的大多数星型查询将会使用被称为星形转换的查询执行策略。星型转换为星型查询提供了非常高效的查询性能。

使用星型转换

星型转换是依靠隐式重写（或转换）原始星型查询SQL的强大优化技术。最终用户不需要知道任何关于星形转换的细节。 Oracle数据库的查询优化器会在合适的地方自动选择星型转换。

星型转换是一个查询转换，旨在高效执行星型查询。 Oracle数据库使用两个基本阶段来处理星型查询。第一阶段是从事实表（结果集）精确地检索出必要的行。由于这种检索使用了位图索引，因此是非常高效的。第二阶段是将一阶段查到的结果集与维度表相结合。最终用户查询的一个例子是：“在西部和西南部地区的销售门店的最后三个季度，食品部门的销售额和利润是多少？”这是一个简单的星型查询。

使用位图索引的星型转换
星型转换的一个前提条件，即在事实表的每一个连接列上都有一个单列位图索引。这些连接列包括所有的外键列。
例如，sh示例模式下的sales表，分别在TIME_ID，CHANNEL_ID，CUST_ID，PROD_ID和promo_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;

该查询分两个阶段进行处理。在第一阶段，Oracle数据库使用事实表外键列的位图索引从事实表中找出并检索出必要的行。也就是说，Oracle数据库从事实表中检索结果集，从本质上是使用下面的查询：

			点击(此处)折叠或打开
		

		
				SELECT ... FROM sales

				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\'));

这是该算法的转换步骤，因为原始星型查询已被改造成子查询表示方式。访问事实表的这种方法利用了位图索引的优势。直观地说，在关系数据库中位图索引提供了基于集合的处理方案。 Oracle实现了非常快速的方法去处理集合操作，如AND（交集），OR（并集），MINUS和COUNT。

在这个星形查询中，TIME_ID位图索引用于标识事实表中销售时间在1999年-Q1所有行的集合。这个集合被表示为位图（一个由1和0组成的字符串，用来表示事实表中的哪些行属于该集合）。

一个类似的位图检索对应sales事实表中1999年第二季度的的所有行。该位图的或操作用于合并Q1销售结果集与Q2销售结果集。

另外还将在客户维度，产品维度来完成集合操作。在星型查询处理的这一点上，有三个位图。每个位图对应于一个单独的维度表，并且每个位图代表了事实表中满足单独维度约束的行的集合。

这三个位图通过AND操作被合并成一个单独的位图。这个最终的位图表示了事实表中满足所有维度约束的行集合。这就是结果集，从评估查询所需的事实表行的确切集合。请注意，没有任何事实表中的实际数据被访问。所有这些操作完全依赖位图索引和维度表。因为位图索引的压缩数据表示，位图集合操作是非常高效的。

一旦确认了结果集，可以通过位图来访问sales表的实际数据。从事实表中仅仅检索需要的数据。在这一点上，Oracle数据库，有效地将所有维度表和事实表结合了起来。这种技术提供了优异的性能，因为Oracle数据库使用了一个逻辑的连接操作将所有维度表和事实表连接恰里，而不是将每个维度表与事实表分别进行连接。。

该查询的第二阶段是将事实表中的行（结果集）与维度表连接在一起。 Oracle使用最有效的方法来访问和连接维度表。许多维度表非常小，并且全表扫描通常是针对这些维度表的最有效的访问方法。对于大尺寸的表，全表扫描可能不是最有效的访问方法。在前面的例子中， product.department列的位图索引可以用来快速识别在食品部门的所有产品。基于优化程序对每个维度表的大小和数据分布的判断，Oracle数据库的优化器会针对给定的维度表来自动确定哪种访问方法是最适合的。

对于每个维度表而言，具体连接方法（以及索引方法）同样将被优化器智能地确定。哈希连接往往是连接维度表最有效的算法。一旦连接了所有的维度表，最终的结果将返回到用户。从一个表中检索出匹配行，然后连接到另一个表的查询技术通常被称为半连接。

使用位图索引星型转换的执行计划
下面这个典型的执行计划是由带位图索引的星型转换生成的：

			点击(此处)折叠或打开
		

		
				SELECT STATEMENT

				 SORT GROUP BY

				  HASH JOIN

				   TABLE ACCESS FULL CHANNELS

				   HASH JOIN

				    TABLE ACCESS FULL CUSTOMERS

				    HASH JOIN

				     TABLE ACCESS FULL TIMES

				     PARTITION RANGE ITERATOR

				      TABLE ACCESS BY LOCAL INDEX ROWID SALES

				       BITMAP CONVERSION TO ROWIDS

				        BITMAP AND

				         BITMAP MERGE

				          BITMAP KEY ITERATION

				           BUFFER SORT

				            TABLE ACCESS FULL CUSTOMERS

				           BITMAP INDEX RANGE SCAN SALES_CUST_BIX

				         BITMAP MERGE

				          BITMAP KEY ITERATION

				           BUFFER SORT

				            TABLE ACCESS FULL CHANNELS

				           BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX

				         BITMAP MERGE

				          BITMAP KEY ITERATION

				           BUFFER SORT

				            TABLE ACCESS FULL TIMES

				           BITMAP INDEX RANGE SCAN SALES_TIME_BIX

在这个计划中，是通过一个由三个位图合并而来的位图访问路径来访问事实表。这三个位图是BITMAP MERGE根据行资源树的位图生成的。每个这样的行资源树是从子查询行资源树的位图键迭代行源组成，在这个例子是一个全表扫描。对于每一个这样的值，位图键迭代行源从位图索引中检索位图。在相应的事实表行通过这种访问路径被检索到以后，它们与维度表及临时表合并产生的查询结果。

使用位图连接索引的星型转换

除了位图索引，您可以在星型转换中使用位图连接索引。假设你有以下附加索引结构：

			点击(此处)折叠或打开
		

		
				CREATE BITMAP INDEX sales_c_state_bjix

				ON sales(customers.cust_state_province)

				FROM sales, customers

				WHERE sales.cust_id = customers.cust_id

				LOCAL NOLOGGING COMPUTE STATISTICS;

使用位图连接索引的星型查询和之前的例子非常相似，唯一的区别是在星型查询的第一阶段，Oracle利用连接索引，而不是一个单表位图索引，去访问顾客数据。

使用位连接图索引星型转换的执行计划
下面这个典型的执行计划是由带位连接图索引的星型转换生成的：

			点击(此处)折叠或打开
		
				SELECT STATEMENT

				 SORT GROUP BY

				  HASH JOIN

				   TABLE ACCESS FULL CHANNELS

				   HASH JOIN

				    TABLE ACCESS FULL CUSTOMERS

				    HASH JOIN

				     TABLE ACCESS FULL TIMES

				     PARTITION RANGE ALL

				      TABLE ACCESS BY LOCAL INDEX ROWID SALES

				       BITMAP CONVERSION TO ROWIDS

				        BITMAP AND

				         BITMAP INDEX SINGLE VALUE SALES_C_STATE_BJIX

				         BITMAP MERGE

				          BITMAP KEY ITERATION

				           BUFFER SORT

				            TABLE ACCESS FULL CHANNELS

				           BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX

				         BITMAP MERGE

				          BITMAP KEY ITERATION

				           BUFFER SORT

				            TABLE ACCESS FULL TIMES

				           BITMAP INDEX RANGE SCAN SALES_TIME_BIX

这个执行计划和前面相比，区别在于使用位图索引扫描顾客维度的那一部分没有子查询。这是因为在customer.cust_state_province的连接述语信息已经满足了位图连接索引sales_c_state_bjix。

Oracle如何选择使用星型转换

优化器可以生成并保存一个未经转换的最优执行计划。如果星型转换被启用，优化器将尝试将其应用到查询；如果适用，则产生一个使用转换查询的最优执行计划。基于这两个版本的执行计划，优化器通过比较二者的成本估算，然后决定使用经过转换的最优执行计划或者是未经转换的版本。

如果查询需要访问事实表中的大部分行，最好使用全表扫描，而不是使用星型转换查询。但是，如果维度表的约束谓词具有充分的可选性，也就是说只会从事实表中检索很小一部分数据，那么基于转换的执行计划很有可能会更好。

需要注意的是，优化器会根据许多标准判断，在它任务合理的情况下才会根据维度表生成子查询。Oracle优化器并不保证为所有维度表生成子查询。基于表和查询的特性，优化器还可以决定该转换是否值得被应用到特定查询中。在这种情况下，优化器将会使用最优计划。

使用星型转换的限制条件

具有任何以下特征的表均不支持星型转换：

?查询使用了与位图访问路径不兼容的表提示（hint）

?查询包含绑定变量

?表没有位图索引。事实表的列必须有位图索引，优化器才能创建子查询。

?远程事实表。然而，子查询中允许使用远程维度表。

?反连接的表

?已经在子查询中用作维度表的表

?表是unmerged视图，并且不是分区视图

?事实表是unmerged视图

?事实表是分区视图

在以下场景优化器可能不会选择星型转换：

?表具有良好的单表访问路径

?表太小，不值得进行转换

此外，在下列条件下星型转换不使用临时表：

?数据库处于只读模式

?星型查询是串行化事务的一部分

Oracle优化器:星型转换

	2010/12/27 BY MACLEAN LIU 1 COMMENT 
	

>>>>

>>>>>>

>>

>>

&

码农公寓

Oracle优化器:星型转换

相关文章