對物件的影響程度 Repair Chained Rows (蟾蜍) < Alter Table Move < Rebuild Table (蟾蜍)

 

對物件的影響程度

Rebuild Table (蟾蜍) > Alter Table Move (reorg) > Repair Chained Rows (蟾蜍)

1. Rebuild Table (蟾蜍) : Create Indexes, Compile Dependencies, Analyze Table

2. Alter Table Move (reorg): Rebuild Indexes, Analyze Table

3. Repair Chained Rows (蟾蜍): Analyze Table

SQL> create table tbl_1224 as select * from employees;

Table created.

SQL> desc tbl_1224;

 Name                                      Null?    Type

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

 EMPLOYEE_ID                                        NUMBER(6)

 FIRST_NAME                                         VARCHAR2(20)

 LAST_NAME                                 NOT NULL VARCHAR2(25)

 EMAIL                                     NOT NULL VARCHAR2(25)

 PHONE_NUMBER                                       VARCHAR2(20)

 HIRE_DATE                                 NOT NULL DATE

 JOB_ID                                    NOT NULL VARCHAR2(10)

 SALARY                                             NUMBER(8,2)

 COMMISSION_PCT                                     NUMBER(2,2)

 MANAGER_ID                                         NUMBER(6)

 DEPARTMENT_ID                                      NUMBER(4)

SQL> CREATE INDEX idx_tbl_1224_01 ON tbl_1224

(EMPLOYEE_ID);  2

Index created.

SQL> select EMAIL from tbl_1224;

EMAIL

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

DOCONNEL

DGRANT

JWHALEN

MHARTSTE

PFAY

SMAVRIS

HBAER

SHIGGINS

WGIETZ

SKING

NKOCHHAR

SQL> CREATE OR REPLACE FORCE VIEW vw_1224

AS

    select EMAIL from tbl_1224;  2    3

View created.

SQL> select * from vw_1224;

EMAIL

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

DOCONNEL

DGRANT

JWHALEN

MHARTSTE

PFAY

SMAVRIS

HBAER

SHIGGINS

WGIETZ

SKING

NKOCHHAR

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and object_type ='VIEW';   2    3

OWNER

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

OBJECT_NAME

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

OBJECT_TYPE         STATUS

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

HR1

VW_1224

VIEW                VALID

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';

  2    3

no rows selected

--Alter Table Move 不會讓 depenedncies 的狀態成為 INVALID

SQL> alter table tbl_1224 move;

Table altered.

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';   2    3

no rows selected

SQL> select

  a.owner       OWNER,

  a.INDEX_NAME  NAME,

  a.TABLE_NAME  TABLE_NAME,

  a.STATUS      STATUS

from all_indexes a

where A.OWNER = 'HR1'

and A.STATUS <> 'VALID';  2    3    4    5    6    7    8

OWNER                          NAME

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

TABLE_NAME                     STATUS

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

HR1                            IDX_TBL_1224_01

TBL_1224                       UNUSABLE

SQL> select * from vw_1224;

EMAIL

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

DOCONNEL

DGRANT

JWHALEN

MHARTSTE

PFAY

SMAVRIS

HBAER

SHIGGINS

WGIETZ

SKING

NKOCHHAR

SQL> ALTER INDEX IDX_TBL_1224_01 REBUILD;

Index altered.

-- TOAD Rebuild Table 會讓 depenedncies 的狀態成為 INVALID

SQL> SET LINESIZE 200

SQL> ALTER TABLE HR1.TBL_1224 RENAME TO TBL_1224_X;

Table altered.

SQL> CREATE TABLE HR1.TBL_1224

(

  EMPLOYEE_ID     NUMBER(6),

  FIRST_NAME      VARCHAR2(20 BYTE),

  LAST_NAME       VARCHAR2(25 BYTE)             NOT NULL,

  EMAIL           VARCHAR2(25 BYTE)             NOT NULL,

  PHONE_NUMBER    VARCHAR2(20 BYTE),

  HIRE_DATE       DATE                          NOT NULL,

  JOB_ID          VARCHAR2(10 BYTE)             NOT NULL,

  SALARY          NUMBER(8,2),

  COMMISSION_PCT  NUMBER(2,2),

  MANAGER_ID      NUMBER(6),

  DEPARTMENT_ID   NUMBER(4)

)  2    3    4    5    6    7    8    9   10   11   12   13   14

 15  ;

Table created.

SQL> INSERT /*+ APPEND */

  2  INTO HR1.TBL_1224 INS_TBL

 (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,

  PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,

  3    4    5    COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)

SELECT

  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,

  PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY,

  COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID

FROM HR1.TBL_1224_X SEL_TBL;

  6    7    8    9   10  COMMIT;

214 rows created.

SQL>

Commit complete.

SQL>

SQL>

SQL>

SQL> DROP INDEX HR1.IDX_TBL_1224_01;

Index dropped.

SQL> CREATE INDEX HR1.IDX_TBL_1224_01 ON HR1.TBL_1224

(EMPLOYEE_ID)  2  ;

Index created.

SQL> select

  a.owner       OWNER,

  a.INDEX_NAME  NAME,

  a.TABLE_NAME  TABLE_NAME,

  a.STATUS      STATUS

from all_indexes a

where A.OWNER = 'HR1'

and A.STATUS <> 'VALID';  2    3    4    5    6    7    8

no rows selected

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';   2    3

OWNER                          OBJECT_NAME                                                                                         OBJECT_TYPE          STATUS

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

HR1                            VW_1224                                                                                             VIEW                 INVALID

SQL> select * from vw_1224;

EMAIL

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

DOCONNEL

DGRANT

JWHALEN

MHARTSTE

PFAY

SMAVRIS

HBAER

SHIGGINS

WGIETZ

SKING

NKOCHHAR

-- 使用到 VIEW VW_1224 之後，則DB會自動COMPILE該物件

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';   2    3

no rows selected

-- 這是手動COMPILE該物件

SQL> ALTER VIEW "HR1"."VW_1224" COMPILE;

View altered.

SQL> select OWNER, object_name, OBJECT_TYPE , status

from dba_objects

where  owner = 'HR1' and status <> 'VALID';   2    3

no rows selected

SQL> select

  a.owner       OWNER,

  a.INDEX_NAME  NAME,

  a.TABLE_NAME  TABLE_NAME,

  a.STATUS      STATUS

from all_indexes a

where A.OWNER = 'HR1'

and A.STATUS <> 'VALID';  2    3    4    5    6    7    8

no rows selected

SQL>