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