There is longer a change request. oracle



  • 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.



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2