index(==색인)는 예를 들어 설명하면 아주 두꺼운 책 뒤에 나오는 "찾아보기" 와 같은 기능을 하는 것이다.
"찾아보기" 의 특징은 정렬되어 있는 것인데 index(==색인) 에 저장된 데이터도 정렬되어 저장되어 있다는 것이 특징이다.
index(==색인)를 생성해서 사용하는 이유는 where 절이 있는 select 명령문의 >> 속도를 향상 시키기 위함 <<이다.
index(==색인)은 어떤 컬럼에 만들어야 할까?
where가 없다고 가정해보자. 그렇다면 몽땅 다 읽어야 할 것이다.(책에 인덱스가 없으면 다 찾아서 읽어야하는 것과 같음)
1. !! where 절에서 자주 사용되어진 컬럼에 만들어야 한다. !!
2. 선택도(selectivity)가 높은 컬럼에 만들어야 한다.
※ 고유한 데이터일수록 선택도(selectivity)가 높아진다.
예: 성별 컬럼 --> 선택도(selectivity)가 아주 낮다. 왜냐하면 수많은 사람 중 남자 아니면 여자 하나만 골라야 하므로 선택의 여지가 아주 낮다.
학번 --> 선택도(selectivity)가 아주 좋다. 왜냐하면 학번은 다양하고 고유하므로 골라야할 대상이 아주 많으므로 선택도가 높은 것이다.
3. 카디널리티(cardinality)가 높은 컬럼에 만들어야 한다.
※ 카디널리티(cardinality)의 사전적인 뜻은 집합원의 갯수를 뜻하는 것으로서,
카디널리티(cardinality)가 높다라는 것은 중복도가 낮아 고유한 데이터일수록 카디널리티(cardinality)가 상대적으로 높다 라는 것이다.
카디널리티(cardinality)가 낮다라는 것은 중복도가 높아 중복된 데이터가 많을수록 카디널리티(cardinality)가 상대적으로 낮다 라는 것이다.
=> 고유한 데이터 : 카디널리티가 상대적 높다
중복된 데이터가 많음 : 카디널리티가 상대적 낮음
카디널리티(cardinality)는 "상대적인 개념" 이다.
예를들어, 주민등록번호 같은 경우는 중복되는 값이 없으므로 카디널리티(cardinality)가 높다고 할 수 있다.
이에 비해 성명같은 경우는 "주민등록번호에 비해" 중복되는 값이 많으므로, 성명은 "주민등록번호에 비해" 카디널리티가 낮다고 할 수 있다.
이와같이 카디널리티(cardinality)는 상대적인 개념으로 이해해야 한다.
>>
[결론]
선택도가높음 카디널리티가높음
인덱스는 고유한 데이터를 가지고, 중복도가 타 컬럼에 비해 낮고, where절에서 자주 사용되어지는 컬럼에 만들자.
1. unique 한 index 생성하기
어떤 컬럼에 unique 한 index 를 생성하면 그 컬럼에 들어오는 값은 중복된 값은 들어올 수 없으며 오로지 고유한 값만 들어오게 된다.
unique 한 index 가 뒤에 나오는 non-unique 한 index 보다 검색속도가 조금 더 빠르다.
1) 문법
[문법]
create unique index 인덱스명
on 해당테이블명(컬럼명 asc|desc);
-- 인덱스를 오름차순? 내림차순? 둘중 선택(디폴트:오름차순)
--테이블에 대한 인덱스를 만드는 것이다. 2) 실습
create unique index idx_tbl_student_1_hakbun -- 인덱스명도 고유해야 한다.
on tbl_student_1(hakbun); -- on tbl_student_1(hakbun asc); 와 동일하다.
--on tbl_student_1(hakbun asc);
--on tbl_student_1(hakbun desc);
-- Index IDX_TBL_STUDENT_1_HAKBUN이(가) 생성되었습니다.
insert into tbl_student_1(hakbun, name, email, address) values('1', '일미자', 'ilmj@naver.com', '서울시 강동구');
-- 1 행 이(가) 삽입되었습니다.
insert into tbl_student_1(hakbun, name, email, address) values('1', '이미자', 'twomj@naver.com', '서울시 강서구');
-- ORA-00001: 무결성 제약 조건(HR.IDX_TBL_STUDENT_1_HAKBUN)에 위배됩니다
-- unique index 이기 때문에, hakbun 컬럼이 PK/UK 처럼 고유한 값이어야 한다.
insert into tbl_student_1(hakbun, name, email, address) values('2', '이미자', 'twomj@naver.com', '서울시 강서구');
-- 1 행 이(가) 삽입되었습니다.
commit;3) 테이블에 생성되어진 index 조회하기
select *
from user_indexes
where table_name = 'TBL_STUDENT_1'; -- 오라클은 디폴트가 대문자인거 잊지 말기!
-- UNIQUENESS : UNIQUE
-- 컬럼명이 안 나와서 인덱스 컬럼을 조회 해보기로 한다.
select *
from user_ind_columns
where table_name = 'TBL_STUDENT_1';
--두개를 합쳐서 조인해서 본다(제약조건 조인처럼)
select
*
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
WHERE A.table_name = 'TBL_STUDENT_1'
-- 인덱스 네임, 유니크한지, 칼럼명, 어떻게 정렬되었는지 조회
select
A.index_name, A.uniqueness, B.column_name, B.descend
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
WHERE A.table_name = 'TBL_STUDENT_1'2. non-unique 한 index 생성하기
1) 문법
어떤 컬럼에 non-unique 한 index 생성하면 그 컬럼에 들어오는 값은 중복된 값이 들어올 수 있다는 것이다.
non-unique 한 index 는 unique 한 index 보다 검색속도가 다소 늦은 편이다.
[문법]
create index 인덱스명
on 해당테이블명(컬럼명 asc|desc);
2) 실습
--name 컬럼에 non unique 인덱스 만들기 : 동명이인이 있기 때문에 non-unique로.
create index idx_tbl_student_1_name
on tbl_student_1(name);
-- Index IDX_TBL_STUDENT_1_NAME이(가) 생성되었습니다.
insert into tbl_student_1(hakbun, name, email, address) values('3', '삼미자', 'three@naver.com', '서울시 강서구');
-- 1 행 이(가) 삽입되었습니다.
insert into tbl_student_1(hakbun, name, email, address) values('4', '삼미자', 'sam@naver.com', '서울시 강남구');
-- 1 행 이(가) 삽입되었습니다. : 동명이인이어도 정상적으로 삽입이 된다.
commit;
--커밋 완료.
select *
from tbl_student_1; -- 전체 확인
select
A.index_name, A.uniqueness, B.column_name, B.descend
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
WHERE A.table_name = 'TBL_STUDENT_1' -- TBL_STUDENT_1테이블에 생성되어있는 인덱스 이름, 유니크한지 여부, 칼럼명, 정렬여부를 알려준다.
/*
----------------------------------------------------------------------
index_name uniqueness column_name descend
----------------------------------------------------------------------
IDX_TBL_STUDENT_1_HAKBUN UNIQUE HAKBUN ASC
IDX_TBL_STUDENT_1_NAME NONUNIQUE NAME ASC
----------------------------------------------------------------------
*/
select *
from tbl_student_1
where hakbun = '2'; --> unique한 인덱스 IDX_TBL_STUDENT_1_HAKBUN 를 사용하여 빠르게 조회해옴.
select *
from tbl_student_1
where name = '이미자'; --> non-unique한 인덱스 IDX_TBL_STUDENT_1_NAME 를 사용하여 빠르게 조회해옴.
select *
from tbl_student_1
where address = '서울시 강동구'; --> address 컬럼에는 인덱스가 없으므로 tbl_student_1 테이블에 있는 모든 데이터를 전부 조회해서
-- address 컬럼의 값이 '서울시 강동구' 인 데이터를 가져온다.
-- 이와 같이 인덱스를 사용하지 않고 데이터를 조회해올 때를 Table Full-scan(인덱스를 사용하지 않고 테이블 전체 조회) 이라고 부른다.
-- Table Full-scan(인덱스를 사용하지 않고 테이블 전체 조회)이 속도가 >>가장 느린 것<<이다.
delete from tbl_student_1;
-- 4개 행 이(가) 삭제되었습니다.
commit;
-- 커밋 완료.3) 데이터 다량으로 넣어보기
시퀀스 + loop를 사용해서 다량으로 넣어본다.
----성능 속도 테스트를 위해 데이터를 막 넣어본다. 인덱스는 데이터가 많아야지 효과가 있다.
create sequence seq_tbl_student_1
start with 1
increment by 1 -- 증가치 값 1 2 3 4 5 ......
nomaxvalue
nominvalue
nocycle -- 반복이 없는 직진.
nocache;
-- Sequence SEQ_TBL_STUDENT_1이(가) 생성되었습니다.
-- 채번해온다 라고 부름.. 학번이 4라면 이메일의 sam4 / 학번이 5라면 이메일이 sam5 이런식으로 넣고싶다 -> 시퀀스 사용
-- 시퀀스의 번호를 따와서 따온 번호를 학번/이메일에 넣어줌. => 이렇게 시퀀스의 번호를 따오는것을 채번해온다라고 함 !!!! ★★★★★★
-- '20240305-1' '20240305-2' '20240305-3' '20240305-4' 학번을 '20240305-10000' 까지 하고 싶다.
-- 쇼핑몰 영수증 번호 출력하는 데에도 이런 방식을 사용한다.
declare
v_today varchar2(8);
v_seq number;
v_cnt number := 0;
begin
-- 날짜 가져오기
select to_char(sysdate,'yyyymmdd') into v_today
from dual;
loop
v_cnt := v_cnt + 1;
EXIT WHEN v_cnt > 10000;
-- 채번하기
select seq_tbl_student_1.nextval into v_seq -- 채번해온값을 v_seq에 넣는다. 시퀀스 값 증가 => NEXTVAL
from dual;
insert into tbl_student_1(hakbun, name, email, address)
values(v_today || '-' || v_seq, '이순신' || v_seq, 'leess' || v_seq || '@gmail.com', '서울시 마포구 월드컵로 ' || v_seq);
end loop;
end;
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
commit;
-- 커밋 완료
select *
from tbl_student_1
order by hakbun asc;
select count(*)
from tbl_student_1;
-- 시퀀스 seq_tbl_student_1 이 마지막으로 사용되어진 값(= 현재 시퀀스값) 을 알아보려고 한다.
select seq_tbl_student_1.currval AS 최근에사용한시퀀스값
from dual;
3. SQL*Developer 에서 Plan(실행계획) 확인하는 방법
select 문이 실행될 때 인덱스를 사용하여 데이터를 얻어오는지 인덱스를 사용하지 않고
Table Full Scan 하여 얻어오는지 알아봐야 한다.
이럴때 사용하는 것이 SQL Plan(실행계획)이다.
SQL*Developer 에서는 "SQL편집창(SQL 워크시트)"에 Plan(실행계획) 과 Trace(자동추적) 메뉴가 상단에 있다.
Plan(실행계획) 과 Trace(자동추적) 의 차이는,
Plan(실행계획) 은 SQL을 실행하기 "전에" Oracle Optimizer(옵티마이저, 최적화기)가 SQL을 어떻게 실행할지를 미리 알려주는 것이고,
Trace(자동추적) 는 SQL을 실행해보고, Oracle Optimizer(옵티마이저, 최적화기)가 SQL을 어떻게 실행했는지 그 결과를 알려주는 것이다.
그러므로, 정확도로 말하자면, Trace(자동추적)가 Plan(실행계획) 보다 훨씬 정확한 것이다.
Plan(실행계획) 은 말그대로 계획이라서 Oracle Optimizer가 계획은 그렇게 세우긴 했으나
실제 실행할때는 여러가지 이유로 다르게 실행할 수도 있기 때문이다.
그래서 Trace(자동추적)가 정확하기는 하나 Trace(자동추적)는 한번 실행해봐야 하는것이라서
시간이 오래 걸리는 SQL인 경우에는 한참 기다려야 하는 단점이 있기는 하다.
▶실행해야할 SQL문을 블럭으로 잡은 후에
"SQL 워크시트" 의 상단 아이콘들중에 3번째 아이콘( 계획 설명... (F10) )을 클릭하면 현재 SQL의 Plan(실행계획)을 아래에 보여준다.
COST(비용)의 값이 적을 수록 속도가 빠른 것이다.
형광펜 칠한 부분이다.
1. 실습
select *
from tbl_student_1
where hakbun = '20240305-6789'; -- unique 인덱스, unique index idx_tbl_student_1_hakbun 을 사용해서 빠르게 조회해옴.
select *
from tbl_student_1
where name = '이순신5783'; -- non-unique 인덱스, idx_tbl_student_1_name 을 사용해서 빠르게 조회해옴. (unique보다는 약간 느리지만 거의 차이가 없다.)
select *
from tbl_student_1
where address = '서울시 마포구 월드컵로 3987';
-- 인덱스가 없는 address 컬럼 : cost가 68로 확 올라갔다.
--> address 컬럼에는 인덱스가 없으므로 tbl_student_1 테이블에 있는 모든 데이터를 조회해서
-- address 컬럼의 값이 '서울시 마포구 월드컵로 3987' 인 데이터를 가져온다.
-- 이와 같이 인덱스를 사용하지 않고 데이터를 조회해올 때를 Table Full-scan(인덱스를 사용하지 않고 테이블 전체 조회) 이라고 부른다.
-- Table Full-scan(인덱스를 사용하지 않고 테이블 전체 조회)이 속도가 가장 느린 것이다.
select *
from tbl_student_1
where email = 'leess2654@gmail.com';
--> address 컬럼에는 인덱스가 없으므로 Table Full-scan(인덱스를 사용하지 않고 테이블 전체 조회) 하여 읽어온다.
-----------------------------------------------------------------------------------------------------------
-- *** Trace(자동추적)을 하기 위해서는 SYS 또는 SYSTEM 으로 부터 권한을 부여 받은 후 HR은 재접속을 해야 한다. *** -- 위에 데이터베이스를 sys로 전환
show user;
-- USER이(가) "SYS"입니다.
grant select_catalog_role to hr;
-- Grant을(를) 성공했습니다.
grant select any dictionary to hr;
-- Grant을(를) 성공했습니다.
----------------------------------------------------------------------------
-- 껐다 킨후 접속에 가서 local_hr 에서 오른쪽 클릭 => 재접속을 클릭한다.
show user;
-- USER이(가) "HR"입니다.
/*
실행해야할 SQL문을 블럭으로 잡은 후에
"SQL 워크시트" 의 상단 아이콘들중에 4번째 아이콘( 자동 추적... (F6) )을 클릭하면 현재 SQL의 Trace(자동추적)을 아래에 보여준다.
Trace(자동추적)을 하면 Plan(실행계획) 도 나오고, 동시에 아래쪽에 통계정보도 같이 나온다.
오른쪽에 Plan(실행계획)에서는 보이지 않던 LAST_CR_BUFFER_GETS 와 LAST_ELAPSED_TIME 컬럼이 나온다.
LAST_CR_BUFFER_GETS 는 SQL을 실행하면서 각 단계에서 읽어온 블록(Block) 갯수를 말하는 것이고,
LAST_ELAPSED_TIME 은 경과시간 정보이다.(경과시간이 짧아야 good)
즉, 이 정보를 통해서 어느 구간에서 시간이 많이 걸렸는지를 확인할 수 있으므로, 이 부분의 값이 적게 나오도록 SQL 튜닝을 하게 된다. (값이 크면 SQL 튜닝에 들어가야)
*/
select *
from tbl_student_1
where hakbun = '20240305-6789'; --unique
select *
from tbl_student_1
where name = '이순신5783'; -- notunique
select *
from tbl_student_1
where address = '서울시 마포구 월드컵로 3987'; -- 테이블 full scan
select *
from tbl_student_1
where email = 'leess2654@gmail.com'; -- 테이블 full scan
-- 책이 두꺼우면 인덱스가 도움이 되는 것처럼, 데이터가 많을 때 인덱스가 도움이 된다.★☆ 인덱스 만들때 주의사항 ☆★
1) DML이 빈번하게 일어나는 테이블에 index 생성하지 않는다
*** DML(insert, update, delete)이 빈번하게 발생하는 테이블에 index가 생성되어 있으면
DML(insert, update, delete) 작업으로 인해 Index 에 나쁜 결과를 초래하므로
index 가 많다고 해서 결코 좋은 것이 아니기에 테이블당 index 의 개수는 최소한의 개수로 만드는 것이 좋다.
학번, 이름 컬럼에 인덱스가 있어서 빠른 조회가 가능했다. 하지만 이메일, 주소는 인덱스가 없어서 느린 조회가 되었다.
모든 걸 인덱스로 하면 빨라지는 거 아닌가? 하겠지만, DML 때문에 꼭 필요한 컬럼에만 최소한의 갯수로 만들어야 한다.
인명사전으로 치면 인덱스 100페이지에 허성심이 있는데 서은우가 들어오면 허 자로 asc 오름차순을 다시 다 해서 인덱스에 끼워넣으려면
뒤로 밀리니까 허성심이 101페이지가 된다. 그러면서 쭉 데이터가 밀리게 된다.
만약 인덱스가 없는 상태의 칼럼이라면, 테이블에 데이터를 넣어도 인덱스에 부하가 안 걸리므로 괜찮다.
*쇼핑몰으로 치면 새물건은 계속 insert하니까 인덱스 안 만드는 것이 좋다.
제품 이름을 검색하면-> 똑같은 것들이 많이 나오니까(이름같고 가격다름) 인덱스를 설정하면 인덱스측에서 부하가 걸리게 된다(insert 많아서)
*결론: 꼭 필요한 컬럼에(DML 적은 곳에) 인덱스 만들자 2) 인덱스 쪼개짐 현상?
< Index Split(인덱스 쪼개짐) 현상>
index 가 생성되어진 테이블에 insert 를 하면 Index Split(인덱스 쪼개짐) 가 발생하므로 index 가 없을시 보다 insert 의 속도가 떨어지게 된다.
그러므로 index 가 많다고 결코 좋은 것이 아니므로 최소한의 개수로 index 를 만드는 것이 좋다.
Index Split(인덱스 쪼개짐)란 Index 의 block(블럭)들이 1개에서 2개로 나뉘어지는 현상을 말한다.
Index 는 정렬이 되어 저장되기 때문에 Index 의 마지막 부분에 추가되는 것이 아니라,
정렬로 인해 중간 자리에 끼워들어가는 현상이 발생하여 인덱스 쪼개짐 현상이 나타난다. 3) 인덱스가 생성되어진 테이블을 삭제/업데이트하게 되면
*아래 설명: 허성심이 테이블에 있다가 나갔음 -> 인덱스에 필요가 없음-> 그럼 또 내가 없어진만큼 내 성 뒤에 있는사람들(오름차순이라고 쳤을떄)다 앞당겨옴.
*혹은 개명함(허심성으로) -> 똑같은 사람이지만 이름이 바뀌었기 떄문에 1)칼럼에서 한번 변경하고 2) 인덱스에서 또 변경을 들어가줘야 한다.
*이러한 이유로 모든 테이블의 모든 컬럼에 인덱스를 만들면 참사가 일어나게 된다...
index 가 생성되어진 테이블에 delete 를 하면 테이블의 데이터는 삭제가 되어지지만
Index 자리에는 데이터는 삭제되지 않고 사용을 안한다는 표시만 하게 된다.
그래서 10만 건이 들어있던 테이블에 9만건의 데이터를 delete 를 하면 테이블에는 데이터가 삭제되어지지만
Index 자리에는 10만 건의 정보가 그대로 있고 1만건만 사용하고 9만건은 사용되지 않은채로 되어있기에
사용하지 않는 9만건의 Index 정보로 인해서 index를 사용해서 select를 해올 때 index 검색속도가 떨어지게 된다.
이러한 경우 Index Rebuild 작업을 해주어 사용하지 않는 9만건의 index 정보를 삭제해주어야만
select를 해올 때 index 검색속도가 빨라지게 된다.
index 가 생성되어진 테이블에 update 를 하면 테이블의 데이터는 "수정" 되어지지만
Index 는 "수정" 이라는 작업은 없고 index 를 delete 를 하고 새로이 insert 를 해준다.
그러므로 index 를 delete 할 때 발생하는 단점 및 index 를 insert 를 할 때 발생하는 Index Split(인덱스 쪼개짐) 가 발생하므로
Index 에는 최악의 상황을 맞게 된다.
이로 인해 테이블의 데이터를 update를 빈번하게 발생시켜 버리면 select를 해올 때 index 검색속도가 현저히 느려지게 된다.
이러한 경우도 select를 해올 때 index 검색속도가 빨라지게끔 Index Rebuild 작업을 해주어야 한다. 4) Index(인덱스)의 상태 확인하기
analyze index IDX_TBL_STUDENT_1_NAME validate structure; -- non-unique 인덱스를 분석
-- Index IDX_TBL_STUDENT_1_NAME이(가) 분석되었습니다.
select (del_lf_rows_len / lf_rows_len) * 100 "인덱스상태(Balance)"
from index_stats
where name = 'IDX_TBL_STUDENT_1_NAME';
/*
인덱스상태(Balance)
------------------
0 <== 0 에 가까울 수록 인덱스 상태가 좋은 것이다.(인덱스가 안 쪼개진 것임 즉 해당 인덱스에 DML 발생이 없다는 뜻)
*/
select count(*)
from tbl_student_1;
----- 현재 tbl_student_1 테이블의 수는 10003개
delete from tbl_student_1
where hakbun between '20240305-400' and '20240305-9400'; -- 400~ 9400번까지 삭제
-- 6,001개 행 이(가) 삭제되었습니다.
commit; -- dml때는 꼭 커밋을 하든 롤백을 하든 꼭하기
select count(*)
from tbl_student_1;
----- 현재 tbl_student_1 테이블의 수는 4002개
-- 인덱스 상태 다시 조회(아직 분석 안함(딜리트 전에 분석함))
select (del_lf_rows_len / lf_rows_len) * 100 "인덱스상태(Balance)"
from index_stats
where name = 'IDX_TBL_STUDENT_1_NAME';
/*
인덱스상태(Balance)
------------------
0 <== delete 하기 전의 index를 분석한 것이므로 0이라고 나올 뿐이다.
*/
-- 딜리트 후 다시 분석하기
analyze index IDX_TBL_STUDENT_1_NAME validate structure; -- non-unique 인덱스를 분석
-- Index IDX_TBL_STUDENT_1_NAME이(가) 분석되었습니다.
-- 인덱스 상태 다시 조회
select (del_lf_rows_len / lf_rows_len) * 100 "인덱스상태(Balance)"
from index_stats
where name = 'IDX_TBL_STUDENT_1_NAME';
/*
인덱스상태(Balance)
------------------
59.99108333467217197114534967787542374243 <== index가 엄청 깨져있다. index의 밸런스가 대략 60% 정도가 깨진 것이다.
*/
--- 결론 : 속도가 느려졌을 때 인덱스가 깨졌는지 확인해볼 수 있다
--- 업데이트 때 인덱스가 깨지는지 조회해보자
update tbl_student_1 set name = '홍길동'
where hakbun between '20240305-9401' and '20240305-9901';
-- 556개 행 이(가) 업데이트되었습니다.
commit;
-- 커밋 완료
-- 업데이트 커밋 후 다시 분석하기
analyze index IDX_TBL_STUDENT_1_NAME validate structure; -- name 컬럼이 바뀜(update tbl_student_1 set name = '홍길동' 해서)
-- Index IDX_TBL_STUDENT_1_NAME이(가) 분석되었습니다.
-- 인덱스 상태 다시 조회
select (del_lf_rows_len / lf_rows_len) * 100 "인덱스상태(Balance)"
from index_stats
where name = 'IDX_TBL_STUDENT_1_NAME';
/*
인덱스상태(Balance)
------------------
62.5602723252534799463648343879789608763 <== index의 밸런스가 대략 61% 정도가 깨진 것이다.
*/5) Index Rebuild(인덱스 재건축) 하기
-- 인덱스 밸런스가 대략 61% 정도 깨진 IDX_TBL_STUDENT_1_NAME 을 Index Rebuild(인덱스 재건축) 하겠습니다. --
alter index IDX_TBL_STUDENT_1_NAME rebuild;
-- Index IDX_TBL_STUDENT_1_NAME이(가) 변경되었습니다.
-- rebuild 후 다시 분석하기
analyze index IDX_TBL_STUDENT_1_NAME validate structure;
-- Index IDX_TBL_STUDENT_1_NAME이(가) 분석되었습니다.
-- 인덱스 상태 다시 조회
select (del_lf_rows_len / lf_rows_len) * 100 "인덱스상태(Balance)"
from index_stats
where name = 'IDX_TBL_STUDENT_1_NAME';
/*
인덱스상태(Balance)
------------------
0 <-- 인덱스의 리빌드를 통해 0으로 만듬. index_stats가 0에 가까울수록 인덱스 상태가 좋은 것이다.
*/
-- index 컬럼에 dml 많이쓰면 부하일어나므로 리빌드함. 4. index 삭제하기
-- drop index 삭제해야할 인덱스명;
select
A.index_name, A.uniqueness, B.column_name, B.descend
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
WHERE A.table_name = 'TBL_STUDENT_1' -- TBL_STUDENT_1테이블에 생성되어있는 인덱스 이름, 유니크한지 여부, 칼럼명, 정렬여부를 알려준다.
/*
----------------------------------------------------------------------
index_name uniqueness column_name descend
----------------------------------------------------------------------
IDX_TBL_STUDENT_1_HAKBUN UNIQUE HAKBUN ASC
IDX_TBL_STUDENT_1_NAME NONUNIQUE NAME ASC
----------------------------------------------------------------------
*/
drop index IDX_TBL_STUDENT_1_HAKBUN;
-- 삭제되었습니다.
drop index IDX_TBL_STUDENT_1_NAME;
-- 삭제되었습니다.5. 복합인덱스(Composite index) 생성하기
복합인덱스(composite index)란?
2개 이상의 컬럼으로 묶어진 인덱스를 말하는 것으로서
where 절에 2개의 컬럼이 사용될 경우 각각 1개 컬럼마다 각각의 인덱스를 만들어서 사용하는 것보다
2개의 컬럼을 묶어서 하나의 인덱스로 만들어 사용하는 것이 속도가 좀 더 빠르다.
select *
from tbl_student_1
where name = '배수지10001' and address = '서울시 마포구 월드컵로 10001';
-- 이렇게 and절로 조회할 일이 많으면 네임인덱스 /주소 인덱스 로 조회하는 것보다 복합적으로 네임+주소 인덱스 이렇게 복합 인덱스를 만드는 것이 좋다.
1) 선행칼럼 정하기
!!!! 중요 !!!! --
복합인덱스(composite index) 생성시 중요한 것은 선행컬럼을 정하는 것이다.
선행컬럼은 맨처음에 나오는 것으로 아래에서는 name 이 선행컬럼이 된다. ★
복합인덱스(composite index)로 사용되는 컬럼중 선행컬럼으로 선정되는 기준은 where 절에 가장 많이 사용되는 것이며
선택도(selectivity)가 높은 컬럼이 선행컬럼으로 선정되어야 한다.
인덱스는 선택도(selectivity)가 높은 컬럼에 만들어야 한다.
※ 고유한 데이터일수록 선택도(selectivity)가 높아진다.
예: 성별 컬럼 --> 선택도(selectivity)가 아주 낮다. 왜냐하면 수많은 사람 중 남자 아니면 여자 하나만 골라야 하므로 선택의 여지가 아주 낮다.
학번 --> 선택도(selectivity)가 아주 좋다. 왜냐하면 학번은 다양하고 고유하므로 골라야할 대상이 아주 많으므로 선택도가 높은 것이다.2) 실습
/*
create index idx_tbl_student_1_name_addr
on tbl_student_1(address, name); -- address 컬럼이 선행컬럼이 된다. ★
아래의 코드와 이 코드는 다른 것이다!! (순서가 다르므로 선행컬럼이 다르다!)
*/
create index idx_tbl_student_1_name_addr
on tbl_student_1(name, address); -- name 컬럼이 선행컬럼이 된다.★
-- Index IDX_TBL_STUDENT_1_NAME_ADDR이(가) 생성되었습니다.
-- 이름이 검색할 때 많이 쓰므로 name 을 선행컬럼으로 만들었다. (만약 address 조회가 더 많은 테이블이라면, address를 선행컬럼으로 잡는다.)
-- 만약 where 빈도수가 엇비슷하면 그때는 선택도(selectivity)가 높은 컬럼을 선행컬럼으로 한다(고유한 데이터를 가진 컬럼)
-- 결론 : 1. where절에 많이 쓰이는 것을 선행컬럼으로 함. 2. 엇비슷하다면 고유성이 높은 컬럼(=선택도가 높은 컬럼)을 선행컬럼으로 함.
3) 선행칼럼 조회
select
A.index_name, A.uniqueness, B.column_name, B.descend,
B.column_position -- 선행컬럼 (숫자 1이 선행컬럼)
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
WHERE A.table_name = 'TBL_STUDENT_1' -- TBL_STUDENT_1테이블에 생성되어있는 인덱스 이름, 유니크한지 여부, 칼럼명, 정렬여부, 선행컬럼을 알려준다.
/*
--------------------------------------------------------------------------------------------
index_name uniqueness column_name descend column_postion
--------------------------------------------------------------------------------------------
IDX_TBL_STUDENT_1_NAME_ADDR NONUNIQUE NAME ASC 1 (숫자 1이 선행컬럼이다)
IDX_TBL_STUDENT_1_NAME_ADDR NONUNIQUE ADDRESS ASC 2
--------------------------------------------------------------------------------------------
*/4) 선행칼럼 성능 조회
select *
from tbl_student_1
where name = '배수지10001' and address = '서울시 마포구 월드컵로 10001';
-- where 절에 선행컬럼인 name 이 사용되어지면 복합인덱스(composite index)인 IDX_TBL_STUDENT_1_NAME_ADDR 을 사용하여 빨리 조회해온다.
select *
from tbl_student_1
where address = '서울시 마포구 월드컵로 10001' and name = '배수지10001';
-- OPTIONS RANGE SCAN/ cost 2 2 1
-- cost 2 2 1
-- where 절에 선행컬럼인 name 이 사용되어지면 복합인덱스(composite index)인 IDX_TBL_STUDENT_1_NAME_ADDR 을 사용하여 빨리 조회해온다.
select *
from tbl_student_1
where name = '배수지10001';
-- OPTIONS RANGE SCAN / cost 3 3 2
-- where 절에 선행컬럼인 name 이 사용되어지면 복합인덱스(composite index)인 IDX_TBL_STUDENT_1_NAME_ADDR 을 사용하여 빨리 조회해온다.
select *
from tbl_student_1
where address = '서울시 마포구 월드컵로 10001';
-- OPTIONS Full / cost 68 68
-- where 절에 선행컬럼이 없으므로 복합인덱스(composite index)인 IDX_TBL_STUDENT_1_NAME_ADDR 을 사용하지 못하고 Table Full Scan 하여 조회해오므로 속도가 떨어진다.
5) PK와 index
어떤 컬럼에 PK나 UNIQUE를 주게 되면 제약조건 이름이 자동적으로 인덱스로 쓰여져 버린다.
create table tbl_member
(userid varchar2(20)
,passwd varchar2(30) not null
,name varchar2(20) not null
,address varchar2(100)
,email varchar2(50) not null
,constraint PK_tbl_member_userid primary key(userid)
,constraint UQ_tbl_member_email unique(email)
);
-- Table TBL_MEMBER이(가) 생성되었습니다.
-- 이 테이블에서 pk/uq인 userid, email에 인덱스를 만들면 안된다!!!! 제약조건명이 인덱스 이름으로 쓰이고 있다.
/* ★
즉 어떤 컬럼에 PK나 UNIQUE를 주게 되면 제약조건 이름이 자동적으로 인덱스로 쓰여져 버린다.
그러므로 굳이 userid, email에 인덱스를 만들 필요가 없다.
*/
create table tbl_member
(userid varchar2(20)
,passwd varchar2(30) not null
,name varchar2(20) not null
,address varchar2(100)
,email varchar2(50) not null
,constraint PK_tbl_member_userid primary key(userid)
,constraint UQ_tbl_member_email unique(email)
);
-- Table TBL_MEMBER이(가) 생성되었습니다.
declare
v_cnt number := 1;
begin
loop
exit when v_cnt > 10000;
insert into tbl_member(userid, passwd, name, address, email)
values('hongkd'||v_cnt, 'qwer1234$', '홍길동'||v_cnt, '서울시 마포구 '||v_cnt, 'hongkd'||v_cnt||'@gmail.com');
v_cnt := v_cnt + 1;
end loop;
end;
-- PL/SQL 프로시저가 성공적으로 완료되었습니다.
commit;
-- 커밋 완료.
select *
from tbl_member;
select A.index_name, A.uniqueness, B.column_name, B.descend, B.column_position
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
where A.table_name = 'TBL_MEMBER';
/*
제약조건이름이 index_name으로 쓰이고 있다.
즉 어떤 컬럼에 PK나 UNIQUE를 주게 되면 제약조건 이름이 자동적으로 인덱스로 쓰여져 버린다. 그러므로 굳이 userid, email에 인덱스를 만들 필요가 없다.
---------------------------------------------------------------------------------------------
index_name uniqueness column_name descend column_position
---------------------------------------------------------------------------------------------
PK_TBL_MEMBER_USERID UNIQUE USERID ASC 1
UQ_TBL_MEMBER_EMAIL UNIQUE EMAIL ASC 1
---------------------------------------------------------------------------------------------
*/
6) 복합인덱스 성능비교
--- 로그인을 하는데 로그인이 성공되어지면 그 회원의 성명만을 보여주도록 한다.
select name
from tbl_member
where userid = 'hongkd201' and passwd = 'qwer1234$';
-- 실행 계획 누르기
-- tbl_member는 실행 계획 내 계획 설명의 options에서 by index rowid(db 전체에서 유일한 값)
--- **** userid, passwd, name 컬럼을 가지고 복합인덱스(composite index)를 생성해 봅니다. **** ---
create index idx_tbl_member_id_pwd_name
on tbl_member(userid, passwd, name); -- name을 넣음 !!!!
-- Index IDX_TBL_MEMBER_ID_PWD_NAME이(가) 생성되었습니다.
select A.index_name, A.uniqueness, B.column_name, B.descend, B.column_position
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
where A.table_name = 'TBL_MEMBER';
/*
---------------------------------------------------------------------------------------------
index_name uniqueness column_name descend column_position
---------------------------------------------------------------------------------------------
PK_TBL_MEMBER_USERID UNIQUE USERID ASC 1
UQ_TBL_MEMBER_EMAIL UNIQUE EMAIL ASC 1
IDX_TBL_MEMBER_ID_PWD_NAME NONUNIQUE USERID ASC 1
IDX_TBL_MEMBER_ID_PWD_NAME NONUNIQUE PASSWD ASC 2
IDX_TBL_MEMBER_ID_PWD_NAME NONUNIQUE NAME ASC 3
---------------------------------------------------------------------------------------------
*/
-- 웹사이트에서 가장 많이 select(조회)가 이루어지는건 로그인이다.
--- 로그인을 하는데 로그인이 성공되어지면 그 회원의 성명만을 보여주도록 한다.
select name
-- 복합 인덱스는 tbl_member(userid, passwd, name)였다. 이렇게 인덱스에 넣어주면 이름을 테이블이 아니라 인덱스에서 찾기 때문에 조금 더 속도가 빨라진다
-- =>이런 경우 복합인덱스 만드는게 조금 더 좋음.
from tbl_member
where userid = 'hongkd201' and passwd = 'qwer1234$';
-- 코스트가 1 1 으로 2개, index밖에 나오지 않는다. 왜냐하면 복합 인덱스는 name까지 묶어온것이다. name은 인덱스에 다 있기 때문에
-- 테이블을 조회할 필요가 없다.
/*
강사님 설명 )
where 절 및 select 에 복합인덱스(composite index)인 IDX_TBL_MEMBER_ID_PWD_NAME 에 사용되어진 컬럼만 있으므로
테이블 tbl_member 에는 접근하지 않고 인덱스 IDX_TBL_MEMBER_ID_PWD_NAME 에만 접근해서 조회하므로 속도가 빨라진다.
*/
select name, address -- address는 복합 인덱스에 포함되어있지 않음.
from tbl_member
where userid = 'hongkd201' and passwd = 'qwer1234$';
-- 조회해보면 TBL_member가 조회되고 있다.
-- 이런 경우 테이블을 조회하는 과정을 거치기 때문에 조금 더 코스트가 높아진다(속도가 느려진다).
/*
강사님 설명 )
where 절에 userid 및 passwd 가 사용되었으므로 복합인덱스(composite index)인 IDX_TBL_MEMBER_ID_PWD_NAME 을 사용하는데
select 절에 IDX_TBL_MEMBER_ID_PWD_NAME 에 없는 address 컬럼이 있으므로 테이블 tbl_member 에 접근해야 한다.
그러므로 인덱스 IDX_TBL_MEMBER_ID_PWD_NAME 만 접근하는 것 보다는 조회 속도가 쬐금 느려진다.
*/
drop index idx_tbl_member_id_pwd_name;
-- Index IDX_TBL_MEMBER_ID_PWD_NAME이(가) 삭제되었습니다.
6. 함수기반 인덱스(function based index) 생성
select A.index_name, A.uniqueness, B.column_name, B.descend, B.column_position
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
where A.table_name = 'TBL_STUDENT_1';
drop index IDX_TBL_STUDENT_1_NAME_ADDR;
-- Index IDX_TBL_STUDENT_1_NAME_ADDR이(가) 삭제되었습니다.
-- 인덱스 다 지운 상태
-- 새로 non-unique index 생성
create index idx_tbl_student_1_name
on tbl_student_1(name);
-- Index IDX_TBL_STUDENT_1_NAME이(가) 생성되었습니다.
select *
from tbl_student_1
where name = '배수지10002';
-- name 컬럼에 생성해둔 인덱스 IDX_TBL_STUDENT_1_NAME 인덱스 range scan 를 사용하여 조회해온다.
select *
from tbl_student_1
where substr(name,2,2) = '수지'; -- substr(name,2,2)는 함수임 / name의 2번쨰부터 2글자 조회.
-- name 컬럼에 인덱스가 있지만 table full scan이 실행된다.
-- 즉 name 컬럼에 생성해둔 인덱스 IDX_TBL_STUDENT_1_NAME 인덱스를 사용하지 않고 Table Full Scan 하여 조회해온다.
-- 그렇다면 이 substr(name,2,2) 함수 부분을 인덱스로 만들어버릴 수 있다
create index idx_func_tbl_student_1_name
on tbl_student_1( substr(name,2,2) ); -- 함수기반 인덱스(function based index) 생성
-- Index IDX_FUNC_TBL_STUDENT_1_NAME이(가) 생성되었습니다.
select *
from tbl_student_1
where substr(name,2,2) = '수지';
-- 함수기반 인덱스인 IDX_FUNC_TBL_STUDENT_1_NAME 을 사용하여 조회해온다.
*/7. index 있는 컬럼을 like로 조회
select *
from tbl_student_1
where name = '배수지10002';
-- 인덱스 IDX_TBL_STUDENT_1_NAME 을 사용하여 조회해온다.
select *
from tbl_student_1
where name like '배수지10002';
-- 인덱스 IDX_TBL_STUDENT_1_NAME 을 사용하여 조회해온다.
select *
from tbl_student_1
where name like '배수지%';
-- 인덱스 IDX_TBL_STUDENT_1_NAME 을 사용하여 조회해온다.
-- 숫자 끝 와일드는 괜찮음
select *
from tbl_student_1
where name like '%배수지%';
-- [!!]맨앞에 % 또는 _ (와일드가 나오면 인덱스 IDX_TBL_STUDENT_1_NAME 을 사용하지 않고 Table Full Scan 하여 조회해온다.
8. Primary Key 제약 또는 Unique 제약과 index - 삭제
--- **** 어떤 테이블의 어떤 컬럼에 Primary Key 제약 또는 Unique 제약을 주면
-- 자동적으로 그 컬럼에는 unique 한 index가 생성되어진다.
-- 인덱스명은 제약조건명이 된다. ****
create table tbl_student_2
(hakbun varchar2(10)
,name varchar2(20)
,email varchar2(20) not null
,address varchar2(20)
,constraint PK_tbl_student_2_hakbun primary key(hakbun) -- !!
,constraint UQ_tbl_student_2_email unique(email) -- !!
);
-- Table TBL_STUDENT_2이(가) 생성되었습니다.
select A.index_name, uniqueness, column_name, descend
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
where A.table_name = 'TBL_STUDENT_2';
-- Primary Key 제약 또는 Unique 제약으로 생성되어진 index 의 제거는
-- drop index index명; 이 아니라
-- alter table 테이블명 drop constraint 제약조건명; 이다.
-- 제약조건을 삭제하면 자동적으로 index 도 삭제가 된다.
drop index PK_TBL_STUDENT_2_HAKBUN;
/*
오류 보고 -
ORA-02429: 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다.
*/
drop index UQ_TBL_STUDENT_2_EMAIL;
/*
오류 보고 -
ORA-02429: 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다.
*/
alter table tbl_student_2
drop primary key;
-- Table TBL_STUDENT_2이(가) 변경되었습니다.
alter table tbl_student_2
drop constraint UQ_tbl_student_2_email;
-- Table TBL_STUDENT_2이(가) 변경되었습니다.
select A.constraint_name, A.constraint_type, A.search_condition,
B.column_name, B.position
from user_constraints A join user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = 'TBL_STUDENT_2';
-- 제약조건이 없는 것을 확인함.
select A.index_name, uniqueness, column_name, descend
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
where A.table_name = 'TBL_STUDENT_2';
-- 인덱스에도 없어짐.
개발하는 심심이