Improve query performance instead of aggregrate function

Hi,

generally I would ask for execution plan and statistics first but for this one, try that please:

SELECT *FROM  (SELECT SD.*, MIN(SALES_DATE) OVER(PARTITION BY PRODUCT_ID) MIN_SALES_DATE        FROM TEST_GROUP SD ----3464        WHERE SD.SALES_DATE <> TO_DATE('01.01.2017', 'dd.mm.yyyy')      )WHERE SALES_DATE = MIN_SALES_DATE;

instead of reading whole table twice you can get it in 1 scan.

@Manik, you forgot to filter minimum sales date rows

I'm not sure whether I understood well what you actually want to do, but, ginven what I have understood I'd do the following

declare

cursor cs is

  with tb as (

   select sd.rowid rid, sd.product_id, row_number() over (partition by product_id order by sales_date) rn /* here I'm not sure aobout the order by - that is not sure what you mean by the first */

    ,to_date('01.01.2017','dd.mm.yyyy') new_sales_date, sales_date

   from <span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">TEST_GROUP sd</span>

   )

  select tb.rid, tb.product_id, tb.new_sales_date

  from tb

  where rn=1 and sales_date!=new_sales_date);

  type tab_cs is table of cs;

  tb_cs tab_cs;

  c_bulk_lim number:=1000;

begin

   open cs;

   loop;

     fetch cs bulk collect into tb_cs limit c_bulk_lim;

     if tb_cs.count>0 then

        forall i in 1..tb_cs.count

        update <span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">TEST_GROUP t set</span>

           t.sales_date:=tb_cs(i).new_sales_date

        where t.rowid=tb_cs(i).rid;

     end if;

     exit when tb_cs.count<c_bulk_lim;

   end loop;

   close cs;

end;

Do you have an index on product_id? I think this may help. But, if there are large amounts of data for many years, even so, it would take quite long.

Do you expect a merge would work? A simple update statement might fare better - but there is an issue if there is a very large data volume. The main thing about using cursor is that, a simple update may fail due to deadlock or too much undo space required, while a cursor, if it fails at some point (why would it?), would update at least some of the data, and that PL/SQL block may be run again to update what is left. I say so thinking that this would be something done once, not just about every day or every week.