提示用法：push_pred

提示用法：push_pred
使用条件：基表为临时表，左连接一个基本两张大表的UNION ALL视图，关联的字段有索引，oracle默认不会使用索引。但使用push_pred提示就能实现用索引访问，初步分析有两个原因：
1、oracle不清楚临时表中的记录数目。
2、视图为UNION视图，oracle对此类视图的索引支持也不是太好。

[@more@]

提示用法：push_pred
使用条件：基表为临时表，左连接一个基本两张大表的UNION ALL视图，关联的字段有索引，oracle默认不会使用索引。但使用push_pred提示就能实现用索引访问，初步分析有两个原因：
1、oracle不清楚临时表中的记录数目。
2、视图为UNION视图，oracle对此类视图的索引支持也不是太好。

具体实例如下：
create table big_table1 as select * from dba_objects;
create table big_table2 as select * from dba_objects;
create global temporary table zt$test (fid number,fno varchar2(10));
create index ix_1 on big_table1(object_id);
create index ix_2 on big_table2(object_id);

create or replace view v_big_table as
select owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary
from big_table1
union all
select owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary
from big_table2;

1、不使用任何提示
SQL> select v.* from zt$test z, v_big_table v where z.fid = v.object_id(+);

未选定行

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN (OUTER)
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'ZT$TEST'
4 1 SORT (JOIN)
5 4 VIEW OF 'V_BIG_TABLE'
6 5 UNION-ALL
7 6 TABLE ACCESS (FULL) OF 'BIG_TABLE1'
8 6 TABLE ACCESS (FULL) OF 'BIG_TABLE2'

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
915 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>

2、使用push_pred提示
SQL> select /*+ push_pred(v)*/ v.* from zt$test z, v_big_table v where z.fid = v.object_id(+);

未选定行

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16347 Card=522593 By
tes=99292670)

1 0 NESTED LOOPS (OUTER) (Cost=16347 Card=522593 Bytes=9929267
0)

2 1 TABLE ACCESS (FULL) OF 'ZT$TEST' (Cost=11 Card=8168 Byte
s=106184)

3 1 VIEW OF 'V_BIG_TABLE'
4 3 UNION-ALL (PARTITION)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE1' (Cost=
2 Card=329 Bytes=58233)

6 5 INDEX (RANGE SCAN) OF 'IX_1' (NON-UNIQUE) (Cost=1
Card=132)

7 4 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE2' (Cost=
2 Card=329 Bytes=58233)

8 7 INDEX (RANGE SCAN) OF 'IX_2' (NON-UNIQUE) (Cost=1
Card=132)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
915 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>

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

转载于:http://blog.itpub.net/10455206/viewspace-1029035/