1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Altering a table which contains huge data

Discussion in 'SQL PL/SQL' started by Revathi Thirunagari, Sep 7, 2017.

Tags:
  1. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi All,


    We had requirement that we need to alter table which contains huge data nearly 25 millions records.
    Its range partitioned table and having 60 Partitions .
    We want to add some extra columns to the same table .

    Obviously it will take more time .What is the efficient way of doing this ?
    Do I need to disable all the indexes or Table gathering

    I know that when we have bulk insertions into the table, disabling the indexes will help .
    When we are selecting the data by applying indexed column where conditions , gathering statistics will help .

    But in this case PARALLE HINT will help ?
    or
    Do I need to go the Multi columns alter ?
    or
    Do we have any other best solution?

    Please help me to resolve the problem

    Details : Table Size : 25 Millions Records
    No of Partitions : 60
    Partition Type : Range Partition on EntryDate Column
    Oracle Version :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


    Thanks
    Revathi.T
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    715
    Likes Received:
    143
    Trophy Points:
    830
    Location:
    Russian Federation

    Hi.
    Work is planned online?
    Provide there, examples of columns that you plan to add.

    1) On-line -> dbms_redefinition

    2)
    Values on NEW_COLUMN are initialized immediately or later ?

    For example:
    Code (Text):

    DROP TABLE test_t1;
    CREATE TABLE test_t1 (ID INT PRIMARY KEY,a INT);
    INSERT INTO test_t1 SELECT LEVEL, nullif(MOD(LEVEL,1E5),0)  FROM dual CONNECT BY LEVEL<=1e6;
    INSERT INTO test_t1 SELECT 2e6+LEVEL, nullif(MOD(LEVEL,1E5),0)  FROM dual CONNECT BY LEVEL<=1e6;
    INSERT INTO test_t1 SELECT 3e6+LEVEL, nullif(MOD(LEVEL,1E5),0)  FROM dual CONNECT BY LEVEL<=1e6;
    INSERT INTO test_t1 SELECT 4e6+LEVEL, nullif(MOD(LEVEL,1E5),0)  FROM dual CONNECT BY LEVEL<=1e6;
    INSERT INTO test_t1 SELECT 5e6+LEVEL, nullif(MOD(LEVEL,1E5),0)  FROM dual CONNECT BY LEVEL<=1e6;
    INSERT INTO test_t1 SELECT 6e6+LEVEL, nullif(MOD(LEVEL,1E5),0)  FROM dual CONNECT BY LEVEL<=1e6;
    INSERT INTO test_t1 SELECT 7e6+LEVEL, nullif(MOD(LEVEL,1E5),0)  FROM dual CONNECT BY LEVEL<=1e6;

    COMMIT;

    ALTER TABLE test_t1 ADD (c  INT DEFAULT 0 NOT NULL ,y VARCHAR2(8));


    SELECT COUNT(*) FROM test_t1 WHERE c IS NULL;
     
     
    Last edited: Sep 7, 2017
  3. Revathi Thirunagari

    Revathi Thirunagari Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Hyderabad
    Hi Sergey Krasnoslobodtsev ,

    Thanks for interest to resolve my issue .

    Some of the new columns are having the default value . Some of the column are not having the values .
    Any how are you suggesting to use the REDEFINATION package to add the column ?

    Thanks
    Revathi thirunagari.



    SELECT COUNT(*) FROM test_t1 WHERE c IS NULL;
    [/code][/QUOTE]
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    715
    Likes Received:
    143
    Trophy Points:
    830
    Location:
    Russian Federation
    For example:

    Code (Text):

    drop table test;
    create table test(id   number primary key,
                      nm number(3)
                      );
    insert into test select level,level from dual connect by level < 1000;
    commit;

    -- Creating Interim Table
    create table tes_vchar(id   number not null,nm varchar2(3));

    declare
     col_mapping varchar2(1000);
    begin
    --  map all the columns in the interim table to the original table
     col_mapping :=
                   'id             id  , '||
                   'to_char(nm)       nm';
         
     dbms_redefinition.start_redef_table(user, 'test', 'test_vchar',
                                         col_mapping);
    end;
    /

    declare
     error_count pls_integer := 0;
    begin
      dbms_redefinition.copy_table_dependents(user, 'test', 'test_vchar',
                                              1, true,true,true,false,
                                              error_count);

      dbms_output.put_line('errors := ' || to_char(error_count));
    end;
    /

    exec  dbms_redefinition.finish_redef_table(user, 'test', 'test_vchar');

    -- Drop the interim table
    drop table test_vchar;

    desc test;

     
    Code (Text):

    drop table test_t1;
    drop table test_t2;
    create table test_t1(id   number primary key,
                      nm NUMBER
                      );
    insert into test_t1 select level,level from dual connect by level < 1E6;
    commit;

    -- Creating Interim Table
    create table test_t2 (id   number not null,nm varchar2(3),f1 INT NOT NULL  ,f2 INT DEFAULT 0 NOT NULL  ) ;

    declare
     col_mapping varchar2(1000);

    begin
    --  map all the columns in the interim table to the original table
     col_mapping :=
                   'id             id  , '||    
                   'to_char(nm)       nm,' ||
                   'to_number(1)      f1 ';
         
     dbms_redefinition.start_redef_table(user, 'test_t1', 'test_t2',
                                         col_mapping);
    end;
    /

    declare
     error_count pls_integer := 0;
    begin
      dbms_redefinition.copy_table_dependents(user, 'test_t1', 'test_t2',
                                              1, true,true,true,false,
                                              error_count);

      dbms_output.put_line('errors := ' || to_char(error_count));
    end;
    /

    exec  dbms_redefinition.finish_redef_table(user, 'test_t1', 'test_t2');

    -- Drop the interim table
    drop table test_t2;

    desc test_t1;

     
    N.B. Do not forget that there are restrictions(Restrictions for Online Redefinition of Tables)

    If there are additional specific questions, then write here we will try to help

    Additional links:

    Performing Online Redefinition with DBMS_REDEFINITION

    DBMS_REDEFINTION

    Online Table Redefinition Examples
     
    Last edited: Sep 8, 2017