Oracle SQL高级编程——位图联结索引
程序员文章站
2023-10-11 23:31:47
一个典型的数据仓库查询：
这个查询中sales 是事实表 ， 一般很大。
products , customers , channels是维度表 ， 一般很小，可以有冗余。
sh@ prod1&...
一个典型的数据仓库查询：
这个查询中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
执行计划得到了极大的简化。