IBM Sterling Ideas

formerly Watson Supply Chain

Submit new product ideas for IBM Sterling solutions. Before you submit, please review existing ideas; if an idea close to yours already exists, it's better to add comments or vote on the existing idea. We will review your ideas and use them to help prioritize our product development. Best of all, the portal will automatically update you when the status of your idea has been changed. Order Management, Store Engagement, Watson Order Optimizer, Inventory Visibility, CPQ and Call Center are now part of Watson Supply Chain

Connect with IBM experts and your peers on the Supply Chain Collaboration Community and the Order Management Interest Group

Add Database Partitioning support for B2B Integrator

Please add database partitioning support in Oracle for performance otimization. This feature will improve delete statements for cleanup purposes in B2Bi.

 

The partitioning will be used to achieve the following improves:

  • Exponencial performance improve by changing DELETE statement for DROP PARTITION (Hours x Seconds);
  • Database Disk Usage immediate recovered, without the need to execute maintenance based in tables and index shrink;
  • Best performance in data access by single or range partition, avoing full table scan;
  • Data compression with different particulars on each partition;
  • Statistics gathering just for the needed partitions;

 

 

  • Avatar32.5fb70cce7410889e661286fd7f1897de Guest
  • Dec 11 2019
  • Needs review
How will this idea be used?
  1. For example, we did the load of 1.000.000 records in table TRANS_DATA_PART:

    SQL> SELECT COUNT(*) FROM USER.TRANS_DATA_PART;

     

    COUNT(*)

    ----------

    1000000

     

    TABLE_OWNER TABLE_NAME PARTITION_NAME NUM_ROWS LAST_ANALYZED PARTITION_POSITION TABLESPACE_NAME COMPRESSION COMPRESS_FOR

    -------------------- -------------------- -------------------- ---------- ------------- ------------------ -------------------- ----------- ------------

    USER TRANS_DATA_PART SYS_P976 0 02/12/2019 15 1 TBS_EDISTS_DAT DISABLED

    USER TRANS_DATA_PART SYS_P1052 199520 02/12/2019 15 2 TBS_EDISTS_DAT DISABLED

    USER TRANS_DATA_PART SYS_P1061 147039 02/12/2019 15 3 TBS_EDISTS_DAT DISABLED

    USER TRANS_DATA_PART SYS_P1058 396508 02/12/2019 15 4 TBS_EDISTS_DAT DISABLED

    USER TRANS_DATA_PART SYS_P1055 256933 02/12/2019 15 5 TBS_EDISTS_DAT DISABLED

     

    2. Exclusion time with DELETE statement (like did today by B2Bi) using the DATA_ID field:

     

    SQL> DECLARE

    2 VR_DATA_ID VARCHAR2(255);

    3 CURSOR CR_TABELA IS SELECT DATA_ID FROM USER.TRANS_DATA_PART;

    4 BEGIN

    5 OPEN CR_TABELA;

    6 LOOP

    7 FETCH CR_TABELA INTO VR_DATA_ID;

    8 EXIT WHEN CR_TABELA%NOTFOUND;

    9 DELETE FROM USER.TRANS_DATA_PART WHERE DATA_ID = VR_DATA_ID;

    10 COMMIT;

    11 VR_DATA_ID := '';

    12 END LOOP;

    13 CLOSE CR_TABELA;

    14 END;

    15 /

     

    PL/SQL procedure successfully completed

     

    Executed in 167,703 seconds

     


    SQL> SELECT COUNT(*) FROM USER.TRANS_DATA_PART;

     

    COUNT(*)

    ----------

    0

     

    3. Exclusion time with TRUNCATE PARTITION statement using CREATION_DATE field:

     

    SQL> SELECT COUNT(*) FROM USER.TRANS_DATA_PART;

     

    COUNT(*)

    ----------

    1000000

     

    SQL> ALTER TABLE USER.TRANS_DATA_PART DROP PARTITION SYS_P976;

     

    Table altered

    Executed in 0,782 seconds

     

    SQL> ALTER TABLE USER.TRANS_DATA_PART DROP PARTITION SYS_P1052;

     

    Table altered

    Executed in 0,562 seconds

     

    SQL> ALTER TABLE USER.TRANS_DATA_PART DROP PARTITION SYS_P1061;

     

    Table altered

    Executed in 0,656 seconds

     

    SQL> ALTER TABLE USER.TRANS_DATA_PART DROP PARTITION SYS_P1058;

     

    Table altered

    Executed in 0,594 seconds

What is your industry? Construction / Architecture / Engineering
What is the idea priority? Urgent
DeveloperWorks ID
RTC ID
Link to original RFE
  • Attach files