Forum Stats

  • 3,768,187 Users
  • 2,252,756 Discussions
  • 7,874,485 Comments

Discussions

Improve query performance instead of aggregrate function

user12251389
user12251389 Member Posts: 322 Blue Ribbon
edited Aug 18, 2017 9:54AM in SQL & PL/SQL

I have query where i want to change the SALES_DATE to '01.01.2017' for the 1st record for a product. It means i want to get every unique product which is stored in PRODUCT_ID and check the first SALES_DATE on which the product is loaded and change this SALES_DATE to '01.01.2017'.

I have used the below query which is working but its hitting the performance it seems. So is there any other i can write the same logic and improves the performance?

UPDATE TEST_GROUP sdset SALES_DATE = TO_DATE ('01.01.2017', 'dd.mm.yyyy')where SALES_DATE = ( select min(SALES_DATE)   from TEST_GROUP sd2   where sd.PRODUCT_ID = sd2.PRODUCT_ID    )and sd.SALES_DATE  <> TO_DATE ('01.01.2017', 'dd.mm.yyyy');
ManikMustafa_KALAYCIMike Kutz
«13

Answers

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy
    edited Aug 18, 2017 3:20AM

    Try this

    select * from (Select a.*, row_number() over(partition by PRODUCT_ID order by SALES_DATE) rn from TEST_GROUP where SALES_DATE <> TO_DATE ('01.01.2017', 'dd.mm.yyyy')) where rn = 1
  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,368 Bronze Crown
    edited Aug 18, 2017 3:22AM

    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
    Manik Member Posts: 2,906 Gold Trophy
    edited Aug 18, 2017 3:22AM

    Can't test without data, but just a guess...

    SELECT sd.*, MIN (sales_date) OVER (PARTITION BY product_id)

      FROM test_group sd

    WHERE sd.SALES_DATE <> TO_DATE ('01.01.2017', 'dd.mm.yyyy');

    Cheers,

    Manik.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,368 Bronze Crown
    edited Aug 18, 2017 3:24AM

    @Manik, you forgot to filter minimum sales date rows

    Manik
  • user12251389
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 3:28AM

    sorry i have edited my question i have to update my query not select. I nthis how it can be done ?

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy
    edited Aug 18, 2017 3:30AM

    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;

    Mustafa_KALAYCI
  • user12251389
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 3:38AM

    why to make that complicated using cursor and all ?

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy
    edited Aug 18, 2017 3:43AM

    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.

  • user12251389
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 3:46AM

    How can i update it ? I have edited my question with update. I believe MIN_SALES_DATE should be out of select query as alias. I am getting error as invalid identifier when i tried to update it

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy
    edited Aug 18, 2017 3:48AM

    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.

This discussion has been closed.