All geek questions in one place

Mysterious Cartesian product in Oracle UPDATE

We have a unique problem in production. From time to time, our Oracle 12c database will hang during our batch cycle in the middle of the night. We will have to restart the database in order to continue working for several days / weeks. Then it happens again. After some digging, we narrowed it down to this SQL:

UPDATE c_bill SET reason_desc = (SELECT description FROM codes 
WHERE code_group = 'TRANSACTION_TYPE' AND code = c_bill.reason_code);

I renamed the tables, but the code table contains the code and description. The developer is trying to copy the description from this to the c_bill table.

This SQL is part of a stored procedure that runs at night as part of a batch job. The developer performs another update immediately before this, which goes through a penalty, but this SQL takes a lot of time.

During a particular run, the table had 36,308 rows. When I check the production DB (I look at SQLs in the v $ sql table), I see the following:

Rows_processed for the first update 36308 Rows_processed for the above update 1318270864, ​​and this happpens should be = 36308 * 36308 !! (Cartesian product?)

We do not have this problem in TEST. When I tried UPDATE and explained that it was planned in TEST, there is no problem - it shows exactly 36308 rows in both UPDATE and v $ sql.

This is puzzling. Does anyone see a chance of a cartesian product in this SQL? Or you know about some kind of error in Oracle 12c optimizer that can turn it into a Cartesian one (we just applied some corrections to fix the errors with Group by!).

Oracle - . , . . , Oracle, . / .

: , - , , UPDATE sql , , . . .

+4
2

, , , , . executions v$sql 1? 36308? 1, , v$sql_plan dba_hist_sql_plan , , AWR. 36308, , , 36308, - 36308 , - .

+1

- , . , UPDATE, :

UPDATE 
    (SELECT c_bill.reason_desc oldDesc, codes.description newDesc
     FROM c_bill INNER JOIN codes ON
         code.code_group = 'TRANSACTION_TYPE' AND 
         codes.code = c_bill.reason_code)
SET oldDesc = newDesc

, Oracle . , .

+1

Source: https://habr.com/ru/post/1653487/

More articles:


All Articles