Improve query performance instead of aggregrate function

user12251389

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

Paulzip

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. 

user12251389

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

Paulzip

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?

user12251389

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

Paulzip

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

Mike Kutz

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

user12251389

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.