Oracle Online Redefinition

Oracle Online Redefinition

     When a table records fragmented, the select statement takes more I/O.
Its causes a performance issue. 
      Defragmentation help to increase the performance of the data.
      Online Redefinition is one of the Method to Defragment the table.
Step 1. Create a table.
SQL> create table ram_test_tab(
  2  id number(5),
  3  name varchar2(10) default ‘RAM’,
  4  edate date default sysdate);
Table created.
SQL> desc ram_test_tab;
 Name                                         Null?      Type
 —————————————– ——– —————————-
 ID                                                          NUMBER(5)
 NAME                                                   VARCHAR2(10)
 EDATE                                                  DATE
Step. 2. The Table Must have a Primary Key:
SQL> alter table ram_test_tab add constraint pk primary key(id);
Table altered.

 SQL> begin
  2  for i in 1..99999 loop
  3  insert into ram_test_tab (id) values(i);
  4  end loop;
  5  end;
  6  /                                                                                                                                                                                                                                                                                                                         PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Step.3 . Collect The Stats about the table.
SQL>  analyze table ram_test_tab compute statistics;
Table analyzed.
SQL> select table_name, round((blocks*8),2) “BLOCK”,
  2  round((num_rows*avg_row_len/1024),2) “SIZE”,
  3  to_char(last_analyzed,’hh:mm:ss dd/mm/yy’)
  4  from user_tables
  5  where table_name=’RAM_TEST_TAB’;
TABLE_NAME                BLOCK       SIZE TO_CHAR(LAST_ANAL
——————–                ———-           ———- —————–
RAM_TEST_TAB              2960           2050.76 04:10:06 31/10/18
Step. 4. Fragment the data:

SQL> delete from ram_test_tab
  2  where id between 200 and 20000;                                                                                                                                                                                                                                                                             19801 rows deleted.
SQL>  analyze table ram_test_tab compute statistics;
Table analyzed.
After collecting statistics the dictionary table updated with latest records.
SQL>select table_name, round((blocks*8),2) “BLOCK”,
  2  round((num_rows*avg_row_len/1024),2) “SIZE”,
  3  to_char(last_analyzed,’hh:mm:ss dd/mm/yy’)
  4  from user_tables
  5  where table_name=’RAM_TEST_TAB’;
 
TABLE_NAME                BLOCK       SIZE TO_CHAR(LAST_ANAL
——————–               ———-              ———- —————–
RAM_TEST_TAB              2960              1644.69 04:10:07 31/10/18
Step. 5. Create a Duplicate Table:
If its confirmed fragmentation lead to performance issue. then defragmentation is recommended. Before going to online redefinition package. Create a Dummy table which have same metadata of the fragmented table.

SQL> create table ram_dummy
  2  as select * from ram_test_tab
  3  where 1=2;                                                                                                                                           Table created.
SQL> desc ram_dummy;
 Name                                      Null?    Type
 —————————————– ——– —————————-
 ID                                                           NUMBER(5)
 NAME                                                    VARCHAR2(10)
 EDATE                                                   DATE
SQL> select * from ram_dummy;
no rows selected
Step. 6. Online redefinition package Run:
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>  exec dbms_redefinition.can_redef_table(‘SAKTHI’,’RAM_TEST_TAB’);
PL/SQL procedure successfully completed.
SQL>  exec  dbms_redefinition.start_redef_table(‘SAKTHI’,’RAM_TEST_TAB’,’_DUMMY’);
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.sync_interim_table(‘SAKTHI’,’RAM_TEST_TAB’,’RAM_DUMMY’);
PL/SQL procedure successfully completed.
SQL>  exec dbms_redefinition.finish_redef_table(‘SAKTHI’,’RAM_TEST_TAB’,’RAM_DUMMY’);
PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats(‘SAKTHI’);
PL/SQL procedure successfully completed.
 So, the fragmentation Switch to another table. And defragment the tables.

 SQL> select table_name, round((blocks*8),2) “BLOCK”,
  2  round((num_rows*avg_row_len/1024),2) “SIZE”,
  3  to_char(last_analyzed,’hh:mm:ss dd/mm/yy’)
  4  from dba_tables
  5  where table_name=’RAM_TEST_TAB’;                                                                                                                                                                                                                                                    TABLE_NAME                BLOCK      SIZE     TO_CHAR(LAST_ANAL
——————————— ———— ———— ——————————–
RAM_TEST_TAB              2160    1409.73   05:10:51 31/10/18
 SQL> select table_name, round((blocks*8),2) “BLOCK”,
  2  round((num_rows*avg_row_len/1024),2) “SIZE”,
  3  to_char(last_analyzed,’hh:mm:ss dd/mm/yy’)
  4  from dba_tables                                                                                                                                5 where table_name=’RAM_DUMMY’;                                                                                                                                                                                                                                                                         TABLE_NAME                BLOCK    SIZE TO_CHAR(LAST_ANAL
——————————- ————— ———– —————————-
RAM_DUMMY                 2960      1409.73     10:10:24 01/11/18
Drop the Fragmented table.
SQL> drop table ram_dummy purge;
Table dropped.

  • November 2, 2018 | 14 views
  • Comments