关于位图索引使用问题

张沛

做了个小实验在测试库上10g，olap数据库，有两张表mmt，mtln数据量都在100万行左右，其中都有一列organization_id列，其中mmt中

1. SQL> select organization_id from mtl_material_transactions group by organization_id;
   
2. 
   
3. ORGANIZATION_ID
   
4. ---------------
   
5.             105
   
6.             106
   
7.             107
   
8.             137
   

复制代码

mtln中

1. SQL> select organization_id from mtl_transaction_lot_numbers group by organization_id;
   
2. 
   
3. ORGANIZATION_ID
   
4. ---------------
   
5.             106
   
6.             107
   

复制代码

有一个查询

1. select mmt.organization_id,
   
2.        mmt.subinventory_code,
   
3.        nvl(mtln.transaction_quantity, mmt.primary_quantity) transaction_quantity,
   
4.        mmt.TRANSACTION_ACTION_ID,
   
5.        mmt.INVENTORY_ITEM_ID,
   
6.        mmt.transaction_type_id,
   
7.        mtln.lot_number,
   
8.        mmt.transaction_id
   
9.   from mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln
   
10. where mmt.transaction_id = mtln.transaction_id(+)
    
11.    and mmt.organization_id = mtln.organization_id(+)
    
12.    and mmt.organization_id = 106

复制代码

我在organization_id 列上添加了位图索引，transaction_id 和organization_id 上添加了符合索引，为什么执行计划依然走的全表扫面（全外连接会影响但是我加了条organization_id = 106想去限制它，让执行计划自行去找索引）为什么没有走索引呢？
注：没添加索引前大约查询20多秒，添加索引后执行计划没变但是变成7秒了，测试库没其他操作影响，这又是为什么？

1. 
   
2. 1         
   
3. 2        --------------------------------------------------------------------------------------------------
   
4. 3        | Id  | Operation             | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)|
   
5. 4        --------------------------------------------------------------------------------------------------
   
6. 5        |   0 | SELECT STATEMENT      |                             |   983K|    73M|       | 71889   (1)|
   
7. 6        |*  1 |  HASH JOIN RIGHT OUTER|                             |   983K|    73M|    25M| 71889   (1)|
   
8. 7        |*  2 |   TABLE ACCESS FULL   | MTL_TRANSACTION_LOT_NUMBERS |   594K|    18M|       | 10365   (2)|
   
9. 8        |*  3 |   TABLE ACCESS FULL   | MTL_MATERIAL_TRANSACTIONS   |   983K|    42M|       | 57580   (1)|
   
10. 9        --------------------------------------------------------------------------------------------------
    
11. 10         
    
12. 11        Predicate Information (identified by operation id):
    
13. 12        ---------------------------------------------------
    
14. 13         
    
15. 14           1 - access("MMT"."TRANSACTION_ID"="MTLN"."TRANSACTION_ID"(+) AND
    
16. 15                      "MMT"."ORGANIZATION_ID"="MTLN"."ORGANIZATION_ID"(+))
    
17. 16           2 - filter("MTLN"."ORGANIZATION_ID"(+)=106)
    
18. 17           3 - filter("MMT"."ORGANIZATION_ID"=106)
    
19. 18         
    

复制代码

Minsic

粗略看来，这个HASH JOIN的计划是合理的，除非organization_id=106的数据在两张表里都非常少，那么会在驱动表使用index range scan，再通过被驱动表上的索引来走嵌套循环。
至于第二次跑只需要7秒，因为前一次SQL执行后，数据块已经进入了buffer cache。

张沛

Minsic 发表于 2013-2-6 13:35
粗略看来，这个HASH JOIN的计划是合理的，除非organization_id=106的数据在两张表里都非常少，那么会在驱动 ...

感谢您的回复，我懂一些了，我之前认为如果类似org_id这样只包含大概106,107这样很少不同值的列，适合位图索引，但是最近做了些实验发现也不全是，还想麻烦问您下，想类似我语句中有这种外连接有没有好的方法改善下（类似这个查询，我直接mmt.organization_id in（105,106,107,137）这样会不会好？

Minsic

张沛 发表于 2013-2-6 14:21
感谢您的回复，我懂一些了，我之前认为如果类似org_id这样只包含大概106,107这样很少不同值的列，适合位 ...

如果不考虑表上的并发DML，位图索引一般会比B树索引带来更好的性能，但无论哪种索引，如果只考虑单条索引，那么能否使用到索引，使用该索引的性能如何，还是要看该字段的选择性强弱与否——某个值可以从表中过滤掉的无关数据越多，选择性越强，那么使用索引的效率也越高。
我个人觉得，如果这些id的唯一值就你主题贴列出的这些，它们的数据分布是均匀的，且，针对这两张表的频繁查询就是你主题贴列出的这条，那么可以考虑将这两张表按照organization_id字段进行list分区，可以使你的查询有一定的性能提升，但根据你的需求，想要带来戏剧性的提升（比如突然提升到毫秒级）看来没有希望了 :)

Minsic

对了，如果说这两张表的字段都比较多，那么创建覆盖索引（在某张表上将该查询所有引用到的字段全部建入索引）可以有较大的性能提升，尤其是引用字段较少的mtl_transaction_lot_numbers表。

张沛

Minsic 发表于 2013-2-7 22:15
对了，如果说这两张表的字段都比较多，那么创建覆盖索引（在某张表上将该查询所有引用到的字段全部建入索引 ...

太感谢您了，我去看看关于覆盖索引的内容，学习到了新的知识，谢谢