select t1.compname,t.*
fromcode_pur_comp t1,ods_mm_matpurorder t
where t.compcode=t1.compcode
and T.YEAR=2011 ANDT.MONTH=10
AND CASE WHEN T.DELETECODE='L' THEN 1 ELSE 0 END=0
and t.bidplancode in (select distinct bidplancode from ods_mm_bidresult t2 where t2.bidplancode'201011')
AND trim(T.ORDERSTATE)='已审批'
and trim(t.vendorcode) is not null
and not exists(select b.supplier_code from ods_mm_dim_vendorinfo b where b.supplier_code=trim(t.vendorcode));
ods_mm_matpurorder表数据超过2000万， ods_mm_bidresult：7万，code_pur_comp：33条
where条件过滤后ods_mm_matpurorder表为60万
下面是执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 152620310
--------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CP
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 410 | 281K(
|*1 |HASH JOIN ANTI|
| 1 | 410 | 281K(
|*2 | HASH JOIN SEMI |
| 1 | 399 | 281K(
|*3 |HASH JOIN |
| 1 | 393 | 280K(
|*4 | TABLE ACCESS FULL| ODS_MM_MATPURORDER| 1 | 367 | 280K(
| 5 | TABLE ACCESS FULL| CODE_PUR_COMP |33 | 858 | 8 (
|*6 |TABLE ACCESS FULL | ODS_MM_BIDRESULT| 67468 | 395K| 578 (
| 7 | TABLE ACCESS FULL| ODS_MM_DIM_VENDORINFO | 86210 | 926K| 294 (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."SUPPLIER_CODE"=TRIM("T"."VENDORCODE"))
2 - access("T"."BIDPLANCODE"="BIDPLANCODE")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
3 - access("T"."COMPCODE"="T1"."COMPCODE")
4 - filter("T"."MONTH"=10 AND "T"."YEAR"=2011 AND TRIM("T"."VENDORCODE") IS N
NULL AND CASE "T"."DELETECODE" WHEN 'L' THEN 1 ELSE 0 END =0 AND
TRIM("T"."ORDERSTATE")='已审批' AND "T"."BIDPLANCODE"'201011')
6 - filter("T2"."BIDPLANCODE"'201011')
本帖最后由 bell6248 于 2011-11-8 13:35 编辑
你的表都已经经过分析了吗？
另外表ods_mm_dim_vendorinfo数据量有多大， 还有4表的关系是如何的？
LZ，你先把4个表都分析一下，然后测试一下如下的语句， 如果可能的话，看看是否可以改下！
select /*+ leading(t1) use_hash(t1 t)*/ t1.compname, t.*
from code_pur_comp t1,
ods_mm_matpurorder t
where t.compcode = t1.compcode
and T.YEAR = 2011
AND T.MONTH = 10
and decode(T.DELETECODE, 'L', 1, 0) = 0
and trim(T.ORDERSTATE) = '已审批'
and trim(t.vendorcode) is not null
and not exists(select 1 from ods_mm_dim_vendorinfo b where b.supplier_code = trim(t.vendorcode))
and exists(select 1 from ods_mm_bidresult t2 where t2.bidplancode = t.bidplancode and t2.bidplancode'201011');
