wif LiNoUz/Oracle,Sql
INDEX_SKIP 예제
크라소니
2015. 5. 26. 13:20
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