下面这条sql的consistent gets为70302，麻烦大神看一下有没有优化的方案。
SELECT m.stdcode “单位代码”, m.stdname “单位名称”,card.billcode “卡片编号”
from md_org m
left join gams_assetcard card
on m.recid = card.orgunit
left join (select a.objectid, a.cardstate
from gams_assetcard a
left join (select cc.objectid, max(cc.yewxlh) yewxlh
from gams_assetcard cc
where cc.jizrq < to_date(‘2020’,‘yyyy’)
group by cc.objectid) dd
on a.objectid = dd.objectid
where a.yewxlh = dd.yewxlh) temp
on temp.objectid = card.objectid
left join (select cc.objectid, min(cc.yewxlh) yewxlh
from gams_assetcard cc
where cc.auditstate = 2
and cc.caiwrzrq is not null
group by cc.objectid) tempYe
on tempYe.objectid = card.objectid
where card.yewxlh = tempYe.yewxlh
and to_char(card.jizrq, ‘yyyy’) = ‘2019’
and (temp.cardstate in (‘00’,‘01’,‘02’,‘03’,‘10’, ‘14’, ‘15’, ‘16’))
and card.QUDFSID = hextoraw(‘36B91A2620000021843A413C3B72743E’)
and not exists (select 1
from GAMS_JC_REFORM_ORGMAPPING rrr
where rrr.orgunit = card.orgunit
and rrr.recid = card.LAIYDJID)
and not exists (select 1
from tj_isjghz temp
where temp.orgunit = card.orgunit
and temp.cardobjectid = card.objectid)
ORDER BY m.stdcode
执行计划

Plan hash value: 1326741783

| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 496| 27120 (1)| 00:05:26 |

| 1 | SORT ORDER BY | | 1 | 496| 27120 (1)| 00:05:26 |

| 2 | NESTED LOOPS | | 1 | 496| 27119 (1)| 00:05:26 |

| 3 | NESTED LOOPS | | 1 | 483| 27114 (1)| 00:05:26 |

| 4 | NESTED LOOPS | | 1 | 470| 27109 (1)| 00:05:26 |

| 5 | NESTED LOOPS ANTI | | 1 | 445| 27107 (1)| 00:05:26 |

| 6 | NESTED LOOPS ANTI | | 1 | 425| 27107 (1)| 00:05:26 |

| 7 | NESTED LOOPS | | 15 | 6075| 27107 (1)| 00:05:26 |

| 8 | TABLE ACCESS FULL | MD_ORG | 986 | 302K| 35 (0)| 00:00:01 |

|* 9 | TABLE ACCESS BY INDEX ROWID| GAMS_ASSETCARD | 1 | 91| 36 (0)| 00:00:01 |

|* 10 | INDEX RANGE SCAN | 6BDB3DB4BA4EF327 | 39 || 23 (0)| 00:00:01 |

|* 11 | INDEX RANGE SCAN | 45E17636B83C87F4 | 1159 | 23180| 0 (0)| 00:00:01 |

|* 12 | INDEX RANGE SCAN | 80FA2A1CCBF2764F | 1 | 20| 0 (0)| 00:00:01 |

|* 13 | INDEX RANGE SCAN | 4C7025CAF550ABF2_1 | 1 | 25| 2 (0)| 00:00:01 |

|* 14 | VIEW PUSHED PREDICATE | | 1 | 13| 5 (0)| 00:00:01 |

| 15 | SORT AGGREGATE | | 1 | 31| | |

|* 16 | TABLE ACCESS BY INDEX ROWID | GAMS_ASSETCARD | 1 | 31
| 5 (0)| 00:00:01 |

|* 17 | INDEX RANGE SCAN | 4C7025CAF550ABF2_1 | 1 || 3 (0)| 00:00:01 |

|* 18 | VIEW PUSHED PREDICATE | | 1 | 13| 5 (0)| 00:00:01 |

| 19 | SORT AGGREGATE | | 1 | 34| | |

|* 20 | TABLE ACCESS BY INDEX ROWID | GAMS_ASSETCARD | 1 | 34| 5 (0)| 00:00:01 |

|* 21 | INDEX RANGE SCAN | 4C7025CAF550ABF2_1 | 1 || 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

9 - filter(“CARD”.“QUDFSID”=HEXTORAW(‘36B91A2620000021843A413C3B72743E’) )
10 - access(“M”.“RECID”=“CARD”.“ORGUNIT”)
filter(TO_CHAR(INTERNAL_FUNCTION(“CARD”.“JIZRQ”),‘yyyy’)=‘2019’)
11 - access(“TEMP”.“ORGUNIT”=“CARD”.“ORGUNIT” AND “TEMP”.“CARDOBJECTID”=“CARD”.“OBJECTID”)

12 - access(“RRR”.“ORGUNIT”=“CARD”.“ORGUNIT” AND “RRR”.“RECID”=“CARD”.“LAIYDJID”)

13 - access(“A”.“OBJECTID”=“CARD”.“OBJECTID”)
filter(“A”.“CARDSTATE”=U’00’ OR “A”.“CARDSTATE”=U’01’ OR “A”.“CARDSTATE”=U’02’ OR
“A”.“CARDSTATE”=U’03’ OR “A”.“CARDSTATE”=U’10’ OR “A”.“CARDSTATE”=U’14’ OR “A”.“CARDSTATE”=U’15’
OR “A”.“CARDSTATE”=U’16’)
14 - filter(“A”.“YEWXLH”=“DD”.“YEWXLH”)
16 - filter(“CC”.“JIZRQ”<TO_DATE(‘2020’,‘yyyy’))
17 - access(“CC”.“OBJECTID”=“A”.“OBJECTID”)
18 - filter(“CARD”.“YEWXLH”=“TEMPYE”.“YEWXLH”)
20 - filter(“CC”.“CAIWRZRQ” IS NOT NULL)
21 - access(“CC”.“OBJECTID”=“CARD”.“OBJECTID” AND “CC”.“AUDITSTATE”=2)
filter(“CC”.“AUDITSTATE”=2)

Note

• dynamic sampling used for this statement (level=2)

统计信息

     25  recursive calls
      0  db block gets
  70302  consistent gets
      0  physical reads
      0  redo size
  50831  bytes sent via SQL*Net to client
   1377  bytes received via SQL*Net from client
     80  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
   1172  rows processed
复制

SQL>