Hello. I have this problem.

The script runs many times longer if you add variables to it.

Example

declare begin insert into tmp1 select fe.uid, fe.type, fe.type_id, fe.id_client, cd.id, fe.amount, fe.doc_num, fe.doc_dt, fe.doc_total, sum(fel.line_total) as lt, sum(fel.qty) as qty from event fe inner join event_line fel on fe.id = fel.id_event inner join goods dgg on fel.cd = dgg.code and dgg.id = 1111 inner join terminal mt on fe.uid = mt.terminal_uid inner join department cd on mt.shop_cd = cd.cd inner join client dac on dac.id_aud = 2222 and dac.id_client = fe.id_client where fe.event_type = 1 and fe.type_id = 1 and fe.doc_dt >= trunc(to_date('01.05.2016', 'dd.mm.yyyy')) and fe.doc_dt < trunc(to_date('29.05.2016', 'dd.mm.yyyy') + 1) and fel.doc_dt >= trunc(to_date('01.05.2016', 'dd.mm.yyyy')) and fel.doc_dt < trunc(to_date('29.05.2016', 'dd.mm.yyyy') + 1) and fe.id_trn is not null group by fe.uid, fe.type, type_id, fe.id_client, cd.id, fe.amount, fe.doc_num, fe.doc_dt, fe.doc_total end; стоимость мощность байты INSERT STATEMENT, GOAL = ALL_ROWS 121930 8597 2372772 LOAD TABLE CONVENTIONAL SE TMP1 HASH GROUP BY 121930 8597 2372772 HASH JOIN 121418 8597 2372772 REMOTE DEPARTMENT 11 1462 95030 HASH JOIN 121406 8574 1809114 MAT_VIEW ACCESS FULL SE TERMINAL 16 2617 143935 HASH JOIN 121390 8574 1337544 REMOTE CLIENT 200 53279 1118859 HASH JOIN 120650 65451 8835885 HASH JOIN 90666 149898 9593472 REMOTE GOODS 3 221 6630 PARTITION RANGE SINGLE 90583 25389230 863233820 TABLE ACCESS FULL SE EVENT_LINE 90583 25389230 863233820 PARTITION RANGE SINGLE 23586 1490348 105814708 TABLE ACCESS FULL SE EVENT 23586 1490348 105814708

if you add a variable to it, it will be executed many times longer.

 declare v_dt date; v_date_end date; begin v_dt := trunc(to_date('01.05.2016', 'dd.mm.yyyy')); v_date_end := trunc(to_date('29.05.2016', 'dd.mm.yyyy')); insert into tmp1 select fe.uid, fe.type, fe.type_id, fe.id_client, cd.id, fe.amount, fe.doc_num, fe.doc_dt, fe.doc_total, sum(fel.line_total) as lt, sum(fel.qty) as qty from event fe inner join event_line fel on fe.id = fel.id_event inner join goods dgg on fel.cd = dgg.code and dgg.id_gds = 1111 inner join terminal mt on fe.point_uid = mt.terminal_uid inner join department cd on mt.shop_cd = cd.cd inner join client dac on dac.id_aud = 2222 and dac.id_cli_client = fe.id_cli_client where fe.event_type = 1 and fe.type_id = 1 and fe.doc_dt >= :a and fe.doc_dt < :b and fel.doc_dt >= :a and fel.doc_dt < :b and fe.id_trn is not null group by fe.uid, fe.type, type_id, fe.id_client, cd.id, fe.amount, fe.doc_num, fe.doc_dt, fe.doc_total end; стоимость мощность байты INSERT STATEMENT, GOAL = ALL_ROWS 557721 1 272 LOAD TABLE CONVENTIONAL SE TMP1 HASH GROUP BY 557721 1 272 FILTER NESTED LOOPS 557720 7 1904 NESTED LOOPS 557713 7 1757 HASH JOIN 557706 7 1302 NESTED LOOPS 557689 7 917 NESTED LOOPS 557689 3109 314009 PARTITION RANGE ITERATOR 370610 3082 206494 TABLE ACCESS FULL SE EVENT 370610 3082 206494 PARTITION RANGE ITERATOR 61 1 34 TABLE ACCESS BY LOCAL INDEX ROWID SE EVENT_LINE 61 1 34 INDEX RANGE SCAN SE EVENT_LINE_IDX 60 1 REMOTE GOODS 0 1 30 MAT_VIEW ACCESS FULL SE TERMINAL 16 2617 143935 REMOTE DEPARTMENT 1 1 65 REMOTE CLIENT 1 1 21

I can not understand why. runs much longer.

  • foxbase.ru/oracle-programming/… - Mike
  • / * + FULL (fe1) * / helped. the script was completed in a minute and a half, and it used to take about 50 minutes. Thank you very much, it helped a lot :-) - Anton Yudenkov

1 answer 1

In the case of fixed values, the optimizer predicts the number of records and takes this into account in the execution plan. For example, it can use a full table scan, instead of using indexes, if it expects a lot of records on the output. And when the records are many, a full scan is indeed much faster than the index. If they are few, then the index is much faster.

In the case of variables, the expression is compiled before values ​​are substituted. And the optimizer cannot make assumptions about what interval of dates you will set and how many records will be in the sample. He expects a small interval and decides to use an index, which in your case is absolutely not suitable.

In such situations there is only one way out - to tell the optimizer clearly what to do. The optimizer hints help in this case .

I can recommend using /*+FULL(fe1)*/ , to prompt a full scan of the event_line as fe1 table event_line as fe1 and / or /*+NO_USE_NL*/ exclude, often slower, NESTED LOOPS.