Improve query performance instead of aggregrate function

BrendanP

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.

Paulzip

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!

user12251389

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)

Boobal Ganesan

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

Paulzip

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

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

Paulzip

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_IDFROM TEST_GROUP sd2GROUP 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

BEDE

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

Paulzip

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

user12251389

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

Paulzip

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.