[oracle] index unusable DML 테스트

DBMS: oracle 19.3

 

- unusable index를 사용하지 않으면 조회 및 DML 가능
- unique index가 unusable인 경우
 : insert 불가능
 : 인덱스에 해당하는 컬럼 아닌 경우 update 가능
- 세션레벨에서 unusable index를 사용 안 하도록 설정
 : ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE ;

 

[목차여기]

 

non-unique index

SQL> SELECT count(*) FROM sk.t1 ;

  COUNT(*)
----------
     71968

SQL> CREATE INDEX sk.t1_ix1 ON sk.t1(owner,object_name) ;

Index created.

SQL> SELECT owner, index_name, status
FROM dba_indexes 
WHERE owner = 'SK'
;

OWNER			       INDEX_NAME		      STATUS
------------------------------ ------------------------------ ------------------------
SK			       T1_IX1			      VALID

-- 인덱스 unusable
SQL> ALTER INDEX sk.t1_ix1 unusable ;

Index altered.

SQL> SELECT owner, index_name, status
FROM dba_indexes 
WHERE owner = 'SK'
;

OWNER			       INDEX_NAME		      STATUS
------------------------------ ------------------------------ ------------------------
SK			       T1_IX1			      UNUSABLE

 

DML 테스트

조회 시 해당 인덱스를 사용하면 ORA-01502 에러 발생 합니다.

INSERT문 수행 가능 하며 UPDATE문 수행 시 해당 인덱스에 포함된 컬럼 변경도 가능합니다.(조회 시 해당 인덱스를 사용하면 에러발생)

-- table full scan 조회 시 가능
SQL> SELECT /*+ full(a) */ count(*) 
FROM sk.t1 a WHERE owner = 'SK' 
; 

  COUNT(*)
----------
     71968

-- 해당 인덱스 사용 조회 시 에러
SQL> SELECT /*+ index(a t1_ix1) */ count(*) 
FROM sk.t1 a WHERE owner = 'SK' 
;
SELECT /*+ index(a t1_ix1) */ count(*)
*
ERROR at line 1:
ORA-01502: index 'SK.T1_IX1' or partition of such index is in unusable state

-- insert 가능
SQL> INSERT INTO sk.t1 SELECT * FROM dba_objects WHERE rownum <= 10 ;

10 rows created.

SQL> SELECT count(*) FROM sk.t1 ;

  COUNT(*)
----------
     71978

SQL> commit ;

Commit complete.

SQL> SELECT count(*)
FROM sk.t1 a WHERE owner = 'SK' 
;

  COUNT(*)
----------
	53

-- 해당 인덱스 사용 시 불가능
SQL> UPDATE /*+ index(a t1_ix1) */ sk.t1 a 
SET owner = 'SK1'
WHERE owner = 'SK'
;
  2    3    4  UPDATE /*+ index(a t1_ix1) */ sk.t1 a
*
ERROR at line 1:
ORA-01502: index 'SK.T1_IX1' or partition of such index is in unusable state

-- table full scan 시 가능
-- 인덱스에 해당하는 컬럼이여도 변경 가능
UPDATE /*+ full(a) */ sk.t1 a 
SET owner = 'SK1'
WHERE owner = 'SK'
;
SQL>   2    3    4  
53 rows updated.

SQL> 
SQL> rollback ;

Rollback complete.


SQL> commit ;

Commit complete.

SQL> SELECT count(*)
FROM sk.t1 a WHERE owner = 'SK1' 
;  

  COUNT(*)
----------
	53

SQL> SELECT owner, index_name, status
FROM dba_indexes 
WHERE owner = 'SK'
;

OWNER			       INDEX_NAME		      STATUS
------------------------------ ------------------------------ ------------------------
SK			       T1_IX1			      UNUSABLE

반응형

unique index

위에서 테스트한 테이블 DROP 후 재생성 및 유니크 인덱스를 생성하여 테스트합니다

SQL> SELECT owner, index_name, status
FROM dba_indexes 
WHERE owner = 'SK'
;

OWNER			       INDEX_NAME		      STATUS
------------------------------ ------------------------------ ------------------------
SK			       T1_IX1			      UNUSABLE

SQL> drop table sk.t1 ;

Table dropped.

SQL> CREATE TABLE sk.t1 AS SELECT * FROM dba_objects ;

Table created.

-- 유니크 인덱스 생성
SQL> CREATE UNIQUE INDEX sk.t1_ix1 ON sk.t1(owner,object_name, object_id) ;

Index created.

SQL> SELECT owner, index_name, uniqueness, status
FROM dba_indexes 
WHERE owner = 'SK'
;
 
OWNER			       INDEX_NAME		      UNIQUENESS		  STATUS
------------------------------ ------------------------------ --------------------------- ------------------------
SK			       T1_IX1			      UNIQUE			  VALID

SQL> ALTER INDEX sk.t1_ix1 unusable ;

Index altered.

SQL> SELECT owner, index_name, uniqueness, status
FROM dba_indexes 
WHERE owner = 'SK'
;

OWNER			       INDEX_NAME		      UNIQUENESS		  STATUS
------------------------------ ------------------------------ --------------------------- ------------------------
SK			       T1_IX1			      UNIQUE			  UNUSABLE

 

유니크 인덱스에 해당하지 않는 컬럼에 대한 업데이트는 가능합니다

-- insert 에러 발생
SQL> INSERT INTO sk.t1 SELECT * FROM dba_objects WHERE rownum <= 10 ;
INSERT INTO sk.t1 SELECT * FROM dba_objects WHERE rownum <= 10
*
ERROR at line 1:
ORA-01502: index 'SK.T1_IX1' or partition of such index is in unusable state

-- update 에러 발생
SQL> UPDATE sk.t1 
SET owner = 'SK1'
WHERE owner = 'SK'
;  
UPDATE sk.t1
*
ERROR at line 1:
ORA-01502: index 'SK.T1_IX1' or partition of such index is in unusable state

-- full scan 힌트 update 불가능
SQL> UPDATE /*+ full(a) */ sk.t1 a 
SET owner = 'SK1'
WHERE owner = 'SK'
;
UPDATE /*+ full(a) */ sk.t1 a
*
ERROR at line 1:
ORA-01502: index 'SK.T1_IX1' or partition of such index is in unusable state

-- 인덱스에 없는 컬럼 update 가능
SQL> UPDATE sk.t1 
SET object_type = 'TTT'
WHERE object_type = 'TABLE'
;

2248 rows updated.

SQL> commit ;

Commit complete.

SQL> select count(*) from sk.t1 where object_type = 'TTT' ;

  COUNT(*)
----------
      2248

SQL>