optimizer_index_cost_adj对索引的影响

曾经遇到过视图查询的问题：按索引字段NUMBATCHID 查询，但却不全走索引！

这个视图其实是对两个表BZ和PUSH的联合查询。虽然BZ/PUSH两个表上都有对 NUMBATCHID 字段的索引，但不知为何只在PUSH上走索引，BZ表上不走索引，而走FTS!!!

当时估计是由于BZ表 NUMBATCHID 字段的分布太不平均，导致优化器估计错误。

今天想到了optimizer_index_cost_adj参数，于是在session中修改 optimizer_index_cost_adj ，从缺省值100修改为 30；然后重新查询，果然就全走索引了。

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2232K| 74M| 54620 | | |
| 1 | SORT GROUP BY | | 2232K| 74M| 54620 | | |
| 2 | VIEW | V_RECORDGROUP | 2232K| 74M| 47179 | | |
| 3 | UNION-ALL | | | | | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| SMS_MTRECORDPUSH | 18038 | 228K| 1020 | ROWID | ROW L |
|* 5 | INDEX RANGE SCAN | I_PUSH_BATCHID | 18038 | | 129 | | |
| 6 | PARTITION LIST ALL | | | | | 1 | 13 |
|* 7 | TABLE ACCESS FULL | T_RECORDBZ | 2214K| 14M| 46159 | 1 | 13 |
-------------------------------------------------------------------------------------------------------------

TEST@ORA9I> alter session set optimizer_index_cost_adj = 10;

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2232K| 74M| 21796 (0)| | |
| 1 | SORT GROUP BY | | 2232K| 74M| 21796 (0)| | |
| 2 | VIEW | V_RECORDGROUP | 2232K| 74M| | | |
| 3 | UNION-ALL | | | | | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_RECORDPUSH | 18038 | 228K| 103 (1)| ROWID | ROW L |
|* 5 | INDEX RANGE SCAN | I_PUSH_BATCHID | 18038 | | 129 (0)| | |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_RECORDBZ | 2214K| 14M| 14254 (0)| ROWID | ROW L |
|* 7 | INDEX RANGE SCAN | I_BZ_BATCHID | 2214K| | 14894 (0)| | |
------------------------------------------------------------------------------------------------------------------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9844649/viewspace-580065/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9844649/viewspace-580065/