Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Improve query performance instead of aggregrate function

user12251389Aug 18 2017 — edited Aug 18 2017

I have query where i want to change the SALES_DATE to '01.01.2017' for the 1st record for a product. It means i want to get every unique product which is stored in PRODUCT_ID and check the first SALES_DATE on which the product is loaded and change this SALES_DATE to '01.01.2017'.

I have used the below query which is working but its hitting the performance it seems. So is there any other i can write the same logic and improves the performance?

UPDATE TEST_GROUP sd

set SALES_DATE = TO_DATE ('01.01.2017', 'dd.mm.yyyy')

where SALES_DATE =

( select min(SALES_DATE)

   from TEST_GROUP sd2

   where sd.PRODUCT_ID = sd2.PRODUCT_ID  

)

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

Comments

APC
Answer
It's the use of the AUTONOMOUS TRANSACTION pragma. Your current transaction cannot see the result of that DDL because it occurs outside of the current transaction. The clue is in the name.

Of course, you cannot execute the DDL in a trigger without using that pragma, so you're pretty well stymied. There is a solution in 11g but that's not going to help you. Unfortunately, your only option is to pre-create the required partitions ahead of the need. For instance, you could have a DBMS JOB to create a partition for the next month which runs on the last day of each month (or whatever date makes business sense).

Cheers, APC

blog: http://radiofreetooting.blogspot.com
Marked as Answer by 676821 · Sep 27 2020
Laurent Schneider
I second Andrew arguments. In 11g, you have automatic partition creation with interval partitioning.

In 8i, DBMS_JOB rules !

Something like (picture)

table T (partition p2007, partition p2008, partition MAXVALUES)

trigger TR if values > (select max(highvalue) from user_tab_parts) then DBMS_JOB.CREATE_JOB('at midnight, split partition MAXVALUES in MAXVALUES/P2009 ');
and insert row in partition MAXVALUES


HTH
Laurent

Edited by: Laurent Schneider on Jan 14, 2009 3:24 PM
Laurent Schneider
or, much easier and cleaner,

every sunday, check for values in MAXVALUE partition and move those rows in new partitions...
OrionNet
Hello,

You don't need trigger to identify missing partitions and add them, see following procedure

using v_missing_months you can create n number of partitions ahead of time. You can also use this to create partition one month in advance and it will work for any "RANGE" partitioned table. Then you can scheduled this procedure to run using dbms_jobs or from cron job (using unix)
DECLARE
      TYPE partrec IS RECORD (
         table_name        varchar2 (30),
         partition_name    varchar2 (30),
         upperbound        varchar2 (83),
         tablespace_name   varchar2 (70)
      );

      CURSOR p_cur
      IS
         SELECT   table_name, partition_name, high_value, tablespace_name
             FROM user_tab_partitions
            WHERE high_value_length > 2
                ORDER BY partition_position DESC;

      CURSOR part_cur (i_table_name IN varchar2)
      IS
         SELECT   table_name, partition_name, high_value, tablespace_name
             FROM user_tab_partitions
            WHERE table_name = i_table_name
         ORDER BY partition_position DESC;

      prec                         partrec;
      v_last_partition             varchar2 (50);
      v_next_partition             varchar2 (50);
      v_last_part_date             varchar2 (50);
      v_next_part_date             date;
      v_part_tmp_date              date;
      v_part_date                  date;
      v_initial_date               date;
      i_initial_date               date;
      v_sql                        varchar2 (300);
      v_initial_part               varchar2 (30);
      i_initial_part               varchar2 (30);

      c_dummy_partition   CONSTANT varchar2 (5)   := 'DUMMY';
      parent_tname                 varchar2 (30);
      v_missing_months             number;
      i                            number;

   BEGIN

      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"';
      --
      -- Adding partitions to existing range partitioned tables
      --
      FOR cur IN p_cur
      LOOP
         OPEN part_cur (cur.table_name);
         FETCH part_cur
          INTO prec;
        CLOSE part_cur;
         v_last_partition := prec.partition_name;
        -- DBMS_OUTPUT.put_line ('Last partition Name=' || v_last_partition);
         v_last_part_date := SUBSTR (prec.upperbound, 10, 20);
        -- DBMS_OUTPUT.put_line ('Last partition date' || v_last_part_date);
         v_part_date := TO_DATE (v_last_part_date, 'YYYY-MM-DD HH24:MI:SS');
        -- DBMS_OUTPUT.put_line ('Last Partition Date = ' || v_part_date);
         v_part_tmp_date := v_part_date;
         v_missing_months := MONTHS_BETWEEN (SYSDATE, v_part_date);

        -- NOTE : MODIFY following line to create partition in one month or n month in advance.
         v_missing_months := v_missing_months + 12; 

        -- DBMS_OUTPUT.put_line
                  --        (   'No of monthly partitions  will be created is:'
                     --      || CEIL (v_missing_months)
                     --     );

         FOR i IN 1 .. v_missing_months
         LOOP
            IF (v_part_date = LAST_DAY (v_part_date + i))
            THEN
               v_part_date := v_part_date + i;
               v_next_part_date := ADD_MONTHS (v_part_date, i);
            ELSE
               v_next_part_date := ADD_MONTHS (v_part_date, i);
            END IF;
            v_part_tmp_date := v_part_tmp_date + i;
            DBMS_OUTPUT.put_line (   'Next partition Date = '
                                || TO_CHAR (v_next_part_date, 'YYYYMM')
                             );

            v_next_partition :=
               REPLACE (v_last_partition,
                        SUBSTR (v_last_partition, -6),
                        TO_CHAR (v_next_part_date - i, 'YYYYMM')
                       );

            DBMS_OUTPUT.put_line ('New Partition Name = ' || v_next_partition);
            v_sql :=
                  'ALTER TABLE '
               || prec.table_name
               || ' ADD PARTITION '
               || v_next_partition
               || ' VALUES LESS THAN ( TO_DATE('''
               || v_next_part_date
               || ' 00:00:00'', ''YYYYMMDD HH24:MI:SS''))'
               || ' tablespace '
               || prec.tablespace_name;

  DBMS_OUTPUT.put_line (v_sql);
           EXECUTE IMMEDIATE v_sql;
            COMMIT;
        END LOOP;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 300));
         RAISE;
   END;
Regards
676821
Thanks a lot for all the replies.

Altough they were all helpful, for some reason, I was asked not to use JOBS (if possible).

After APC confirmed my first solution would not work I gave it some thought and came up with another approach which might not be the most appropriate solution in this case, but the result was achieved without JOBS.

Solution I´m using:

The lower partition, which is created with the table, will hold the current month´s records and the previous ones;

A BEFORE INSERT TRIGGER will call an AUTONOMOUS TRANSACTION PROCEDURE that check if next month´s partition (relative to the record that´s being inserted) is already created. If it´s not, the procedure then creates it;

The insertion now will work, as the partition created outside the transaction is for next month. This way the partition for the current month is always going to be available.

PS.: It might be important to point out that this solution only works in this situation because the business rules ensure that there will never be a record with a date greater than the current date. If this rule didn´t exist the solution would not be suitable.

Thanks again.

Bruno
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 15 2017
Added on Aug 18 2017
28 comments
1,299 views