Good people, help! At work, I encountered such a problem, there is such a request:

select /*+ USE_HASH(nsi_core_gr) */ kategoria,works,oper, time_mmm, sum(ratio) over(PARTITION BY kategoria,works,oper order by kategoria,works,oper rows unbounded preceding) sval_rows from ( select /*+ USE_HASH(nsi_core_gr) */ d.work as works,d.kategoria, d.oper, d.time_mmm, ratio_to_report(count(d.time_mmm)) OVER (PARTITION BY d.kategoria,d.work,d.oper) AS ratio from nsi_data d inner join nsi_core_gr crg on crg.id_gr=d.work_gr and crg.id_core=21 where d.date_zagr between to_date('01.1.2016','dd.mm.yyyy') and to_date('30.3.2016','dd.mm.yyyy') and d.proc_od=9 group by d.kategoria,d.work,d.oper,d.time_mmm order by d.kategoria,d.work,d.oper,d.time_mmm )

The nsi_data table contains more than 10 million rows, the nsi_core_gr table contains about 2000 rows. Without an inner join, the request is executed for a reasonable amount of time, with inner time is increased by a little more than 10 times, which is unacceptable, but you cannot do without inner because sometimes you need to fetch it. Plan hash value: 1412621496

execution plan with inner

 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 46767 | 2968K| | 43128 (1)| 00:08:38 | | 1 | WINDOW NOSORT | | 46767 | 2968K| | 43128 (1)| 00:08:38 | | 2 | VIEW | | 46767 | 2968K| | 43128 (1)| 00:08:38 | | 3 | WINDOW BUFFER | | 46767 | 1689K| | 43128 (1)| 00:08:38 | | 4 | SORT GROUP BY | | 46767 | 1689K| 2592K| 43128 (1)| 00:08:38 | | 5 | NESTED LOOPS | | 46767 | 1689K| | 42672 (1)| 00:08:33 | | 6 | NESTED LOOPS | | 46767 | 1689K| | 42672 (1)| 00:08:33 | | 7 | TABLE ACCESS BY INDEX ROWID | NSI_CORE_GR | 50 | 350 | | 2 (0)| 00:00:01 | | 8 | BITMAP CONVERSION TO ROWIDS| | | | | | | |* 9 | BITMAP INDEX SINGLE VALUE | C_GR_IND | | | | | | | 10 | BITMAP CONVERSION TO ROWIDS | | | | | | | | 11 | BITMAP AND | | | | | | | |* 12 | BITMAP INDEX SINGLE VALUE | W_GR_INDEX | | | | | | |* 13 | BITMAP INDEX SINGLE VALUE | P_OD_INDEX | | | | | | |* 14 | TABLE ACCESS BY INDEX ROWID | NSI_DATA | 935 | 28050 | | 42672 (1)| 00:08:33 | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 9 - access("CRG"."ID_CORE"=21) 12 - access("CRG"."ID_GR"="D"."WORK_GR") 13 - access("D"."PROC_OD"=9) 14 - filter("D"."DATE_ZAGR"<=TO_DATE(' 2016-03-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."DATE_ZAGR">=TO_DATE(' 2016-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Execution plan without inner

 Plan hash value: 719937189 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1800K| 111M| | 345K (1)| 01:09:04 | | 1 | WINDOW NOSORT | | 1800K| 111M| | 345K (1)| 01:09:04 | | 2 | VIEW | | 1800K| 111M| | 345K (1)| 01:09:04 | | 3 | WINDOW BUFFER | | 1800K| 44M| | 345K (1)| 01:09:04 | | 4 | SORT GROUP BY | | 1800K| 44M| 76M| 345K (1)| 01:09:04 | |* 5 | TABLE ACCESS FULL| NSI_DATA | 1800K| 44M| | 331K (1)| 01:06:24 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("D"."DATE_ZAGR"<=TO_DATE(' 2016-03-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D"."PROC_OD"=9 AND "D"."DATE_ZAGR">=TO_DATE(' 2016-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

hint as it seems do not work ... it seems to me, please help me, I am a noob in optimization. Thank you in advance!

  • 1. By date_zagr index is (although I do not think that will help, but suddenly). 2. Try the condition connection to write as on crg.id_gr=nvl(d.work_gr,0) can return to FULL. Although FULL in your "good" version I don’t really like the same thing, but of two evils ... - Mike
  • Try to put the alias in the hint instead of the table name: / * + leading (crg d) use_hash (crg d) * / - SkyWalker
  • @SkyWalker will take out your comment in response to the author marked it as correct and close the question? - Denis

1 answer 1

Try to put an alias in the hint, but not the name of the table:

 /*+ leading (crg d) use_hash (crg d) */