奇怪的索引

Oracle视图关联查询索引使用问题

最新推荐文章于 2025-12-04 22:03:39 发布

原创最新推荐文章于 2025-12-04 22:03:39 发布 · 620 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #access #loops #oracle #nested

博主使用Oracle 9i，一个由十个表union组成的视图，单独查询时能使用a字段索引，但与其他表关联查询时不使用索引而遍历全量数据。博主希望找到让Oracle在关联查询时使用a字段索引的方法。

　　今天遇到一种很奇怪的情况。使用的是oracle9i

有一个view，是十个表的union，eg.

create or replace view view_name as
select * from table_name_0
union
select * from table_name_1
union
.
.
.
union
select * from table_name_9

其中每个表都有对a字段的索引，当使用

select * from view_name where a='123'时，使用explain plan，能看到使用了a字段的索引( IX_Table_name_0, IX_Table_name_1... IX_Table_name_9)。

explain plan
SELECT STATEMENT, GOAL = CHOOSE
VIEW view_name
SORT UNIQUE
   UNION-ALL
    TABLE ACCESS BY INDEX ROWID Table_name_0
     INDEX RANGE SCAN  IX_Table_name_0
    TABLE ACCESS BY INDEX ROWID Table_name_1
     INDEX RANGE SCAN  IX_Table_name_1
    TABLE ACCESS BY INDEX ROWID Table_name_2
     INDEX RANGE SCAN  IX_Table_name_2
    TABLE ACCESS BY INDEX ROWID Table_name_3
     INDEX RANGE SCAN  IX_Table_name_3
    TABLE ACCESS BY INDEX ROWID Table_name_4
     INDEX RANGE SCAN  IX_Table_name_4
    TABLE ACCESS BY INDEX ROWID Table_name_5
     INDEX RANGE SCAN  IX_Table_name_5
    TABLE ACCESS BY INDEX ROWID Table_name_6
     INDEX RANGE SCAN  IX_Table_name_6
    TABLE ACCESS BY INDEX ROWID Table_name_7
     INDEX RANGE SCAN  IX_Table_name_7
    TABLE ACCESS BY INDEX ROWID Table_name_8
     INDEX RANGE SCAN  IX_Table_name_8
    TABLE ACCESS BY INDEX ROWID Table_name_9
     INDEX RANGE SCAN  IX_Table_name_9

可是当写其他表关联查询时，则不会使用索引而遍历十个表。eg.
select * from view_name, table_other where table_other.id = '20' and view_name.a = table_other.a;

SELECT STATEMENT, GOAL = CHOOSE
NESTED LOOPS
VIEW view_name
   SORT UNIQUE
    UNION-ALL
     TABLE ACCESS FULL Table_name_0
     TABLE ACCESS FULL Table_name_1
     TABLE ACCESS FULL Table_name_2
     TABLE ACCESS FULL Table_name_3
     TABLE ACCESS FULL Table_name_4
     TABLE ACCESS FULL Table_name_5
     TABLE ACCESS FULL Table_name_6
     TABLE ACCESS FULL Table_name_7
     TABLE ACCESS FULL Table_name_8
     TABLE ACCESS FULL Table_name_9
TABLE ACCESS BY INDEX ROWID table_other
   INDEX UNIQUE SCAN table_other