sql중급 / DDL문(global temporary table, view,index)

+) 대량의 컬럼 데이터타입 변경방법 (notepad++ 이용)

마우스 커서로 alt키 누르고 드래그하면 여러 행을 한번에 작성 및 선택할 수 있음

-- sql developer로 하는 방법
select 'alter table crime_loc_type modify ' || column_name || 'number(38);'
 from user_tab_columns
 where table_name='CRIME_LOC_TYPE'
 order by column_id;
 
 -- 을 작성하여 출력물을 복사하여 다시 쿼리실행하면 됨

+) 특정테이블의 컬럼명만 뽑아내는 방법

select column_name
 from user_tab_columns
 where table_name='CRIME_LOC_TYPE'
 order by column_id;

 

 

094 임시 테이블 생성하기(CREATE TEMPORAY TABLE)

데이터를 영구히 저장하는게 아니라 지금 잠깐 테스트를 위해서 볼 데이터이고 금방 삭제할 데이터면 그 데이터를 임시 테이블에 저장하는 것이 바람직함.

-- 예제
create global temporary table emp56
( empno    number(10),
  ename    varchar2(20),
  sal      number(10) )
 on commit delete rows;

위와 같이 테이블을 생성하면 데이터를 영구히 저장하는게 아니라 commit할때까지만 데이터를 보존.

insert into emp56 values(1234,'scott',3500);

입력 후 데이터 확인 > commit > 데이터 사라져있음

테이블 구조는 그대로 남고 데이터만 사라짐. 이렇게 하는게 일반 테이블을 만들고 데이터를 지우는것보다 훨씬 속도가 빠르고 database에 부하를 주지 않음.

문제492. on commit delete rows 옵션을 사용해서 dept700테이블을 생성하는데 dept 테이블의 구조와 똑같이 만드시오.

create global temporary table dept700
 ( deptno      number(10),
   dname      varchar2(14),
   loc        varchar2(13) )
  on commit delete rows ;

ㅇ임시테이블 옵션 2가지

1. on commit delete rows : 데이터를 commit할 때 까지만 유지. commit이후에는 데이터 사라짐.

2. on commit preserve rows : 데이터를 session이 종료될때까지만 유지= sql developer 종료할 때까지. 접속해제되면 데이터가 사라짐

문제493. 문제492번에서 만든 dept700테이블을 on commit preserve rows로 다시 생성하시오

drop table dept700;
create global temporary table dept700
 ( deptno      number(10),
   dname      varchar2(14),
   loc        varchar2(13) )
  on commit preserve rows ;

문제494. 위의 dept700테이블에 아래의 데이터를 입력하시오

부서번호 10 , 부서명 sales , 부서위치 dallas

insert into dept700(deptno, dname, loc)
 values(10,'SALES','DALLAS');

 

임시테이블은 언제 사용하는 것인가?

> 영구히 데이터를 남길 필요가 없는 임시로 사용할 데이터인데, 이 데이터가 빅데이터일때 사용함.

 

 

095 복잡한 쿼리를 단순하게 하기 1(VIEW)

ㅁ오라클 database object 5가지

1. table : 행과 열로 이루어져있으며 데이터를 저장하는 기본 저장 영역

2. view : 테이블과 비슷하지만 데이터를 저장하지 않고 select문으로 데이터 검색결과를 출력하는 db object.

3. index

4. sequence

5. synonym

 

ㅇview를 사용했을때의 장점

1. 보안상 이유로 민감한 데이터를 노출시키고 싶지 않을 때

2. 복잡한 쿼리문을 간단하게 쿼리하고 싶을 때

 

--예제. 보안상의 이유로 민감한 데이터를 노출시키고 싶지 않을 때
create view emp57
as
select empno, ename, sal
 from emp;
 
