Forum Stats

  • 3,768,172 Users
  • 2,252,755 Discussions
  • 7,874,481 Comments

Discussions

Improve query performance instead of aggregrate function

13»

Answers

  • user12251389
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 7:04AM

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

  • Paulzip
    Paulzip Member Posts: 8,489 Blue Diamond
    edited Aug 18, 2017 7:11AM
    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
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 7:11AM

    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
    Paulzip Member Posts: 8,489 Blue Diamond
    edited Aug 18, 2017 7:13AM
    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
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 7:16AM

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

    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
    Mike Kutz Member Posts: 5,792 Silver Crown
    edited Aug 18, 2017 9:40AM
    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
    user12251389 Member Posts: 322 Blue Ribbon
    edited Aug 18, 2017 9:54AM

    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.

This discussion has been closed.