SQL & PL/SQL

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

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.

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.

@"Manik", you forgot to filter minimum sales date rows

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

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 TEST_GROUP sd

   )

  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 TEST_GROUP t set

           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;

why to make that complicated using cursor and all ?

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.

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

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.

Do you have indexes that include sales_date? If so, updating the column will lead to physical restructuring of the indexes and may cause poor performance with a lot of undo usage. I would drop the indexes and recreate them after the update if possible. Also using parallel may help.

merge into TEST_GROUP d

using (select *

       from (

         select PRODUCT_ID, TO_DATE ('01.01.2017', 'dd.mm.yyyy') SALES_DATE,

                row_number() over (partition by PRODUCT_ID order by SALES_DATE) rn

         from   TEST_GROUP

       )

       where rn = 1) s

on (d.PRODUCT_ID = s.PRODUCT_ID)

when MATCHED then

  update set

    d.SALES_DATE = s.SALES_DATE

  where 0 = decode(d.SALES_DATE, s.SALES_DATE, 1, 0)  -- null agnostic check to only update if destination isn't desired value

Note : Make sure there is an index on PRODUCT_ID!

i am checking just select part to check whether i am getting the same amount of records or not but getting errors as sql command not properly ended at the end line

select r.*

       from (

         select H_LOCPR_ID, TO_DATE ('01.01.2017', 'dd.mm.yyyy') SYS_LOAD_DTS,

                row_number() over (partition by H_LOCPR_ID order by SYS_LOAD_DTS) rn

         from   INT_CDW_DV.L_LOCAL_PROD_GRP

       )

       where rn = 1) s

on (d.H_LOCPR_ID = s.H_LOCPR_ID)

Try the below merge please?

Also, please make sure your PRODUCT_ID column is indexed.

merge INTO TEST_GROUP sd USING

(SELECT MIN(SALES_DATE)SALES_DATE,

  PRODUCT_ID

FROM TEST_GROUP sd2

GROUP BY PRODUCT_ID

)rs ON (sd.product_id=rs.product_id)

WHEN matched THEN

  UPDATE

  SET SALES_DATE       =rs.sales_date

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

Thank you,

Boobal Ganesan

There was a rogue alias in the merge select, fixed above.

Run it, check results, rollback if incorrect, commit if correct.

Boobal Ganesan wrote:
Try the below merge please?
Also, please make sure your PRODUCT_ID column is indexed.
merge INTO TEST_GROUP sd USING
(SELECT MIN(SALES_DATE)SALES_DATE,
 PRODUCT_ID
FROM TEST_GROUP sd2
GROUP BY PRODUCT_ID
)rs ON (sd.product_id=rs.product_id)
WHEN matched THEN
 UPDATE
 SET SALES_DATE =rs.sales_date
 WHERE sd.SALES_DATE <> TO_DATE ('01.01.2017', 'dd.mm.yyyy');
Thank you,
Boobal Ganesan

Won't update anything if all of the SALES_DATE records are null for a PRODUCT_ID

I'm not so sure that this merge will work: I fear the classical error that occurs when merge "can't get a stable set of rows". That is why I previously wrote the rather complicated thing with cursor and bulk. And that is why I generally avoid merge into the very table that consists the data source. I can't say I fully understand when this "can't get a stable set of rows" occurs (meaning that sometimes it is evident, but sometimes I simply don't get why Oracle says so).

"Can't get a stable set of rows" generally occurs if either...

1. There are duplicate rows generated in the USING clause
2. The WHERE clause in the USING clause is non-deterministic.

I can't see how either of these can be true here, especially as row_number is being used (which removes duplicates) and there are no non deterministic functions being used in the where clause.

i dont have indexes on any column but i have only 15000 records. So in this case using Merge will it be improve performance ?

Without an index in the worse case scenario it'll have to hash join two lots of 15000 records. 

As a minimum add an index on PRODUCT_ID, it should be a LOT quicker afterwards.

actually its PROD database for this reason i cant do that or else i could have provided an index

user12251389 wrote:
actually its PROD database for this reason i cant do that or else i could have provided an index

Do it temporarily then.

create index...

do update

drop index...

The temp index is only there to make sure your update doesn't take hours.  The column should probably have an index on it anyway, so make it part of a release cycle in the future. 

this query will not upodate the date to TO_DATE ('01.01.2017', 'dd.mm.yyyy')  which i want and i want to update the first product record which has min sales_date

user12251389 wrote:
this query will not upodate the date to TO_DATE ('01.01.2017', 'dd.mm.yyyy') which i want and i want to update the first product record which has min sales_date

What are you talking about?

In my question i am updating the records TO_DATE ('01.01.2017', 'dd.mm.yyyy') where the first product record with min sales_date is not TO_DATE ('01.01.2017', 'dd.mm.yyyy'). But the merge query will not do this which you mentioned. I have tried running this

Wasn't entirely clear.  Just move the where filter into the using clause...

merge into TEST_GROUP d

using (select *

       from (

         select PRODUCT_ID, TO_DATE ('01.01.2017', 'dd.mm.yyyy') SALES_DATE,

                SALES_DATE as OLD_SALES_DATE,

                row_number() over (partition by PRODUCT_ID order by SALES_DATE) rn

         from   TEST_GROUP      

       )

       where rn = 1 and 0 = decode(SALES_DATE, OLD_SALES_DATE, 1, 0)) s

on (d.PRODUCT_ID = s.PRODUCT_ID)

when MATCHED then

  update set

    d.SALES_DATE = s.SALES_DATE

user12251389 wrote:
i dont have indexes on any column but i have only 15000 records. So in this case using Merge will it be improve performance ?

ONLY  15k records ?

How long is it taking now ?

What size (in Bytes) is your table?  How many columns does it have?

What database version (4 digits) are you on?

MK

I am using oracle 12c and the table has 5 columns and it has 15k records and i dont have index on any of the column. The query which i mentioned in my question is taking like 2 to 3 minutes. I think its scanning the complete table.