--emp57 view의 데이터를 수정하는데 이름이 scott인 사원의 월급을 0로 변경하시오
update emp57
set sal = 0
where ename = 'SCOTT';
/* emp57을 조회해보면 scott의 월급이 0으로 변경된 상태로 보임. >emp테이블의 데이터도 변경됨
 
 view를 수정하면 테이블도 수정됨 */

문제495. 직업이 SALESMAN인 사원들의 이름,월급,직업,부서번호를 출력하는데 view를 emp442라는 이름으로 생성하시오

create view emp442
as
select ename, sal, job, deptno
 from emp
 where job = 'SALESMAN';

위의 view를 수정하면 emp테이블이 같이 수정됨.

 

ㅇview의 종류 2가지

	단순 view	복합 view
테이블의 갯수	1개	2개 이상
함수 포함여부	불포함	포함
데이터 갱신여부	갱신됨	수정안될수도 있음

 

문제496. 부서번호, 이름, 월급, 순위를 출력하는데 순위가 부서번호별로 각각 월급이 높은순으로 부여하시오

select deptno, ename, sal, rank() over (partition by deptno
                                        order by sal desc) as 순위
 from emp;

문제497. 위의 결과에서 1등들만 출력하시오

select *
 from ( select deptno, ename, sal, rank() over (partition by deptno
                                                order by sal desc) as 순위
         from emp )
 where 순위 = 1;

문제498. 서브쿼리를 view로 만들고 view를 select해서 순위가 1등인 사원들을 출력하시오. 

(view name = emp498)

create view emp498
as
select deptno, ename, sal, rank() over (partition by deptno
                                        order by sal desc) as 순위
 from emp ;
 
 
select *
 from emp498
 where 순위 = 1;

복잡한 쿼리를 view를 통해서 간단하게 쿼리할 수 있게 되었음. 

위의 view는 함수가 포함되어 있으므로 복합view ( view 생성시 쿼리에 함수가 있을 경우 반드시 별칭을 써줘야함)

 

 

문제499. 공공데이터포털에서 소상공인시장진흥공단_상가(상권)정보를 내려받아 테이블로 구성하시오. 

(테이블명 : market)

 

문제500. 서울시에 스타벅스 매장이 총 몇개가 있는가?

select *
 from market
 where 상호명 like '%스타벅스%';

문제501.시군구명을 중복제거해서 출력하시오 

select distinct 시군구명
 from market;

문제502. 시군구명, 시군구명별 스타벅스매장의 건수를 출력하시오

select 시군구명, count(*)
 from market
 where 상호명 like '%스타벅스%'
 group by 시군구명
 order by 2 desc;

문제503. 다음의 쿼리를 이용해서 서울시의 카페 매장의 상호명, 상호명 별 건수를 출력하는데 건수가 높은 것부터 출력하시오

select *

 from market

where 상권업종소분류명 like '%카페%';

 select 상호명, count(*)
  from market
  where 상권업종소분류명 like '%카페%'
  group by 상호명
  order by 2 desc;

문제504. 부서번호, 직업, 부서번호별 직업별 인원수를 출력하시오

select deptno, job, count(*)
 from emp
 group by deptno, job
 order by deptno, job;

문제505. 강남구의 가장 많은 업종이 무엇인지 다음과 같이 출력하시오.

시군구명, 상권업종소분류명, 시군구명별 상권업종소분류명별 건수를 출력하시오

select 시군구명, 상권업종소분류명, count(*)
 from market
 where 시군구명 = '강남구'
 group by 시군구명, 상권업종소분류명
 order by 3 desc;

 

 

ㅇ복합view

문제506. 사원이름, 부서위치를 출력하는 view를 생성하시오

create view emp506
as
select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno;

문제507. 위의 view를 이용하여 부서위치가 dallas인 사원들의 사원이름과 부서위치를 출력하시오

select *
 from emp506
 where loc = 'DALLAS';

문제508. emp506의 데이터를 수정하는데 KING의 부서위치를 CHICAGO로 변경하시오

update emp506
set loc = 'CHICAGO'
where ename = 'KING';
-- 에러 / 복합view라서 수정안됨

