oracle表连接 where条件,多表连接时如何强制Oracle优先进行where条件过滤？

本帖最后由 hxpwork 于 2012-7-18 16:46 编辑

今天在客户处进行调优，遇到了下面的一条SQL

SELECT "T0"."FID"          "ID",

...

"T0"."FRESTYPEID"   "T1.ID",

"T1"."FNAME_L2"     "T1.NAME",

...

"T1"."FNAME_L3"     "T1.NAME_L3",

"T2"."FNAME_L2"     "T2.NAME",

...

"T2"."FNUMBER"      "T2.NUMBER",

"T2"."FRESOURCEID"  "T3.ID",

"T3"."FTYPE"        "T3.TYPE",

"T0"."FUNITID"      "T4.ID",

"T4"."FNAME_L2"     "T4.NAME",

"...

"T4"."FNUMBER"      "T4.NUMBER",

"T0"."FPARENTID"    "T5.ID",

FROM "T_EC_BASEWBSRESENTRY" "T0"

LEFT OUTER JOIN "T_EC_RESOURCE" "T1"

ON "T0"."FRESTYPEID" = "T1"."FID"

LEFT OUTER JOIN "T_EC_RESOURCEITEM" "T2"

ON "T0"."FRESITEMID" = "T2"."FID"

LEFT OUTER JOIN "T_EC_RESOURCE" "T3"

ON "T2"."FRESOURCEID" = "T3"."FID"

LEFT OUTER JOIN "T_BD_MEASUREUNIT" "T4"

ON "T0"."FUNITID" = "T4"."FID"

WHERE "T0"."FPARENTID" IN

(SELECT pa.fid

FROM t_ec_basewbs CH

INNER JOIN t_ec_basewbs PA

ON INSTR((ch.flongnumber || '!'), (pa.flongnumber || '!')) = 1

WHERE (ch.fprojectid = 'BZcTC5sFRnmHpfl29LofTMgrxyQ=' AND

((ch.fid IN ('YCSlu88JTZWT7cfV9L+IGMbIBoI=',

/*省略*/...

'Y1wU8y1zQDePXMUqKohD8 cbIBoI=')) AND

pa.fprojectid = 'BZcTC5sFRnmHpfl29LofTMgrxyQ=')))

执行时间145秒，执行计划如下：

描述                                  对象所有者               对象名称             耗费  基数  字节

SELECT STATEMENT, GOAL = ALL_ROWS                                    51271    6    18372   

NESTED LOOPS OUTER                                                              51271    6    18372

NESTED LOOPS                                                                        51268    6    17352

VIEW    SYS    VW_NSO_1                                                         17    1    24

HASH UNIQUE                                                                            1    234

TABLE ACCESS BY INDEX ROWID    GDCD    T_EC_BASEWBS    8    1    117

NESTED LOOPS                                                                        17    1    234

TABLE ACCESS BY INDEX ROWID    GDCD    T_EC_BASEWBS    10    1    117

INDEX RANGE SCAN    GDCD    IX_EC_WBSPROJECT                   2    129

INDEX RANGE SCAN    GDCD    IX_EC_WBSPROJECT                        1    129

VIEW    SYS                                                                                        51250    6    17208

HASH JOIN RIGHT OUTER                                                                  51250    200296    576451888

TABLE ACCESS FULL    GDCD    T_EC_RESOURCE                              42    2941    94112

VIEW    SYS                                                                                      51202    200296    570042416

HASH JOIN OUTER                                                                          51202    200296    401593480

VIEW    SYS                                                                                    1187    199156    308293488

HASH JOIN RIGHT OUTER                                                              1187    199156    115510480

TABLE ACCESS FULL    GDCD    T_EC_RESOURCE                           42    2941    1199928

TABLE ACCESS FULL    GDCD    T_EC_BASEWBSRESENTRY            1139    199156    34254832

TABLE ACCESS FULL    GDCD    T_EC_RESOURCEITEM                     13933    957698    437667986

TABLE ACCESS BY INDEX ROWID    GDCD    T_BD_MEASUREUNIT         1    1    170

INDEX UNIQUE SCAN    GDCD    PK_MEASUREUNIT                               1    1

如果将上面的语句改成下面形式

SELECT

"T0"."FID"          "ID",

"T0"."FSEQ"         "SEQ",

"T0"."FRESTYPEID"   "T1.ID",

"T1"."FNAME_L2"     "T1.NAME",

"T1"."FNAME_L1"     "T1.NAME_L1",

"T1"."FNAME_L2"     "T1.NAME_L2",

"T1"."FNAME_L3"     "T1.NAME_L3",

"T0"."FRESITEMID"   "T2.ID",

"T2"."FNAME_L2"     "T2.NAME",

"T2"."FNAME_L1"     "T2.NAME_L1",

"T2"."FNAME_L2"     "T2.NAME_L2",

"T2"."FNAME_L3"     "T2.NAME_L3",

"T2"."FNUMBER"      "T2.NUMBER",

"T2"."FRESOURCEID"  "T3.ID",

