Skip to main content

Search

Welcome to Tibero GTS!

Speed Improvement Measures for Table Modify

Document Type | Technical Information

Category | Patch/Upgrade

Applicable Product Versions | 6FS06, 6FS07

Document Number | TPATI006

 

Overview

When modifying a table column, types with variable size (number, varchar, nvarchar, raw) do not actually change the row if the size is increased, so the speed can be improved through a patch.
 
 

Method

Apply the column modify DDL speed improvement patch to resolve the issue.

  • Applied patch: 194853e
  • This patch is included by default in Tibero7.

Note

Apply the patch through technical support provided by Tmax Tibero.

 

Test

1. Sample Data Generation

Generate 1 million random data records.

-- Create test table
CREATE TABLE test_num_tbl (
    id           NUMBER(10)      PRIMARY KEY,
    amt          NUMBER(8,2)     NOT NULL,   -- Maximum 999999.99
    category_cd  VARCHAR2(10),
    created_dt   DATE
);

-- Insert 10 million test data records
DECLARE
    TYPE t_id_tab   IS TABLE OF NUMBER      INDEX BY PLS_INTEGER;
    TYPE t_amt_tab  IS TABLE OF NUMBER(8,2) INDEX BY PLS_INTEGER;
    TYPE t_cat_tab  IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
    v_id_arr   t_id_tab;
    v_amt_arr  t_amt_tab;
    v_cat_arr  t_cat_tab;
    c_total     CONSTANT PLS_INTEGER := 1000000;  -- Total count
    c_batch     CONSTANT PLS_INTEGER := 10000;     -- Batch (commit) size
    v_start_tm  TIMESTAMP := SYSTIMESTAMP;
BEGIN
    FOR i IN 0 .. (c_total / c_batch) - 1 LOOP
        v_id_arr.DELETE;
        v_amt_arr.DELETE;
        v_cat_arr.DELETE;
        FOR j IN 1 .. c_batch LOOP
            v_id_arr(j)  := i * c_batch + j;
            -- Random value within NUMBER(8,2) range: 0.00 ~ 999999.99
            v_amt_arr(j) := ROUND(DBMS_RANDOM.VALUE(0, 999999.99), 2);
            v_cat_arr(j) := CASE MOD(v_id_arr(j), 5)
                                WHEN 0 THEN 'A'
                                WHEN 1 THEN 'B'
                                WHEN 2 THEN 'C'
                                WHEN 3 THEN 'D'
                                ELSE 'E'
                            END;
        END LOOP;
        FORALL k IN 1 .. c_batch
            INSERT INTO test_num_tbl (id, amt, category_cd, created_dt)
            VALUES (v_id_arr(k), v_amt_arr(k), v_cat_arr(k), SYSDATE - DBMS_RANDOM.VALUE(0, 365));
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Progress: ' || ((i+1) * c_batch) || ' / ' || c_total);
    END LOOP;
    --DBMS_OUTPUT.PUT_LINE('Completed. Elapsed time: ' || 
   --     EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_tm)) || 'sec');
END;
/

 

2. Before Patch

Before the patch, the modify operation took about 9.6 seconds.

SQL> ALTER TABLE test_num_tbl MODIFY (amt NUMBER(10,4));

Table 'TEST_NUM_TBL' altered.

Total elapsed time 00:00:09.653586

 

3. After Patch

After the patch, the modify operation took about 0.1 seconds.

SQL> ALTER TABLE test_num_tbl MODIFY (amt NUMBER(10,4));

Table 'TEST_NUM_TBL' altered.

Total elapsed time 00:00:00.124676