07월 24일 수 | SQL활용 05 - SQL View와 Index Ⅱ

 

 

SQL View와 Index

Index

데이터를 빠르게 찾을 수 있는 수단

테이블에 대한 조회 속도를 높여주는 자료구조

PK칼럼은 자동으로 인덱스 생성된다.

 

인덱스생성: create index 인덱스명

인덱스삭제: drop index 인덱스명

인덱스수정: alter index 인덱스명

 

인덱스 방식

- full scan

처음부터 끝까지 일일이 검사하는 방법, 전수조사

- index range scan

이름이 여러개인 경우 목차를 찾아서 페이지를 찾아감

훨씬 빠름, 별도의 메모리가 있어야 함.

- index unique scan

학번은 1개만 존재함, 유일한 값.

 

 

--F10 실행계획 보기 (커서위치중요)

-> full scan cost 3

 

--제약조건중

-- Primary Key, Unique 제약조건을 만들면 해당 인덱스 페이지가 자동으로 생성된다.

--인덱스 생성 후

create index 인덱스명 on 테이블명(칼럼명)

 

 

인덱스 생성

create index c_emp_name_idx

on c_emp(name);

 

 

-- PK는 인덱스가 자동으로 생성되면서 정렬된다.

create table emp4 (

no number primary key

,name varchar2(10)

,sal number

)

 

Procedure 

프로시저 작성방법

declare -- 프로시저 선언문 -- 변수선언 begin --  -- T-SQL문(제어문) end; --  /  -- 종결문자

 

 

예시 1)

select count(*) from emp3;

현재 emp3 에 아무 데이터가 없다.

 

프로시저문을 작성하여 100만건의 데이터를 넣을 것이다.

 

declare --선언문   --변수선언  i    number      := 1; --i변수에 1 대입(:=)연산자  name varchar(20) := 'kim';  sal  number      := 0; begin   --T-SQL문(제어문) for문, while문  while i<=1000000 loop    if i mod 2 = 0 then        name := 'kim' || to_char(i);       sal  := 300;    elsif i mod 3 = 0 then       name := 'park' || to_char(i);       sal  := 400;    elsif i mod 5 = 0 then       name := 'hong' || to_char(i);       sal  := 500;    else        name := 'shin' || to_char(i);       sal  := 250;    end if;        insert into emp3(no, name, sal)    values (i, name, sal);    i := i+1;  end loop; end; /

 

 

문제 1) name 칼럼을 기준으로 인덱스를 생성한 후 name칼럼에서 조회하고 F10 계획결과를 확인하시오.

create index emp3_name_idx on emp3(name); select * from emp3 where name = 'kim466';

 

 

 

select * from user_indexes where table_name in ('emp3','emp4');

 

 

 

select index_name, table_name, uniqueness from user_indexes where table_name in ('emp3','emp4');

 

 

이름과 급여를 기준으로 인덱스 생성

create index emp3_name_sal_index on emp3(name,sal);

 

인덱스 조회하기

예시 1) full scan, cost 893

select * from emp3 where no = 466;

 

 

예시 2) range scan, cost 17

select * from emp3 where name = 'kim466';

 

 

예시 3) full scan cost 894

select * from emp3 where sal>200;

 

 

예시 4) range scan, cost 3

select * from emp3 where name = 'kim466' and sal>200;

 

 

 

문제 1) 국어점수에 따라 A,B,C,D,F학점을 구하시오.

select uname, kor,      case when kor>=90 then 'A학점'          when kor>=80 then 'B학점'          when kor>=70 then 'C학점'          when kor>=60 then 'D학점'             else 'F학점' --자바의 default     end as 국어학점     from sungjuk;

 

 

문제 2) addr칼럼의 주소를 한글로 조회하시오.

select uname,     case when addr='SEOUL' then '서울'          when addr='BUSAN' then '부산'          when addr='JEJU' then '제주'          when addr='SUWON' then '수원'     else '없음'     end as 주소지     from sungjuk;

 

 

 

문제 3) 부서코드가 10 경리팀, 20 연구팀, 30 총무팀, 40 전산팀

select ename, deptno,     case when deptno = 10 then '경리팀'          when deptno = 20 then '연구팀'          when deptno = 30 then '총무팀'          when deptno = 40 then '전산팀'     else '없음'     end as 부서     from emp order by deptno;

 