"T3"."FTYPE"        "T3.TYPE",

"T0"."FUNITID"      "T4.ID",

"T4"."FNAME_L2"     "T4.NAME",

"T4"."FNAME_L1"     "T4.NAME_L1",

"T4"."FNAME_L2"     "T4.NAME_L2",

"T4"."FNAME_L3"     "T4.NAME_L3",

"T4"."FNUMBER"      "T4.NUMBER",

"T0"."FPRICE"       "PRICE",

"T0"."FQTY"         "QTY",

"T0"."FAMOUNT"      "AMOUNT",

"T0"."FCOSTQTY"     "COSTQTY",

"T0"."FCOSTAMOUNT"  "COSTAMOUNT",

"T0"."FISPAYITEM"   "ISPAYITEM",

"T0"."FPARENTID"    "T5.ID",

"T0"."FDESCRIPTION" "DESCRIPTION",

"T0"."FINITQTY"     "INITQTY",

"T0"."FCHANGEQTY"   "CHANGEQTY",

"T0"."FUNITQTY"     "UNITQTY"

FROM

(select * from T_EC_BASEWBSRESENTRY where FPARENTID in (SELECT pa.fid

FROM t_ec_basewbs CH

INNER JOIN t_ec_basewbs PA

ON INSTR((ch.flongnumber || '!'), (pa.flongnumber || '!')) = 1

WHERE (ch.fprojectid = 'BZcTC5sFRnmHpfl29LofTMgrxyQ=' AND

((ch.fid IN ('YCSlu88JTZWT7cfV9L+IGMbIBoI=',

/*省略*/ ...

'Y1wU8y1zQDePXMUqKohD8 cbIBoI=')) AND

pa.fprojectid = 'BZcTC5sFRnmHpfl29LofTMgrxyQ='))))

"T0"

LEFT OUTER JOIN "T_EC_RESOURCE" "T1"

ON "T0"."FRESTYPEID" = "T1"."FID"

LEFT OUTER JOIN "T_EC_RESOURCEITEM" "T2"

ON "T0"."FRESITEMID" = "T2"."FID"

LEFT OUTER JOIN "T_EC_RESOURCE" "T3"

ON "T2"."FRESOURCEID" = "T3"."FID"

LEFT OUTER JOIN "T_BD_MEASUREUNIT" "T4"

ON "T0"."FUNITID" = "T4"."FID"

执行时间0.5秒，执行计划如下：

描述                                  对象所有者               对象名称             耗费  基数  字节

SELECT STATEMENT, GOAL = ALL_ROWS                                     33    6   18258

SORT ORDER BY                                                                         33    6    18258

NESTED LOOPS OUTER                                                              32    6    18258

VIEW    SYS                                                                              29    6    17238

NESTED LOOPS OUTER                                                            29    6    17268

VIEW    SYS                                                                           26    6    17076

NESTED LOOPS OUTER                                                          26    6    12030

VIEW    SYS                                                                          23    6    9288

NESTED LOOPS OUTER                                                       23    6    3624

NESTED LOOPS                                                                 20    6    1176

VIEW    SYS    VW_NSO_1                                                 17    1    24

HASH UNIQUE                                                                  1    234

TABLE ACCESS BY INDEX ROWID    GDCD    T_EC_BASEWBS    8    1    117

NESTED LOOPS                                                                        17    1    234

TABLE ACCESS BY INDEX ROWID    GDCD    T_EC_BASEWBS    10    1    117

INDEX RANGE SCAN    GDCD    IX_EC_WBSPROJECT                2    129

INDEX RANGE SCAN    GDCD    IX_EC_WBSPROJECT                 1    129

TABLE ACCESS BY INDEX ROWID    GDCD    T_EC_BASEWBSRESENTRY    2    6    1032

INDEX RANGE SCAN    GDCD    IX_EC_WBSRESENTRY               1    6

TABLE ACCESS BY INDEX ROWID    GDCD    T_EC_RESOURCE    1    1    408

INDEX UNIQUE SCAN    GDCD    PK_EC_RESOURCE                     1    1

TABLE ACCESS BY INDEX ROWID    GDCD    T_EC_RESOURCEITEM    1    1    457

INDEX UNIQUE SCAN    GDCD    PK_EC_RESITEM                        1    1

TABLE ACCESS BY INDEX ROWID    GDCD    T_EC_RESOURCE    1    1    32

INDEX UNIQUE SCAN    GDCD    PK_EC_RESOURCE                     1    1

TABLE ACCESS BY INDEX ROWID    GDCD    T_BD_MEASUREUNIT    1    1    170

INDEX UNIQUE SCAN    GDCD    PK_MEASUREUNIT                       1    1

显然Oracle认为where条件中的IN语句可能会返回很多记录，因此优先选择了全表检索并连接后再使用where条件过滤结果集；而实际上IN子句并不会返回多少条结果；现在的问题是如何在不改变语句的前提下，让Oracle优先进行where条件的处理？

说明一下，我已经尝试了INDEX hint，没有效果。