添加索引还是换分区表

张沛

数据库10.2.0.3.0  olap
两种表mtl_material_transactions   mmt（约150列，485万行数据）,mtl_transaction_lot_numbers mtln（约150列，150万行数据）
一条select中只包含了两张表的外连接来获取所需信息

1. select mmt.organization_id,
   
2.        mmt.primary_quantity,
   
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(+)
    

复制代码

备注信息：

1. select organization_id
   
2. from mtl_material_transactions
   
3. group by organization_id

复制代码

organization_id
1        137
2        105
3        106
4        107
1        137
2        105
3        106
4        107

1. select organization_id
   
2. from mtl_transaction_lot_numbers
   
3. group by organization_id

复制代码

organization_id
1        106
2        107
两张表transaction_id 包含不同值信息较多，两张表上transaction_id 列和organization_id有复合索引无其他索引
问题：对于这样一条语句，是该拆表，还是改分区表（根据organization_id）还是在organization_id列上添加位图索引

shunzi

把执行计划贴出来，试试先创建索引，能不能解决问题，你现在这种数据量，做分区表以后维护会比较麻烦，拆表也不置于。先试试创建索引吧。

张沛

shunzi 发表于 2013-1-30 10:29
把执行计划贴出来，试试先创建索引，能不能解决问题，你现在这种数据量，做分区表以后维护会比较麻烦，拆表 ...

1. 
   
2. 1         
   
3. 2        --------------------------------------------------------------------------------------------------
   
4. 3        | Id  | Operation             | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)|
   
5. 4        --------------------------------------------------------------------------------------------------
   
6. 5        |   0 | SELECT STATEMENT      |                             |  4846K|   254M|       | 95757   (1)|
   
7. 6        |*  1 |  HASH JOIN RIGHT OUTER|                             |  4846K|   254M|    58M| 95757   (1)|
   
8. 7        |   2 |   TABLE ACCESS FULL   | MTL_TRANSACTION_LOT_NUMBERS |  1498K|    41M|       | 12987   (1)|
   
9. 8        |   3 |   TABLE ACCESS FULL   | MTL_MATERIAL_TRANSACTIONS   |  4846K|   120M|       | 71034   (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         
    
18. 17        Note
    
19. 18        -----
    
20. 19           - 'PLAN_TABLE' is old version

复制代码

就是说试着在org_id列添加位图索引试试呗

cargoo

请删除PLAN_TABLE，重新生成执行计划

张沛

cargoo 发表于 2013-1-30 14:05
请删除PLAN_TABLE，重新生成执行计划

恩...不太明白，什么意思？

cargoo

1         
2        --------------------------------------------------------------------------------------------------
3        | Id  | Operation             | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)|
4        --------------------------------------------------------------------------------------------------
5        |   0 | SELECT STATEMENT      |                             |  4846K|   254M|       | 95757   (1)|
6        |*  1 |  HASH JOIN RIGHT OUTER|                             |  4846K|   254M|    58M| 95757   (1)|
7        |   2 |   TABLE ACCESS FULL   | MTL_TRANSACTION_LOT_NUMBERS |  1498K|    41M|       | 12987   (1)|
8        |   3 |   TABLE ACCESS FULL   | MTL_MATERIAL_TRANSACTIONS   |  4846K|   120M|       | 71034   (1)|
9        --------------------------------------------------------------------------------------------------
10         
11        Predicate Information (identified by operation id):
12        ---------------------------------------------------
13         
14           1 - access("MMT"."TRANSACTION_ID"="MTLN"."TRANSACTION_ID"(+) AND
15                      "MMT"."ORGANIZATION_ID"="MTLN"."ORGANIZATION_ID"(+))
16         
17        Note
18        -----
19           - 'PLAN_TABLE' is old version

这个PLAN_TABLE版本不是当前数据库版本

张沛

cargoo 发表于 2013-1-30 14:59
1         
2        ------------------------------------------------------------------------------- ...

您不说真没注意，但是drop table plan_table purge;
能在生产库直接用么...没用过

laobu

可以这样考虑：
哈希连接的实际逻辑读，如果远大于两张表的总块数，显然是在连接过程中multipath的开销过高
如果依据关联字段做了分区，哪怕只对其中一张表分区，也可以通过所谓的智能分区连接，来大幅度减少逻辑读
根据不完全测试，两张表分别200万、50万记录，都按hash(transaction_id)分4个区，实际逻辑读降了一个数量级还多些
另外，就本例而言，索引似乎没帮助

张沛

laobu 发表于 2013-1-30 16:44
可以这样考虑：
哈希连接的实际逻辑读，如果远大于两张表的总块数，显然是在连接过程中multipath的开销过高 ...

感谢感谢，我感觉也是，但是数据量不是很大所以就不太想搞在线重定义去换分区表，看看开发能不能改善下语句，谢谢了

piii866

ebs的开发大多不考虑效率问题，能实现就结束。。。。
兰州要抓紧时间鞭策他们