Forum Stats

  • 3,770,127 Users
  • 2,253,073 Discussions
  • 7,875,336 Comments

Discussions

Improve query performance instead of aggregrate function

2

Answers

  • BrendanP
    BrendanP Member Posts: 383 Bronze Badge
    edited Aug 18, 2017 4:33AM

    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
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Aug 18, 2017 5:41AM

    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!

    Mustafa_KALAYCIMike Kutz
  • user12251389
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 5:18AM

    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
    Boobal Ganesan Member Posts: 224
    edited Aug 18, 2017 5:28AM

    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
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Aug 18, 2017 5:43AM

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

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

  • Paulzip
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Aug 18, 2017 5:44AM
    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
    BEDE Oracle Developer Member Posts: 2,303 Gold Trophy
    edited Aug 18, 2017 6:09AM

    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
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Aug 18, 2017 6:41AM

    "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
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 6:48AM

    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
    Paulzip Member Posts: 8,494 Blue Diamond
    edited Aug 18, 2017 6:56AM

    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.

This discussion has been closed.