如何SQL以避免288核1.5T内存数据库崩溃？

最近遇到一个Oracle的SQL语句引发的线上故障，数据库服务被重启。主机288核，1.5T也没抗住。 select count(*) from dev_db.t1 t1,dev_db.t2 t2 where t1.object_name = t2.object_name or t1.DATA_OBJECT_ID = t2.DATA_OBJECT_ID and t1.OBJECT_ID = 123 and rownum = 1; -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 77 | 31M (1)| 00:20:50 | | 1 | SORT AGGREGATE | | 1 | 77 | | | | 2 | COUNT | | | | | | |* 3 | FILTER | | | | | | | 4 | MERGE JOIN CARTESIAN| | 6131M| 439G| 31M (1)| 00:20:50 | | 5 | TABLE ACCESS FULL | T1 | 78306 | 3135K| 411 (1)| 00:00:01 | | 6 | BUFFER SORT | | 78307 | 2752K| 31M (1)| 00:20:50 | | 7 | TABLE ACCESS FULL | T2 | 78307 | 2752K| 409 (1)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."OBJECT_NAME"="T2"."OBJECT_NAME" OR "T1"."DATA_OBJECT_ID"="T2"."DATA_OBJECT_ID" AND "T2"."DATA_OBJECT_ID" IS NOT NULL AND "T1"."DATA_OBJECT_ID" IS NOT NULL AND "T1"."OBJECT_ID"=123 AND ROWNUM=1) 在执行计划中看到步骤4产生了笛卡尔积连接，成本相当的高，这两个表仅有7万行数据，执行时间超过了5分钟。 数据库中关于NOT、AND、OR的逻辑运算优先级就是按这个顺序进行的，判断是开发人员失误造成的。下面是经过优化后的执行计划。