Database/Oracle

[SQL-Oracle]인덱스(INDEX)

sukii 2023. 12. 8. 15:42
반응형

⚫인덱스(INDEX) : 테이블에 저장된 행을 보다 빠르게 검색하기 위한 기능을 제공하는 객체

(테이블을 만들 때, 인덱스까지 같이 만드는 것을 권장)

--컬럼에 인덱스를 부여하면 인덱스 영역을 생성하여 컬럼값 검색 관련 정보를 저장해 컬럼값에 대한 행 검색 속도 증가

--조건식에서 많이 사용하는 컬럼에 인덱스를 부여하며 저장행을 많을수록 인덱스를 사용하여 검색하는 것이 효율적
=> 행이 많을 때 인덱스 사용하면 좋음. 테이블 조인해서 결합된 행이 10만개 이상 정도 되면, 인덱스 여부에 따라 검색 속도 차이가 엄청 남.

=> 그렇다고 인덱스를 모든 컬럼에 주게 되면 오히려 속도가 느려질 수 있으니 주의 ! 


🔘유니크 인덱스(UNIQUE INDEX) : PRIMARY KEY 제약조건이나 UNIQUE 제약조건에 의해 자동 생성되는 인덱스
🔘 비유니크 인덱스(NON-UNIQUE INDEX) : 사용자에 의해 컬럼에 수동으로 인덱스를 부여하여 생성하는 인덱스

 

 

🔘 유니크 인덱스(UNIQUE INDEX)

--USER2 테이블 생성 - 회원번호(숫자형-PRIMARY KEY),회원이름(문자형),이메일(문자형-UNIQUE)
CREATE TABLE USER2(NO NUMBER(4) CONSTRAINT USER2_NO_PK PRIMARY KEY
    ,NAME VARCHAR2(20),EMAIL VARCHAR2(100) CONSTRAINT USER2_EMAIL_UK UNIQUE);
    
--USER2 테이블의 제약조건 확인
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='USER2';

--USER2 테이블의 컬럼에 부여된 인덱스 확인 
--USER_INDEXES : 인덱스 정보를 제공하는 딕셔너리, USER_IND_COLUMNS : 컬럼에 설정된 인덱스 정보를 제공하는 딕셔너리
--컬럼에 부여된 PRIMARY KEY 제약조건이나 UNIQUE 제약조건에 의해 유니크 인덱스 자동 생성 - 인덱스의 이름은 제약조건의 이름과 동일
SELECT C.INDEX_NAME,COLUMN_NAME,UNIQUENESS FROM USER_INDEXES I JOIN USER_IND_COLUMNS C
    ON I.INDEX_NAME=C.INDEX_NAME WHERE C.TABLE_NAME='USER2';

▶️인덱스를 따로 만들지 않았지만, PK 제약조건이 부여된 NO 컬럼은 인덱스가 자동으로 생성되어 있음.

 

 

🔘 비유니크 인덱스(NON-UNIQUE INDEX)

--인덱스 생성 - 비유니크 인덱스(NON-UNIQUE INDEX)
--형식) CREATE INDEX 인덱스명 ON 테이블명(컬럼명)

--USER2 테이블의 NAME 컬럼을 참조하는 비유니크 인덱스를 생성
CREATE INDEX USER2_NAME_INDEX ON USER2(NAME);

--USER2 테이블의 인덱스 확인
SELECT C.INDEX_NAME,COLUMN_NAME,UNIQUENESS FROM USER_INDEXES I JOIN USER_IND_COLUMNS C
    ON I.INDEX_NAME=C.INDEX_NAME WHERE C.TABLE_NAME='USER2';

▶️USER2_NAME_INDEX 이라는 NAME 컬럼을 참조하는 인덱스를 수동으로 만들어줌.

 

🔘 비유니크 인덱스 삭제

--인덱스 삭제 - 비유니크 인덱스(NON-UNIQUE INDEX)
--형식) DROP INDEX 인덱스명

--USER2 테이블의 NAME 컬럼을 참조하는 인덱스 삭제
DROP INDEX USER2_NAME_INDEX;
SELECT C.INDEX_NAME,COLUMN_NAME,UNIQUENESS FROM USER_INDEXES I JOIN USER_IND_COLUMNS C
    ON I.INDEX_NAME=C.INDEX_NAME WHERE C.TABLE_NAME='USER2';

--USER2 테이블의 EMAIL 컬럼을 참조하는 인덱스 삭제
--PRIMARY KEY 제약조건이나 UNIQUE 제약조건에 의해 생성된 유니크 인덱스는 DROP INDEX 명령으로 삭제하면 에러 발생
DROP INDEX USER2_EMAIL_UK;
--유니크 인덱스는 컬럼에 부여된 제약조건을 회수하면 자동 삭제
ALTER TABLE USER2 DROP CONSTRAINT USER2_EMAIL_UK;--제약조건 회수
SELECT C.INDEX_NAME,COLUMN_NAME,UNIQUENESS FROM USER_INDEXES I JOIN USER_IND_COLUMNS C
    ON I.INDEX_NAME=C.INDEX_NAME WHERE C.TABLE_NAME='USER2';

▶️비유니크 인덱스인 USER2_NAME_INDEX 이라는 인덱스는 DROP 명령어를 이용해 삭제함.

그러나 제약조건에 의해 생성된 유니크 인덱스는 DROP 명령을 사용해도 삭제할 수 없음. 삭제하고 싶다면 제약조건을 회수하면 인덱스도 함께 삭제됨.

반응형