Oracle SQL高级编程——位图联结索引

一个典型的数据仓库查询：

这个查询中sales 是事实表 ， 一般很大。

products , customers , channels是维度表 ， 一般很小，可以有冗余。

sh@ prod1> explain plan for 
  2  select sum( s.quantity_sold ) , sum( s.amount_sold ) from sales s , products p , customers c , channels ch
  3  where 
  4  s.prod_id = p.prod_id and 
  5  s.cust_id = c.cust_id and 
  6  s.channel_id = ch.channel_id and 
  7  p.prod_name = 'y box' and 
  8  c.cust_first_name = 'abigail' and 
  9  ch.channel_desc = 'direct_sales' ;

explained.

执行计划如下：

sh@ prod1> select * from table(dbms_xplan.display) ;

plan_table_output
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
plan hash value: 2309889988

--------------------------------------------------------------------------------------------------------------------------
| id  | operation                            | name              | rows  | bytes | cost (%cpu)| time     | pstart| pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | select statement                     |                   |     1 |    75 |   784   (1)| 00:00:10 |       |       |
|   1 |  sort aggregate                      |                   |     1 |    75 |            |          |       |       |
|*  2 |   hash join                          |                   |    20 |  1500 |   784   (1)| 00:00:10 |       |       |
|*  3 |    table access full                 | customers         |    43 |   516 |   405   (1)| 00:00:05 |       |       |
|   4 |    nested loops                      |                   |       |       |            |          |       |       |
|   5 |     nested loops                     |                   |  3235 |   199k|   378   (0)| 00:00:05 |       |       |
|   6 |      merge join cartesian            |                   |     1 |    43 |     6   (0)| 00:00:01 |       |       |
|*  7 |       table access full              | channels          |     1 |    13 |     3   (0)| 00:00:01 |       |       |
|   8 |       buffer sort                    |                   |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|*  9 |        table access full             | products          |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|  10 |      partition range all             |                   |       |       |            |          |     1 |    28 |
|  11 |       bitmap conversion to rowids    |                   |       |       |            |          |       |       |
|  12 |        bitmap and                    |                   |       |       |            |          |       |       |
|* 13 |         bitmap index single value    | sales_prod_bix    |       |       |            |          |     1 |    28 |
|* 14 |         bitmap index single value    | sales_channel_bix |       |       |            |          |     1 |    28 |
|  15 |     table access by local index rowid| sales             |  3190 | 63800 |   378   (0)| 00:00:05 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------------------

predicate information (identified by operation id):
---------------------------------------------------

   2 - access("s"."cust_id"="c"."cust_id")

plan_table_output
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   3 - filter("c"."cust_first_name"='abigail')
   7 - filter("ch"."channel_desc"='direct_sales')
   9 - filter("p"."prod_name"='y box')
  13 - access("s"."prod_id"="p"."prod_id")
  14 - access("s"."channel_id"="ch"."channel_id")

32 rows selected.

elapsed: 00:00:00.56

事实表必须有主键或唯一约束，而且主键必须是validated状态的。
将约束的状态改为validated（经过验证的），因为有时候有可能会有不符合的值出现在里面，比如enable novalidate操作后。

sh@ prod1> alter table products modify primary key validate ;

table altered.

elapsed: 00:00:01.44
sh@ prod1> alter table customers modify primary key validate ;

table altered.

elapsed: 00:00:00.23
sh@ prod1> alter table channels modify primary key validate ;

table altered.

elapsed: 00:00:00.23

需要注意的是，索引是建立在谓词列上的，这些列来自扩展后的事实表。

create bitmap index sales_bji1 on sales(p.prod_name , c.cust_first_name , ch.channel_desc )
  2  from sales s , products p , customers c , channels ch 
  3  where 
  4  s.prod_id = p.prod_id and 
  5  s.cust_id = c.cust_id and 
s.channel_id = ch.channel_id 
  7  local ;

index created.

elapsed: 00:01:54.07

重新来看

explain plan for 
  2  select sum( s.quantity_sold ) , sum( s.amount_sold ) from sales s , products p , customers c , channels ch
where s.prod_id = p.prod_id and  s.cust_id = c.cust_id and  s.channel_id = ch.channel_id and  p.prod_name = 'y box' and 
  4  c.cust_first_name = 'abigail' and  ch.channel_desc = 'direct_sales' ;

explained.

elapsed: 00:00:00.23
sh@ prod1> select * from table(dbms_xplan.display) ;

plan_table_output
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
plan hash value: 1034966156

------------------------------------------------------------------------------------------------------------------
| id  | operation                           | name       | rows  | bytes | cost (%cpu)| time     | pstart| pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | select statement                    |            |     1 |    20 |    61   (0)| 00:00:01 |       |       |
|   1 |  sort aggregate                     |            |     1 |    20 |            |          |       |       |
|   2 |   partition range all               |            |    19 |   380 |    61   (0)| 00:00:01 |     1 |    28 |
|   3 |    table access by local index rowid| sales      |    19 |   380 |    61   (0)| 00:00:01 |     1 |    28 |
|   4 |     bitmap conversion to rowids     |            |       |       |            |          |       |       |
|*  5 |      bitmap index single value      | sales_bji1 |       |       |            |          |     1 |    28 |
------------------------------------------------------------------------------------------------------------------

predicate information (identified by operation id):
---------------------------------------------------

   5 - access("s"."sys_nc00008$"='y box' and "s"."sys_nc00009$"='abigail' and
              "s"."sys_nc00010$"='direct_sales')

18 rows selected.

elapsed: 00:00:00.29

执行计划得到了极大的简化。