Oracle sqlldr 실습 및 Index

--|| 우편번호 데이타파일을 가지고 테이블에 저장하기 ||--
-- 1. 우편번호 데이타 파일을 다운받는다. (http://www.zipfinder.co.kr/ 참조)
/* 엑셀파일을 "," 형식의 파일로 바꿀려면 xx.csv 형태로 바꾸어주어야 한다. */
-- 2. 파일을 받았으면 파일내용에 맞게 테이블을 생성한다.
create table ziptbl
(zipcode varchar2(10)
,sido varchar2(30)
,gugun varchar2(200)
,dong varchar2(200)
,bunji varchar2(200)
) tablespace users;
-- 3. 이제 로드 할 수 있도록 ctl 파일을 생성한다 (생성은 상단 파일생성 참조)
-- 4. 명령프롬프트에서 명령을 실행하여 데이타를 로드한다. (상단 로드 명령어 참조)
-- 5. 테이블에 잘 입력되었나 확인해본다.
select * from ziptbl;

select *
from ziptbl
where dong='용흥동'; -- 원하는 동만 검색하나 풀검색을 하기 때문에 시간이 많이 소요된다.
--- index 생성
--// where 절에 자주 사용되어지는 컬럼에 생성한다.
--   단. 해당컬럼에 찾고자 하는 데이터가 전체 데이터에 10% - 15% 미만일 경우에만 생성한다.
--   10% - 15% 넘을 경우는 index를 생성하지 않는것이 성능에 좋다.
--   index가 많으면 유지 관리가 힘들고 DML처리속도가 느려지기  때문에 꼭 필요한 컬럼만 사용하는것이 좋다.
create index idx_ziptbl_dong -- index 생성
on ziptbl(dong);             -- dong 컬럼에 중복된 데이터값을 허용하는 것이다.

drop index idx_ziptbl_dong;  -- index 삭제

create table heowon
(id varchar2(10) not null
,name varchar2(10)
,jubun varchar2(13)
,addr varchar2(10)) tablespace users;

create unique index idx_heowon_id    -- 테이블 생성시 not null을 주고 unique index를 주면
               -- pk와 동일하게 작용한다.
on heowon(id);

create unique index idx_heowon_jubun
on heowon(jubun);

create index idx_heowon_name
on heowon(name);
--// index는 해당 유저의 default된 곳에 저장이 되어진다.
select *
from user_indexes
where table_name = 'HEOWON'; -- 해당 테이블의 인덱스 내용을 보여준다.

select *
from user_ind_columns
where table_name = 'HEOWON'; -- 해당 테이블에 인덱스된 컬럼내용을 보여준다.
/* 해당 테이블과 그 테이블에 해당하는 index는 서로 다른 tablespace에 있어야 물리적으로 좋다 */
create index idx_heowon_name  -- 이처럼 별도로 인덱스만 저장가능한 tablespace를 생성하여 저장한다.
on heowon(name)
tablespace indx;              -- indx는 별도로 생성해준 index 저장 tablespace
 
select * from heowon;

insert into heowon values('aaa','홍길동','8010101234567','서울');
insert into heowon values('aaa','김유신','9010101234567','서울');
insert into heowon values('bbb','김유신','8010101234567','서울');
---// 위 데이타를 입력해 보면 김유신은 데이타 입력이 안되게 된다.
--    이것은 id와 jubun에 unique index를 지정하여서 해당 컬럼이 PK로 작용하기 때문이다.
insert into heowon values('ccc','이순신',null,'서울');
insert into heowon values('ddd','엄정화',null,'서울');
--// 위 두개의 데이타를 입력해 보면 jubun에 null을 허용했기 때문에 null값이 들어온다.
--   또한 엄정화도 null을 주게되면 중복데이타기때문에 안들어 갈거 같지만.
--   index에서는 null을 제외해 버리기 때문에 오류없이 입력이 된다. 
insert into heowon values('eee','김유신','8210101234567','수원');
--// name 컬럼은 일반 index로 했기때문에 동일한 값이여도 입력이 가능하다.
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
/* 제약조건으로 primary key 와 unique 제약을 생성하면 자동적으로 unique한 index가 생성되어진다. */
/*//// salesman 으로 접속하여 실행 ////*/
select *
from user_indexes
where table_name='SAWON';  -- 테이블의 인덱스 내용을 보여줌

select *
from user_ind_columns
where table_name='SAWON';  -- 테이블의 해당 컬럼의 인덱스 내용을 보여줌

drop index sawon_jubun_uk; -- 직접 생성한 인덱스가 아니고 pk나 unique등 제약조건으로 생성된 index는
             -- drop index로는 지워지지가 않는다.
--- 제약조건으로 생긴 index를 지우려면 다음과 같이 실행해야한다.
alter table sawon
drop constraint sawon_jubun_uk; -- 1. 제약조건을 없애거나
-- 또는 
alter table sawon
enable constraint sawon_jubun_uk;  -- 2. 제약조건을 disable 시키면 인덱스가 사라진다.
                --    다시 enable 시키면 재 생성된다.
/* 테이블 생성시 pk,unique 제약조건을 줄시 생성되는 index는 테이블과 동일한 tablespace에
   위치하게 된다. 이것은 물리적으로 좋지 않기 때문에 테이블 생성시 나누어 주어야 한다. */
create table test01
(id varchar2(10)
,name varchar2(10)
,constraint test01_id_pk primary key(id)
) tablespace users;

select index_name, tablespace_name
from user_indexes
where table_name='TEST01';   -- 이렇게 생성하면 index가 테이블과 동일한 tablespace에 저장이 된다.

create table test02
(id varchar2(10)
,name varchar2(10)
,constraint test02_id_pk primary key(id) using index tablespace indx
) tablespace users;                      -- using index tablespace (저장할 테이블스페이스명)
              -- 이렇게 테이블을 생성해야만 다른 tablespace에 
              -- index가 저장이 되어진다.
select index_name, tablespace_name
from user_indexes
where table_name='TEST02';     -- 해당 테이블의 index 내용을 보여준다.