문제 4) 커미션 5 이상 '5%', 4 이상 '4%', 3 이상 '3%', 2 이상 '2%' 외는 없음

select ename,      case when nvl(comm,0) >= 5 then '5%'          when nvl(comm,0) >= 4 then '4%'          when nvl(comm,0) >= 3 then '3%'          when nvl(comm,0) >= 2 then '2%'     else '없음'     end as 커미션     from emp order by comm;

 

 

 

decode() 함수

값을 비교하여 해당하는 값을 돌려주는 함수.

단, 비교시에는 정확이 같은 값(=)만 비교가 가능함

decode(A,B,같을 때의 값, 다를 때의 값)

 

select ename, deptno,         decode(deptno, 10, '경리팀'                       ,20, '연구팀'                       ,30, '총무팀') 부서                       from emp;

 

 

문제 5) 학점 구하기

select uname, trunc(((kor+eng+mat)/3)/10),         decode(trunc(((kor+eng+mat)/3)/10),10,'A'                                          , 9,'A'                                          , 8,'B'                                          , 7,'C'                                          , 6,'D'                                            ,'F' ) as 성적 from sungjuk;

 

 

 

Join

두 개 이상의 테이블을 결합하여 데이터를 추출하는 기법.

두 테이블의 공통값을 이용하여 컬럼을 조합하는 수단.

 

 

 

-- 직원테이블 create table emp(   empno       number(4)     primary key   ,ename      varchar2(10)     ,job        varchar2(9)      ,mgr        number(4)        ,hiredate   date             ,sal        number(7,2)      ,comm       number(7,2)      ,deptno     number(7)     references dept(deptno) ); -- 부서테이블 create table dept(   deptno   number(7)    primary key   ,dname   varchar(20)   ,loc     varchar(10) );

 

 

데이터 삽입 및 실행의 순서가 제대로 되어있어야함.

 

1. 부서테이블을 만들고

2. 그 다음 부서테이블의 데이터를 삽입하고

3. 직원테이블을 만들고 (직원테이블에 부서테이블을 참조하는 외래키가 있기 때문에)

4. 직원테이블에 데이터를 삽입한다.

 

1~2

-- 부서테이블 create table dept(   deptno   number(7)    primary key   ,dname   varchar(20)   ,loc     varchar(10) ); insert into dept(deptno,dname,loc) values(10,'경영팀','부산'); insert into dept(deptno,dname,loc) values(20,'영업팀','울산'); insert into dept(deptno,dname,loc) values(30,'재무팀','여의도'); insert into dept(deptno,dname,loc) values(40,'개발팀','판교');

 

 

3~4

-- 직원테이블 create table emp(   empno       number(4)     primary key   ,ename      varchar2(10)     ,job        varchar2(9)      ,mgr        number(4)        ,hiredate   date             ,sal        number(7,2)      ,comm       number(7,2)      ,deptno     number(7)     references dept(deptno) ); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7369, '개나리', '사원', 7902, '2000-12-17', 200, 20); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7499, '진달래', '주임', 7698, '2001-12-15', 360, 20); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7521, '라일락', '주임', 7698, '2001-02-17', 355, 30); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7654, '손흥민', '과장', 7839, '2002-01-11', 400, 30); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7698, '박지성', '주임', 7698, '2000-07-12', 325, 20); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7782, '김연아', '사원', 7698, '2001-12-17', 225, 10); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7778, '무궁화', '사원', 7839, '2005-11-14', 200, 10); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7839, '홍길동', '부장', 7566, '2006-06-17', 450, 20); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7844, '송강호', '과장', 7566, '2018-09-17', 400, 30); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7876, '정우성', '대표', 7839, '2004-09-09', 500, 30); insert into emp(empno, ename, job, mgr, hiredate, sal, deptno) values(7900, '김혜수', '사원', 7902, '2001-12-03', 200, 20);

 

 

그 다음 조인을 해보면 ( 아래 두 식 모두 같은 뜻임)

-- 조인하기 select * from emp join dept on emp.deptno = dept.deptno; select * from emp , dept where emp.deptno = dept.deptno;

 

이렇게 두개의 테이블이 합쳐진 결과가 나온다.