21c에서는 위의 update문이 수행됨. 18c는 수정되지 않음.

위의 update문이 실행되는 것은 부서테이블의 10번의 loc가 NEW YORK에서 CHICAGO로 변경된다는 것.

view는 데이터를 저장하고 있지 않음. view는 쿼리문일 뿐. 그래서 view를 쿼리하면 view를 만들었을 때의 쿼리문이 실행되는 것. view를 수정하면 실제 테이블이 수정되는 것.

 

문제509. 직업, 직업별 토탈월급을 출력하시오

select job, sum(sal)
 from emp
 group by job;

문제510. 위의 출력결과를 view로 생성하시오.

create view emp510
as
select job, sum(sal) as 토탈
 from emp
 group by job;

문제511. emp510 view를 수정하는데 SALESMAN의 토탈월급을 9000으로 변경하시오

update emp510
set 토탈 = 9000
where job = 'SALESMAN';
-- 에러 / 그룹함수를 사용한 복합view의 데이터는 수정할 수 없음

문제512. 내가 그동안 만든 view를 조회하시오

select *
from user_views;

문제513. 방금만든 emp510 view를 삭제하시오

drop view emp510;

 

 

096 복잡한 쿼리를 단순하게 하기 2(VIEW)

-- 사원테이블에서 부서번호, 부서번호별 평균월급을 출력하는 view를 emp_avg_sal로 생성하시오
create view emp_avg_sal
as
 select deptno, avg(sal) as 평균
  from emp
  group by deptno;

문제514. 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을 출력하시오

-- view를 이용한 쿼리
select e.deptno, e.ename, e.sal, a.평균
 from emp e, emp_avg_sal a
 where e.deptno = a.deptno;
 
-- 위 쿼리를 튜닝한다면, view를 굳이 사용하지 않아도 됨
select deptno, ename, sal, avg(sal) over (partition by deptno)
 from emp;

 

097 데이터 검색 속도를 높이기(INDEX)

인덱스(index) : 테이블의 데이터 검색속도를 높이기 위한 database object 

인덱스가 없어서 테이블을 끝까지 읽는 것 = full table scan

-- 월급이 1600인 사원의 이름, 월급을 조회하시오
 -- 인덱스없는 full table scan 방식
select ename, sal
 from emp
 where sal = 1600 ;
 
-- 인덱스 만드는 문법
create index emp_sal
on emp(sal);

-- 인덱스를 이용한 검색
select ename, sal
 from emp
 where sal = 1600;
/* whrer절에 월급으로 검색하면 오라클이 자동으로 emp_sal 인덱스를 먼저 검색해서 1600의 rowid를 알아내고
그 정보를 가지고 emp테이블에 가서 rowid 행을 찾아 이름과 월급을 조회 */

인덱스를 타고 실행된 쿼리

full scan한 쿼리

문제515. 아래의 sql의 검색속도가 빨라지도록 인덱스를 생성하시오

select ename, sal, job

from emp

where ename = 'SCOTT';

create index emp_name
on emp(ename);

emp_name인덱스의 구조는 ename데이터와 rowid로 구성되어 있음. 만약 emp_name인덱스의 구조를 보고 싶다면 아래와 같이 쿼리하면 됨

select ename, rowid
from emp
where ename > ' ';
       -- 모두 조회한다는 뜻 = 공백문자보다 큼

order by절을 쓰지 않아도 이름이 정렬되어 있음. 이것은 emp_name인덱스를 생성할 때 이름을 정렬해서 만들었다는 것이고 우리는 emp_name인덱스에서 데이터를 읽어서 본 것.

 

ㅇ인덱스의 구조

"인덱스 컬럼의 데이터 + rowid(해당 테이블의 물리적 주소)"

1. 인덱스 컬럼의 데이터악 asc하게 정렬되어있음

2. rowid는 테이블의 물리적 주소인데 책에 비유하자면 페이지 번호

 

