重建表

该方案只适合非常大的表，比如好几百上千万，而且窗口时间较少的情况。

考虑了原表包含非空约束、主键、唯一约束、外键约束的情况。

约束与索引的绑定关系会失效。

可以事先把代码写好再统一起执行。

一、插入数据

创建表结构

拿原表的表结构DDL的语句，包括表和列的注释，但不包括约束和索引，然后把表名替换掉，创建新的表。

插入数据

可以为查询指定并行提升性能。

alter session enable parallel dml;

INSERT /*+ append parallel(8) */ INTO test_parent_new SELECT /*+ parallel(8) */ * FROM test_parent t;

COMMIT;

二、创建索引

使用下面的语句获取原表索引的DDL，然后把表名和索引名称替换掉。

建议只在索引名称后面加个统一的后缀，如果放在引号里面要用大号，方便修改回来。

为了提升创建性能，要指定并行度，并且打开多个会话并行创建。

WITH w1 AS

 (SELECT t.table_owner,

         t.table_name,                                                                                           

         t.owner AS index_owner,

         t.index_name,

         t.degree,

         to_char(dbms_metadata.get_ddl('INDEX', t.index_name, t.owner)) AS ddl_text,

         4AS num_value --指定并行数

    FROM dba_indexes t

   WHERE t.table_owner = 'SYS'

     AND t.table_name = 'TEST_PARENT'),

w2 AS

 (SELECT table_owner,

         table_name,

         index_owner,

         index_name,

         degree,

         ddl_text,

         num_value,

         instr(ddl_text, 'PARALLEL') paralle_pos, --目的是去掉自带的PARALLEL

         (CASE

           WHEN instr(ddl_text, 'PARALLEL') > 0 THEN

           --取出PARALLEL旁边的并行值

            regexp_substr(substr(ddl_text, instr(ddl_text, 'PARALLEL')),

                          '[^ ]+',

                          1,

                          2)

         END) AS paralle_count --目的是去掉自带的PARALLEL

    FROM w1),

w3 AS

 (SELECT table_owner,

         table_name,

         index_owner,

         index_name,

         degree,

         num_value,

         (CASE

           WHEN paralle_pos > 0 THEN

           --如果PARALLEL旁边是数字表示是并行值，则把PARALLEL+并行值一起去掉，否则只要去掉PARALLEL

            (CASE

              WHEN regexp_like(paralle_count, '^[0-9]+\.?[0-9]*$') THEN

               REPLACE(ddl_text, 'PARALLEL ' || paralle_count, '')

              ELSE

               REPLACE(ddl_text, 'PARALLEL ', '')

            END)

           ELSE

            ddl_text

         END) AS ddl_text

    FROM w2)

SELECT table_owner,

       table_name,

       index_owner,

       index_name,

       degree,

       ddl_text || ' PARALLEL ' || num_value || ' NOLOGGING' || ';' AS ddl_text,

       SYSDATE

  FROM w3;

三、创建约束

使用下面的语句获取原表的约束DDL，然后把表名、约束、使用的索引名称替换掉，不过要注意这样会导致约束与索引的绑定关系失效。

建议只在约束名称后面加个统一的后缀，如果放在引号里面要用大号，方便修改回来。

DECLARE

  v_table_owner VARCHAR2(100) := 'SYS';

  v_table_name  VARCHAR2(100) := 'TEST_PARENT';

 

  v_ddl_text  VARCHAR2(10000);

  v_count_11g NUMBER;

BEGIN

  dbms_metadata.set_transform_param(dbms_metadata.session_transform,

                                    'SQLTERMINATOR',

                                    TRUE);

  dbms_metadata.set_transform_param(dbms_metadata.session_transform,

                                    'STORAGE',

                                    FALSE);

  dbms_metadata.set_transform_param(dbms_metadata.session_transform,

                                    'TABLESPACE',

                                    FALSE);

  dbms_metadata.set_transform_param(dbms_metadata.session_transform,

                                    'SEGMENT_ATTRIBUTES',

                                    FALSE);

 

  SELECT COUNT(1)

    INTO v_count_11g

    FROM v$version t

   WHERE t.banner LIKE '%11g%';

 

  FOR v IN (WITH w1 AS

               (SELECT t.owner,

                      t.table_name,

                      t.constraint_name,

                      t.constraint_type,

                      t.index_owner,

                      t.index_name,

                      t.generated

                 FROM dba_constraints t

                WHERE t.owner = v_table_owner

                  AND t.table_name = v_table_name

                  AND t.constraint_type IN ('P', 'U', 'R'))

              SELECT t.owner,

                     t.table_name,

                     t.constraint_name,

                     t.constraint_type,

                     t.index_owner,

                     t.index_name,

                     REPLACE(REPLACE(to_char(dbms_metadata.get_ddl(decode(t.constraint_type,

                                                                          'P',

                                                                          'CONSTRAINT',

                                                                          'U',

                                                                          'CONSTRAINT',

                                                                          'R',

                                                                          'REF_CONSTRAINT'),

                                                                   t.constraint_name,

                                                                   t.owner)),

                                     ';',

                                     ''),

                             'NOVALIDATE',

                             '') || ' NOVALIDATE' || ';' AS ddl_text, --添加NOVALIDATE避免对已有数据做校验

                     t.generated,

                     SYSDATE

                FROM w1 t) LOOP

    v_ddl_text := v.ddl_text;

 

    IF v.constraint_type IN ('P', 'U') THEN

      IF v_count_11g > 0 THEN

        v_ddl_text := REPLACE(v.ddl_text,

                              'ENABLE',

                              'USING INDEX "' || v.index_owner || '"."' ||

                              v.index_name || '"' || ' ENABLE');

      ELSE

        v_ddl_text := REPLACE(v.ddl_text,

                              'USING INDEX',

                              'USING INDEX "' || v.index_owner || '"."' ||

                              v.index_name || '"');

      END IF;

    END IF;

 

    dbms_output.put_line(v_ddl_text);

  END LOOP;

 

END;

四、重命名表

alter table TEST_PARENT rename to TEST_PARENT_OLD;

alter table TEST_PARENT_NEW rename to TEST_PARENT;

五、修改索引

有些地方可能使用hint固化了索引，为了避免hint失效，所以要使新表仍然使用原来的索引名称。

 修改原表的索引名称

将原表的索引改名，带上后缀0，获取改名语句：

SELECT 'ALTER INDEX ' || t.owner || '.' || t.index_name || ' RENAME TO ' ||

       t.index_name || 'O;' AS old_index

  FROM dba_indexes t

 WHERE t.table_owner = 'SYS'

   AND t.table_name = 'TEST_PARENT_OLD';

修改新表的索引名称

将新表的索引改名，获取改名语句，要将后缀去掉：

SELECT 'ALTER INDEX ' || t.owner || '.' || t.index_name || ' RENAME TO ' ||

       t.index_name || ';' AS old_index

  FROM dba_indexes t

 WHERE t.table_owner = 'SYS'

   AND t.table_name = 'TEST_PARENT';

恢复并行度

上面的查询出的degree是原来的并行度，使用下面的语句生成恢复语句：

SELECT 'alter index ' || t.owner || '.' || t.index_name || ' parallel 1;'

  FROM dba_indexes t

 WHERE t.table_owner = 'SYS'

   AND t.table_name = 'TEST_PARENT';