push_subq

March 18th, 2010 | Categories: Boring | Tags: ,
Leave a comment | Trackback

push_subq可以让SQL中的子查询先和外面的某个表作Join，目的是返回更少的中间结果集，从而再和其他的表join返回。

可惜10g中OUTLINE不支持push_subq , 有bug。

Bug 7281240

REPRODUCIBILITY:—————-

Generic reproduces on 10.2.0.3, 10.2.0.4, 11.1.0.6.
On 11.1.0.6 the testcase has a second difference: the stored outline has
hint PUSH_SUBQ(@”SEL$2″) which is not used in the generated cursor

10g中还可以有qb_name() push_subq(@subq1) 的组合，更加灵活。 For reference in future.

select  /*+ push_subq(@subq1) */
par.small_vc1,
chi.small_vc1
from
parent    par,
child    chi
where
par.id1 between 1001 and 2200
and    chi.id1 = par.id1
and    exists (
select
/*+ qb_name(subq1) */
null
from    subtest    sub1
where
sub1.id1       = par.id1
and    sub1.small_vc1 = par.small_vc1
and    sub1.small_vc2 >= ‘2′
)
;

Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
  1. jametong
    March 19th, 2010 at 09:33
    Quote | #1

    兄弟,,这个说明太简单了,,怎么也贴出个执行计划啊..^_^