문제516. emp_sal인덱스의 구조를 살펴보시오

select sal, rowid
from emp
where sal > 0;

인덱스 데이터 전체를 읽어오려면 where절에 다음과 같은 조건을 주면 됨.

1. 숫자 >= 0

2. 문자 > ' '

3. 날짜 < to_date('9999/12/31','RRRR/MM/DD')

 

ㅇ생성된 인덱스 조회하기

select *
 from user_indexes;

 

문제517. 이름이 JAMES인 사원의 이름, 월급, 부서번호를 출력하시오

select ename, sal, deptno
from emp
where ename = 'JAMES';

아까 emp_name이라고 emp테이블에 ename인덱스를 생성함. 그렇다면 위의 sql은 emp테이블의 ename의 인덱스를 사용해서 데이터를 검색했을까? 아니면 그냥 emp테이블을 full table scan 했을까?

> 이를 알기위해서는 실행계획을 볼 수 있어야 함.

인공지능화 되어가고 있는 오라클 옵티마이저가 어떻게 계획을 세웠는지 확인해볼 필요가 있음.

explain plan for
select ename, sal, deptno
from emp
where ename = 'JAMES';

select * from table(dbms_xplan.display);

만약 다음과 같이 index를 drop하면 어떻게 되는지 실행계획을 살펴보자.

drop index emp_name;

explain plan for
select ename, sal, deptno
from emp
where ename = 'JAMES';

select * from table(dbms_xplan.display);

문제518. 81년 11월17일에 입사한 사원들의 이름과 입사일을 출력하시오

select ename, hiredate
 from emp
 where hiredate = to_date('1981/11/17','RRRR/MM/DD');

문제519. 위의 sql의 검색속도를 높이기 위해 index를 생성하시오

create index emp_hiredate
on emp(hiredate);

문제520. sql이 인덱스를 통해서 데이터를 검색했는지 아니면 full table scan 했는지 실행계획을 확인하시오

explain plan for
select ename, hiredate
 from emp
 where hiredate = to_date('1981/11/17','RRRR/MM/DD');
 
select * from table(dbms_xplan.display);

문제521. 아래의 sql의 실행계획을 확인하시오

explain plan for
select ename, hiredate
 from emp
 where to_char(hiredate,'RR/MM/DD') = '81/11/17';
 
select * from table(dbms_xplan.display);

 

기존컬럼을 where절에서 가공하면 데이터를 제대로 읽기 못함. hiredate가 index를 읽지 못하고 full table scan함. 따라서 이 sql을 튜닝하자면 520번과 같은 sql로 작성해야함.

문제522. 아래의 sql을 튜닝하시오.

-- 튜닝 전
select ename, sal
 from emp
 where sal*12 = 36000;
 
-- 튜닝 후
select ename, sal
 from emp
 where sal = 36000/12;

where절에 검색조건 컬럼을 가공하게 되면 index access를 할 수 없음을 확인함.

 

문제523. 아래의 sql의 검색속도를 높이기 위해서 알맞은 index를 생성하시오

select ename, sal, job

from emp

where job = 'ANALYST';

create index emp_job
on emp(job);

문제524. 위의 emp_job 인덱스의 구조를 확인하시오. (인덱스의 구조는 컬럼값 + rowid)

select job, rowid
 from emp
 where job > ' ';

문제525. (오늘의 마지막 문제) 아래의 sql을 튜닝하시오

--튜닝 전
select ename, sal, job
 from emp
 where substr(job,1,5) = 'SALES';
 
--튜닝 후
create index emp_job
 on emp(job);
 
select ename, sal, job
 from emp
 where job like 'SALES%';
 
--실행계획 
explain plan for
select ename, sal, job
 from emp
 where job like 'SALES%';
 
select * from table(dbms_xplan.display);

인덱스 이용하여 검색조건 걸 때, 와일드카드가 앞에 있으면 안됨