时间：2021-07-01 10:21:17 帮助过：6人阅读
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
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 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 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-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 size
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
本文出自 “11862116” 博客，请务必保留此出处http://11872116.blog.51cto.com/11862116/1959063
Oracle——星型查询
标签：星型查询