|
|
星型转换的基本思路是尽量避免直接去扫描星型模式中的事实表，因为这些事实表总会因为存有大量数据而十分庞大，对这些表的全表扫描会引起大量物理读并且效率低下。在典型的星型查询中，事实表总是会和多个与之相比小得多的维度表发生连接(join)操作。典型的事实表针对每一个维度表会存在一个外键(foreign key)，除去这些键值(key)外还会存在一些度量字段譬如销售额度(sales amount)。与之对应的键值(key)在维度表上扮演主键的角色。而事实表与维度表间的连接操作一般都会发生在事实表上的外键和与之对应的维度表的主键间。同时这类查询总是会在维度表的其他列上存在限制十分严格的过滤谓词。充分结合这些维度表上的过滤谓词可以有效减少需要从事实表上访问的数据集合。这也就是星型转换(star transformation)的根本目的，仅访问事实表上相关的、过滤后精简的数据集合。
星型转换可以有效改善大的事实表与多个具有良好选择率的维度表间连接的查询。星型转换有效避免了全表扫描的性能窘境。它只fetch那些事实表上的”绝对”相关行。同时星型转换是基于CBO优化器的，Oracle能很好地认清使用该种转换是否有利。一旦维度表上的过滤无法有效减少需要从事实表上处理的数据集和时，那么可能全表扫描相对而言更为恰当。
以上我们力图通过一些简单的查询和执行计划来诠释星型转换的基本理念，但现实生产环境中实际的查询语句可能要复杂的多；举例而言如果查询涉及星型模型中的多个事实表的话，那么其复杂度就大幅提高了；如何正确构建事实表上的索引，收集相关列上的柱状图信息，在Oracle优化器无法正确判断的情况下循循善诱，都是大型数据仓库环境中DBA所面临的难题。
TEMP_DISABLE:当一个维度表超过100个块时，”如果简单地设置star_transformation_enabled为TRUE来启用星型变换，那么会话会创建一个内存中的全局临时表(global temporary table)来保存已过滤的维度数据，这在过去会造成很多问题;”这里说的100个块其实是隐式参数_temp_tran_block_threshold(number of blocks for a dimension before we temp transform)的默认值，此外隐式参数_temp_tran_cache(determines if temp table is created with cache option，默认为TRUE)决定了这类临时表是否被缓存住；为了避免创建全局临时表可能带来的问题，就可以用到TEMP_DISABLE这个禁用临时表的选项，让优化器不再考虑使用物化的临时表。
set autotace on;
1、执行如下语句，注意sales表跟多个表做连接查询，但是其它的表之间没有连接条件：
SQL>>
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-01','1999-02','2000-03','2000-04')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 593420798
-------------------------------------------------------------------------------------------------------------
|> -------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1144 | 96096 | 964 (3)| 00:00:12 | | |
| 1 | HASH GROUP BY | | 1144 | 96096 | 964 (3)| 00:00:12 | | |
|* 2 | HASH JOIN | | 6231 | 511K| 963 (3)| 00:00:12 | | |
|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 12462 | 766K| 960 (3)| 00:00:12 | | |
| 5 | PART JOIN FILTER CREATE | :BF0000 | 365 | 5840 | 18 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS FULL | TIMES | 365 | 5840 | 18 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 49822 | 2286K| 941 (3)| 00:00:12 | | |
|* 8 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
| 9 | PARTITION RANGE JOIN-FILTER| | 918K| 18M| 530 (3)| 00:00:07 |:BF0000|:BF0000|
| 10 | TABLE ACCESS FULL | SALES | 918K| 18M| 530 (3)| 00:00:07 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
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")
6 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
"T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
7 - access("S"."CUST_ID"="C"."CUST_ID")
8 - filter("C"."CUST_STATE_PROVINCE"='CA')
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
Statistics
----------------------------------------------------------
2793 recursive calls
2 db block gets
6185 consistent gets
2098 physical reads
0 redo> 2168 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
225 sorts (memory)
0 sorts (disk)
41 rows processed
2、Without modifying the SH schema, how can you improve the execution plan for the query
mentioned in step 2? Verify your solution and explain why it is probably a better solution.
You can use second_run.sql.
a) Enable star transformation in your session. In this step, you do not want to use a
temporary table for the star transformation. Looking at the previous execution plan, the
optimizer estimates the data that is to be manipulated in megabytes. Using the star
transformation as follows, the estimation is now expressed in kilobytes. That is why this
new execution plan is probably a much better> CUSTOMERS table is accessed using full scan twice. If the table is larger, the impact is significant.
SQL>>
SQL>> ALTER SESSION SET star_transformation_enabled=TEMP_DISABLE;
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','2000-03','2000-04')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 1869106227
------------------------------------------------------------------------------------------------------------------------
|> ------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 506 | 46552 | 1405 (1)| 00:00:17 | | |
| 1 | HASH GROUP BY | | 506 | 46552 | 1405 (1)| 00:00:17 | | |
|* 2 | HASH JOIN | | 506 | 46552 | 1404 (1)| 00:00:17 | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
|* 4 | HASH JOIN | | 506 | 33396 | 998 (1)| 00:00:12 | | |
|* 5 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 506 | 22770 | 994 (1)| 00:00:12 | | |
|* 7 | TABLE ACCESS FULL | TIMES | 365 | 5840 | 18 (0)| 00:00:01 | | |
| 8 | VIEW | VW_ST_34C376F1 | 507 | 14703 | 976 (1)| 00:00:12 | | |
| 9 | NESTED LOOPS | | 507 | 28899 | 549 (1)| 00:00:07 | | |
| 10 | PARTITION RANGE SUBQUERY | | 506 | 14195 | 462 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)|
| 11 | BITMAP CONVERSION TO ROWIDS| | 506 | 14195 | 462 (1)| 00:00:06 | | |
| 12 | BITMAP AND | | | | | | | |
| 13 | BITMAP MERGE | | | | | | | |
| 14 | BITMAP KEY ITERATION | | | | | | | |
| 15 | BUFFER SORT | | | | | | | |
|* 16 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | |
|* 17 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 18 | BITMAP MERGE | | | | | | | |
| 19 | BITMAP KEY ITERATION | | | | | | | |
| 20 | BUFFER SORT | | | | | | | |
|* 21 | TABLE ACCESS FULL | TIMES | 365 | 5840 | 18 (0)| 00:00:01 | | |
|* 22 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 23 | BITMAP MERGE | | | | | | | |
| 24 | BITMAP KEY ITERATION | | | | | | | |
| 25 | BUFFER SORT | | | | | | | |
|* 26 | TABLE ACCESS FULL | CUSTOMERS | 383 | 6128 | 406 (1)| 00:00:05 | | |
|* 27 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 28 | TABLE ACCESS BY USER ROWID | SALES | 1 | 29 | 514 (1)| 00:00:07 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
2 - access("ITEM_2"="C"."CUST_ID")
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
4 - access("ITEM_1"="CH"."CHANNEL_ID")
5 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
6 - access("ITEM_3"="T"."TIME_ID")
7 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
"T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
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-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
"T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
22 - access("S"."TIME_ID"="T"."TIME_ID")
26 - filter("C"."CUST_STATE_PROVINCE"='CA')
27 - access("S"."CUST_ID"="C"."CUST_ID")
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- star transformation used for this statement
Statistics
----------------------------------------------------------
2697 recursive calls
0 db block gets
37357 consistent gets
3599 physical reads
0 redo> 2168 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
215 sorts (memory)
0 sorts (disk)
41 rows processed
注意：与第一个查询相比，这里用了星形查询，返回的数据少了，只有K级别，原来的是M级别。但是customers有两次的全表扫描。
3、How would you enhance the previous optimization without changing the SH schema? You
can use third_run.sql.
a) Let the optimizer decide if it is better to use a temporary table. You can try to set the
STAR_TRANSFORMATION_ENABLED parameter to TRUE.
SQL>>
SQL>> ALTER SESSION SET star_transformation_enabled=TRUE;
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','2000-03','2000-04')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 3070449514
---------------------------------------------------------------------------------------------------------------------------------
|> ---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 506 | 40986 | 600 (1)| 00:00:08 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_110935 | | | | | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
| 4 | HASH GROUP BY | | 506 | 40986 | 194 (2)| 00:00:03 | | |
|* 5 | HASH JOIN | | 506 | 40986 | 193 (2)| 00:00:03 | | |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_110935 | 383 | 5745 | 2 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 506 | 33396 | 191 (2)| 00:00:03 | | |
|* 8 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
|* 9 | HASH JOIN | | 506 | 22770 | 187 (1)| 00:00:03 | | |
|* 10 | TABLE ACCESS FULL | TIMES | 365 | 5840 | 18 (0)| 00:00:01 | | |
| 11 | VIEW | VW_ST_62EEF96F | 507 | 14703 | 169 (1)| 00:00:03 | | |
| 12 | NESTED LOOPS | | 507 | 28899 | 145 (0)| 00:00:02 | | |
| 13 | PARTITION RANGE SUBQUERY | | 506 | 14195 | 58 (2)| 00:00:01 |KEY(SQ)|KEY(SQ)|
| 14 | BITMAP CONVERSION TO ROWIDS| | 506 | 14195 | 58 (2)| 00:00:01 | | |
| 15 | BITMAP AND | | | | | | | |
| 16 | BITMAP MERGE | | | | | | | |
| 17 | BITMAP KEY ITERATION | | | | | | | |
| 18 | BUFFER SORT | | | | | | | |
|* 19 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | |
|* 20 | BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 21 | BITMAP MERGE | | | | | | | |
| 22 | BITMAP KEY ITERATION | | | | | | | |
| 23 | BUFFER SORT | | | | | | | |
|* 24 | TABLE ACCESS FULL | TIMES | 365 | 5840 | 18 (0)| 00:00:01 | | |
|* 25 | BITMAP INDEX RANGE SCAN| SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 26 | BITMAP MERGE | | | | | | | |
| 27 | BITMAP KEY ITERATION | | | | | | | |
| 28 | BUFFER SORT | | | | | | | |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_110935 | 383 | 1915 | 2 (0)| 00:00:01 | | |
|* 30 | BITMAP INDEX RANGE SCAN| SALES_CUST_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 31 | TABLE ACCESS BY USER ROWID | SALES | 1 | 29 | 110 (0)| 00:00:02 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
3 - filter("C"."CUST_STATE_PROVINCE"='CA')
5 - access("ITEM_2"="C0")
7 - access("ITEM_1"="CH"."CHANNEL_ID")
8 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
9 - access("ITEM_3"="T"."TIME_ID")
10 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
"T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
19 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
20 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
24 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
"T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
25 - access("S"."TIME_ID"="T"."TIME_ID")
30 - access("S"."CUST_ID"="C0")
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- star transformation used for this statement
Statistics
----------------------------------------------------------
2980 recursive calls
20 db block gets
36271 consistent gets
2208 physical reads
604 redo> 2168 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
230 sorts (memory)
0 sorts (disk)
41 rows processed
注意：执行计划的1－3行指出了TEMP TABLE TRANSFORMATION LOAD AS SELECT TABLE ACCESS FULL CUSTOMERS的全局临时表为SYS_TEMP_0FD9D660B_110935，可以避免多次访问维度表，以后创建bitmap join就在该表上创建。解决了以前的疑惑。
4，How do you eliminate one access on the CUSTOMERS table from the previous execution
plan for the same SELECT statement seen in step 3?
a) Create a bitmap join index between the SALES and CUSTOMERS tables.
6. Try to apply your finding. What happens and why?
a) Because the CUSTOMERS_PK primary key constraint is not enforced, it is not possible to
create a bitmap join index between the SALES and CUSTOMERS tables.
alter table sh.customers enable constraint customers_pk;
为什么知道需要enable customers表的customers_pk的主键约束呢，因为星形查询跟维度表连接的列必须是主键或者唯一键约束，所以在创建bitmap join索引的时候，只要看跟哪张维度表连接，就把该维度表的约束给enable就行了。而且选择的维度表的列必须是查询条件语句中出现的列，比如customers.cust_state_province。
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 sales.time_id = times.time_id AND
sales.cust_id = customers.cust_id AND
sales.channel_id = channels.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','2000-03','2000-04')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
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;
desc user_constraints;
select CONSTRAINT_NAME from user_constraints where TABLE_NAME='CUSTOMERS';
alter table customers enable constraint CUSTOMERS_PK;
Execution Plan
----------------------------------------------------------
Plan hash value: 632695221
-----------------------------------------------------------------------------------------------------------------------------
|> -----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1144 | 96096 | 574 (1)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 1144 | 96096 | 574 (1)| 00:00:07 | | |
|* 2 | HASH JOIN | | 3975 | 326K| 552 (1)| 00:00:07 | | |
|* 3 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 3975 | 244K| 549 (1)| 00:00:07 | | |
|* 5 | TABLE ACCESS FULL | TIMES | 365 | 5840 | 18 (0)| 00:00:01 | | |
|* 6 | HASH JOIN | | 3984 | 182K| 530 (1)| 00:00:07 | | |
|* 7 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
| 8 | PARTITION RANGE SUBQUERY | | 73467 | 1506K| 124 (1)| 00:00:02 |KEY(SQ)|KEY(SQ)|
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 73467 | 1506K| 124 (1)| 00:00:02 |KEY(SQ)|KEY(SQ)|
| 10 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 11 | BITMAP AND | | | | | | | |
| 12 | BITMAP MERGE | | | | | | | |
| 13 | BITMAP KEY ITERATION | | | | | | | |
| 14 | BUFFER SORT | | | | | | | |
|* 15 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | |
|* 16 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 17 | BITMAP MERGE | | | | | | | |
| 18 | BITMAP KEY ITERATION | | | | | | | |
| 19 | BUFFER SORT | | | | | | | |
|* 20 | TABLE ACCESS FULL | TIMES | 365 | 5840 | 18 (0)| 00:00:01 | | |
|* 21 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|
|* 22 | BITMAP INDEX SINGLE VALUE | SALES_C_STATE_BJIX | | | | |KEY(SQ)|KEY(SQ)|
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation> ---------------------------------------------------
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-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
"T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
6 - access("S"."CUST_ID"="C"."CUST_ID")
7 - filter("C"."CUST_STATE_PROVINCE"='CA')
15 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
16 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
20 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02' OR
"T"."CALENDAR_QUARTER_DESC"='2000-03' OR "T"."CALENDAR_QUARTER_DESC"='2000-04')
21 - access("S"."TIME_ID"="T"."TIME_ID")
22 - access("S"."SYS_NC00008$"='CA')
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- star transformation used for this statement
Statistics
----------------------------------------------------------
2808 recursive calls
0 db block gets
9161 consistent gets
2045 physical reads
132 redo> 2168 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
220 sorts (memory)
0 sorts (disk)
41 rows processed
5、
总结：通过比较，当我们建立了位图连接索引后，系统就没有用到两个临时表和视图了，节省了执行的流程。
星形查询的要点：
1、至少是3张表连接查询。
2、一张是事实表，其它是维度表，所谓的事实表是跟其它表连接的表，比如本案例中的sales表。
3、一般在事实表的连接条件的列上创建位图连接索引。
4、纬度表上的连接条件列上是主键约束。
6、位图连接索引：
1、位图连接索引（bitmap join index）是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时，标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时，就不需要执行连接，因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的ROWID进一步改进性能，并且减少访问数据所需的I/O数量。
2、创建位图连接索引时，指定涉及的两个表。相应的语法应该遵循如下模式：
create bitmap index FACT_DIM_COL_IDX
on FACT(DIM.Descr_Col)
from FACT, DIM
where FACT.JoinCol = DIM.JoinCol;
位图连接的语法比较特别，其中包含FROM子句和WHERE子句，并且引用两个单独的表。索引列通常是维度表中的描述列-- 就是说，如果维度是CUSTOMER，并且它的主键是CUSTOMER_ID，则通常索引Customer_Name这样的列。如果事实表名为SALES，可以使用如下的命令创建索引：
create bitmap index SALES_CUST_NAME_IDX
on SALES(CUSTOMER.Customer_Name)
from SALES, CUSTOMER
where SALES.Customer_ID=CUSTOMER.Customer_ID;
如果用户接下来使用指定Customer_Name列值的WHERE子句查询SALES和CUSTOMER表，优化器就可以使用位图连接索引快速返回匹配连接条件和Customer_Name条件的行。
3、限制使用条件
位图连接索引的使用一般会受到限制：只可以索引维度表中的列。用于连接的列必须是维度表中的主键或唯一约束；如果是复合主键，则必须使用连接中的每一列。不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限制也适用于位图连接索引。
创建位图连接索引11g帮助文档入口：
Data Warehousing and Business Intelligence ——> Data Warehousing Guide －>indexes ->Using Bitmap Indexes in Data Warehouses -> 7.3
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2018-9-6 10:24:42
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