oracle hint push_pred,提示用法：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>