MERGE JOIN CARTESIAN處理一例

今天有同事反映有個sql增加了一個表後查詢特別慢。該語句如下：

select mp.mp_id,<br /> mp.mp_name,<br /> mp.read_meter_day date_time,<br /> customer.customer_id,<br /> customer.customer_name,<br /> customer.customer_code,<br /> customer.address,<br /> meter.serial_id,<br /> meter.factory manufacturer,<br /> day_energy.value0,<br /> day_energy.value1,<br /> day_energy.value2,<br /> day_energy.value3,<br /> day_energy.value4,<br /> day_energy.quality_code<br /> from meter, day_energy, mp, contract_account, customer<br /> where meter.mp_id = mp.mp_id<br /> and meter.end_date = 0<br /> and day_energy.meas_type = 100663808<br /> and day_energy.date_time = '20090705'<br /> and day_energy.object_id = mp.mp_id<br /> and day_energy.sub_company_id = customer.sub_company_id<br /> and mp.end_date = 0<br /> and mp.contract_id(+) = contract_account.contract_id<br /> and contract_account.customer_id(+) = customer.customer_id<br /> and customer.end_date = 0<br /> and customer.customer_type = 5<br />

 

查看其執行計畫，結果如下：

-------------------------------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |<br />-------------------------------------------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 284 | 1225K (4)| 04:05:10 | | |<br />| 1 | NESTED LOOPS | | 1 | 284 | 1225K (4)| 04:05:10 | | |<br />| 2 | NESTED LOOPS | | 1 | 220 | 1225K (4)| 04:05:10 | | |<br />| 3 | NESTED LOOPS | | 1 | 208 | 1225K (4)| 04:05:10 | | |<br />| 4 | MERGE JOIN CARTESIAN | | 118M| 18G| 1200K (2)| 04:00:10 | | |<br />| 5 | PARTITION RANGE SINGLE | | 1483 | 183K| 0 (0)| 00:00:01 | 187 | 187 |<br />| 6 | PARTITION LIST SINGLE | | 1483 | 183K| 0 (0)| 00:00:01 | 1 | 1 |<br />| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| DAY_ENERGY | 1483 | 183K| 0 (0)| 00:00:01 | 2233 | 2233 |<br />|* 8 | INDEX RANGE SCAN | CONS_DAY_ENERGY | 1 | | 0 (0)| 00:00:01 | 2233 | 2233 |<br />| 9 | BUFFER SORT | | 80113 | 3442K| 1200K (2)| 04:00:10 | | |<br />|* 10 | TABLE ACCESS FULL | METER | 80113 | 3442K| 810 (2)| 00:00:10 | | |<br />|* 11 | TABLE ACCESS BY INDEX ROWID | MP | 1 | 37 | 0 (0)| 00:00:01 | | |<br />|* 12 | INDEX UNIQUE SCAN | CONS_MP_1 | 1 | | 0 (0)| 00:00:01 | | |<br />|* 13 | INDEX RANGE SCAN | CONS_CONTRACT_ACCOUNT_IDX | 1 | 12 | 1 (0)| 00:00:01 | | |<br />|* 14 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 64 | 1 (0)| 00:00:01 | | |<br />|* 15 | INDEX UNIQUE SCAN | CONS_CUSTOMER_1 | 1 | | 0 (0)| 00:00:01 | | |<br />-------------------------------------------------------------------------------------------------------------------------------------<br />

 

 由於meter表和day_energy都是大表，所以這個"4 |     MERGE JOIN CARTESIAN 
  "執行計畫是存在嚴重問題的。

 

解決方案：

 首先受到懷疑的是相關表的統計資料不正確，導致最佳化器做代價分析時出了問題。

對幾個資料表做重新分析後，執行計畫如下：

-------------------------------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |<br />-------------------------------------------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 284 | 816 (2)| 00:00:10 | | |<br />|* 1 | HASH JOIN | | 1 | 284 | 816 (2)| 00:00:10 | | |<br />| 2 | NESTED LOOPS | | 1 | 240 | 2 (0)| 00:00:01 | | |<br />| 3 | NESTED LOOPS | | 1 | 176 | 1 (0)| 00:00:01 | | |<br />| 4 | NESTED LOOPS | | 1 | 164 | 0 (0)| 00:00:01 | | |<br />| 5 | PARTITION RANGE SINGLE | | 1483 | 183K| 0 (0)| 00:00:01 | 187 | 187 |<br />| 6 | PARTITION LIST SINGLE | | 1483 | 183K| 0 (0)| 00:00:01 | 1 | 1 |<br />| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| DAY_ENERGY | 1483 | 183K| 0 (0)| 00:00:01 | 2233 | 2233 |<br />|* 8 | INDEX RANGE SCAN | CONS_DAY_ENERGY | 1 | | 0 (0)| 00:00:01 | 2233 | 2233 |<br />|* 9 | TABLE ACCESS BY INDEX ROWID | MP | 1 | 37 | 0 (0)| 00:00:01 | | |<br />|* 10 | INDEX UNIQUE SCAN | CONS_MP_1 | 1 | | 0 (0)| 00:00:01 | | |<br />|* 11 | INDEX RANGE SCAN | CONS_CONTRACT_ACCOUNT_IDX | 1 | 12 | 1 (0)| 00:00:01 | | |<br />|* 12 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1 | 64 | 1 (0)| 00:00:01 | | |<br />|* 13 | INDEX UNIQUE SCAN | CONS_CUSTOMER_1 | 1 | | 0 (0)| 00:00:01 | | |<br />|* 14 | TABLE ACCESS FULL | METER | 80113 | 3442K| 812 (2)| 00:00:10 | | |<br />-------------------------------------------------------------------------------------------------------------------------------------<br />

 

至此問題解決。