INDEX_SKIP 예제

DROP TABLE  APPEND_TEST CASCADE CONSTRAINTS PURGE;

;

CREATE TABLE APPEND_TEST      AS

SELECT 1                      AS SKEWED_DATA

     ,  LPAD(ROWNUM-1,10)     AS ID

     , TRUNC(SQRT(ROWNUM-1))  AS ID_CHAR

     , RPAD('X',50, 'X')      AS FILLER

  FROM ALL_OBJECTS

 WHERE 1=0;

 

 

 CREATE        INDEX APPEND_TEST_01 ON APPEND_TEST(ID_CHAR);

 CREATE UNIQUE INDEX APPEND_TEST_PK ON APPEND_TEST(ID);

 ALTER TABLE APPEND_TEST ADD CONSTRAINT PK_APPEND_TEST PRIMARY KEY (ID);

 

 ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE;

 

 ALTER INDEX APPEND_TEST_PK UNUSABLE;

 ALTER INDEX APPEND_TEST_01 UNUSABLE;

 ALTER TABLE APPEND_TEST DISABLE PRIMARY KEY;

 

 

 

 

 

INSERT /*+ APPEND */

  INTO APPEND_TEST

SELECT 1                     AS SKEWED_DATA

     ,  LPAD(ROWNUM-1,10)    AS ID

     , TRUNC(SQRT(ROWNUM-1)) AS ID_CHAR

     , RPAD('X',50, 'X')     AS FILLER

  FROM ALL_OBJECTS

 WHERE ROWNUM <= 10;

 

SELECT INDEX_NAME

     , UNIQUENESS

     , STATUS

  FROM USER_INDEXES

WHERE TABLE_NAME = UPPER('APPEND_TEST');

TRUNCATE TABLE  APPEND_TEST;

ALTER SESSION SET SKIP_UNUSABLE_INDEXES=FALSE;

 

ALTER TABLE APPEND_TEST ENABLE PRIMARY KEY;

 

 

ALTER INDEX  APPEND_TEST_PK REBUILD;

ALTER INDEX  APPEND_TEST_01 REBUILD