There is longer a change request. oracle

[meriah]

Hello. I have a problem.

The violin shall be performed at times longer if the variables are added.

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, it it'll be once more.

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't understand why it's been done for a long time.

[kalyn]

In the case of fixed values, the optimist will foresee the number of records and take this into account in the implementation plan. For example, it can use complete scanning of the table instead of using indexes if it expects many outlet records. And when there's a lot of full scans, really much faster than the index. If they are small, the index is much faster.

In the case of variables, the expression shall be compiled before the values are set. And the optimizer can't speculate on what time you're givein' and how many records are in the sample. He's counting on a small interval and decides to use the index, which in your case is completely irrelevant.

There's only one way in these situations - to tell the optimist what to do. Help in this case https://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm

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