Oracle Star Schema

来源：互联网发布：局域网yum配置编辑：程序博客网时间：2024/05/16 05:30

星型转换是一个非常强大的优化技术，它是通过对原来的SQL语句的隐式的改写来实现的，它能够很大程度减少I/O. 终端用户并不需要知道有关星型转换的任何细节。数据库优化器会在合适的时候进行星型转换。要获得星型转换的最大性能，需要遵循以下3个基本的条件：

1，事实表上的维度列上要有外键
2，事实表的每个外键上都有BITMAP索引。
3，star_transformation_enabled=true。系统默认是false. 它有三个取值：（TRUE, FALSE, TEMP_DISABLE）.其中TEMP_DISABLE表示不允许用临时表来存放第一次扫描的结果集。星型查询中，维度表会被扫描两次，如果维度表很大，性能会很差，所以需要一个临时表来存放第一次扫描的维度表集合。
如果能够满足这三个条件,则查询会使用star transformation,而这是提高基于事实表的查询效率的主要的技术。

数据库进行星型查询时，会使用两个基本的阶段：
第一个阶段从事实表(或者说结果集)里获取所有必要的记录行。由于这是通过bitmap索引来检索数据，因此比较高效。
第二个阶段将该结果集与维度表进行关联。这叫做semi-join(也就是exists和in写法)。
注意：只有oracle企业版才有bitmap索引。标准版不支持bitmap索引和星型转换。

下面的SQL语句执行计划可以很好的说明进行"星型查询"的条件：

SQL> show parameter star_transformation_enabled

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled string FALSE

SQL> conn sh/sh
Connected.

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

Execution Plan
----------------------------------------------------------
Plan hash value: 1612666291

--------------------------------------------------------------------------------
--------------------------

--------------------------------------------------------------------------------
--------------------------

| 0 | SELECT STATEMENT | | 572 | 48048 | 793 (8)|
00:00:10 | | |

| 1 | HASH GROUP BY | | 572 | 48048 | 793 (8)|
00:00:10 | | |

|* 2 | HASH JOIN | | 3116 | 255K| 791 (8)|
00:00:10 | | |

|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)|
00:00:01 | | |

|* 4 | HASH JOIN | | 6231 | 383K| 788 (8)|
00:00:10 | | |

|* 5 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 15 (0)|
00:00:01 | | |

|* 6 | HASH JOIN | | 49822 | 2286K| 771 (8)|
00:00:10 | | |

|* 7 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 332 (2)|
00:00:04 | | |

| 8 | PARTITION RANGE SUBQUERY| | 918K| 18M| 426 (9)|
00:00:06 |KEY(SQ)|KEY(SQ)|

| 9 | TABLE ACCESS FULL | SALES | 918K| 18M| 426 (9)|
00:00:06 |KEY(SQ)|KEY(SQ)|

--------------------------------------------------------------------------------
--------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   3 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
   4 - access("S"."TIME_ID"="T"."TIME_ID")
   5 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')

6 - access("S"."CUST_ID"="C"."CUST_ID")
7 - filter("C"."CUST_STATE_PROVINCE"='CA')

可以看出上面的SQL语句没有用到star transfermation, 因为star_transformation_enabled=false。执行计划只是普通的JOIN操作。

我来把star_transformation_enabled=TRUE，让他用star transfermation技术执行SQL语句。

SQL> alter system set star_transformation_enabled=true;

System altered.

SQL> col PLAN_PLUS_EXP format a120
SQL> set autotrace traceonly exp;

SQL> col PLAN_PLUS_EXP format a120
SQL> set autotrace traceonly exp;
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')
11 GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;

Execution Plan
----------------------------------------------------------
Plan hash value: 580301883

--------------------------------------------------------------------------------
----------------------------------------------------

--------------------------------------------------------------------------------
----------------------------------------------------

| 0 | SELECT STATEMENT | |
1 | 73 | 786 (2)| 00:00:10 | | |

| 1 | TEMP TABLE TRANSFORMATION | |
| | | | | |

|* 3 | TABLE ACCESS FULL | CUSTOMERS | 3
83 | 9958 | 332 (2)| 00:00:04 | | |

| 4 | HASH GROUP BY | |
1 | 73 | 455 (2)| 00:00:06 | | |

|* 5 | HASH JOIN | |
1 | 73 | 454 (2)| 00:00:06 | | |

|* 6 | HASH JOIN | |
2 | 104 | 450 (2)| 00:00:06 | | |

|* 7 | HASH JOIN | |
32 | 1184 | 448 (2)| 00:00:06 | | |

|* 8 | TABLE ACCESS FULL | TIMES | 1
83 | 2928 | 15 (0)| 00:00:01 | | |

| 9 | PARTITION RANGE SUBQUERY | | 2
54 | 5334 | 432 (2)| 00:00:06 |KEY(SQ)|KEY(SQ)|

| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 2
54 | 5334 | 432 (2)| 00:00:06 |KEY(SQ)|KEY(SQ)|

| 11 | BITMAP CONVERSION TO ROWIDS | |
| | | | | |

| 12 | BITMAP AND | |
| | | | | |

| 13 | BITMAP MERGE | |
| | | | | |

| 14 | BITMAP KEY ITERATION | |
| | | | | |

| 15 | BUFFER SORT | |
| | | | | |

|* 16 | TABLE ACCESS FULL | CHANNELS |
2 | 42 | 3 (0)| 00:00:01 | | |

| 18 | BITMAP MERGE | |
| | | | | |

| 19 | BITMAP KEY ITERATION | |
| | | | | |

| 20 | BUFFER SORT | |
| | | | | |

|* 21 | TABLE ACCESS FULL | TIMES | 1
83 | 2928 | 15 (0)| 00:00:01 | | |

| 23 | BITMAP MERGE | |
| | | | | |

| 24 | BITMAP KEY ITERATION | |
| | | | | |

| 25 | BUFFER SORT | |
| | | | | |

| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_7E653 |
1 | 13 | 2 (0)| 00:00:01 | | |

| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_7E653 | 3
83 | 5745 | 2 (0)| 00:00:01 | | |

|* 29 | TABLE ACCESS FULL | CHANNELS |
2 | 42 | 3 (0)| 00:00:01 | | |

--------------------------------------------------------------------------------
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("C"."CUST_STATE_PROVINCE"='CA')
   5 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
   6 - access("S"."CUST_ID"="C0")
   7 - access("S"."TIME_ID"="T"."TIME_ID")
   8 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')

16 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
17 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
21 - filter("T"."CALENDAR_QUARTER_DESC"='1999-Q1' OR "T"."CALENDAR_QUARTER_DES
C"='1999-Q2')

22 - access("S"."TIME_ID"="T"."TIME_ID")
27 - access("S"."CUST_ID"="C0")
29 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')

从上面执行计划可以看出，此SQL语句做了star transformation. 走了BITMAP索引，例如：BITMAP MERGE ,BITMAP AND等等。