220520 SQL INDEX, VIEW,JOIN

[학습목표]
1. Index : 책의 목차과 비슷

2. View : 가상의 테이블(약속), 논리(하드디스크에 x), 
3. ***Join : 여러가지 테이블을 한 개로 합치는것. 이콜조인, 아우터조인, 인터조인, 셀프조인, 내추럴조인

---

▶INDEX

▷정의 

- 특정데이터를 빨리 찾기위해 사용

 

▷용도

- SELECT문과 WHERE절을 사용하는 경우(검색 시) 자주사용
- 꼭 필요한 내용만, 검색속도개선이 목적

- 기본키를 만드는 순간 자동생성

- 테이블의 데이터 갱신 시 자동으로 인덱스의 내용도 갱신

 

▷ 종류

- B-Tree(가장많이쓰임, 컬럼의 값과 rowid물리적위치정보를 기반으로 저장)*

- Bitmap(성별,결혼여부,탈퇴여부 등 2진형태), 
- function Based(조건절에 함수를 사용하여 검색하는 경우가 많을때, 다양한 함수사용)

 

▷Index 생성

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(칼럼명[, 칼럼명...])

▷Index 삭제

DROP INDEX (인덱스명)

▷Index를 생성하지 않는 경우

- 테이블이 작은 경우

- 갱신이 자주 발생하는 테이블

- 대부분의 검색 결과가 전체행의 10~15%이상인 경우

▶VIEW 

▷정의

- 테이블이나 다른 뷰에서 만들어진 논리적인 테이블

 

 

▷용도

- 기존에 생성된 테이블 또는 다른 뷰에서 접근할 수 있는 전체데이터 중에서

  일부만 접근할 수 있도록 제한하기 위함

 

▷ VIEW의 생성

OR REPLACE : 같은 이름의 뷰가 있을 경우 무시하고 다시 생성
FORCE : 기본 테이블의 유무에 상관없이 뷰 생성
NOFORCE : 기본 테이블이 있을 때만 뷰를 생성

 

▷ VIEW의 삭제

- DROP VIEW

▶JOIN***

 

▷정의

- 두 개 이상의 테이블들을 연결 또는 결합하여 데이터 출력

 

▷종류
- 모이면 센 우리

- 관절은 센 정형외과
   암기연상법 CENOS

- Cartesion product : 모든 행과 열의 조합, 모든 경우의 수를 따짐
- Equal join : Cartesion product 후 컬럼의 값이 똑같은 것을 찾음
- Non - equal join : Equal이외에 같지 않은 것을 찾음, 조건연산자 ,조건지정가능
- Outer : Equal join의 결과를 base로 깔고, 특정 팀의 모든 정보포함
- Self join : 한 집합이 스스로 복제 후 서로 join시

 

---

실습

--p224

--회원생일이 조건절에 자주 사용되어 Index를 사용
SELECT ROWID 
	, MEM_ID
	, MEM_NAME
	, MEM_JOB
	, MEM_BIR
FROM MEMBER
WHERE MEM_ID='a001';

--회원생일이 조건절에 자주 사용되어 Index를 생성-> 검색속도개선, 느려질 수도 있기때문에 DBA에게 허락받기(함부로 생성X)
CREATE INDEX IDX_MEMBER_BIR -- 인덱스명은 임의로 지정가능
ON MEMBER(MEM_BIR);

SELECT ROWID 
	, MEM_ID
	, MEM_NAME
	, MEM_JOB
	, MEM_BIR
FROM MEMBER
WHERE MEM_BIR LIKE '75%';

--회원생일에서 년도만 분리 후 인덱스 생성(Function -based Index)
CREATE INDEX IDX_MEMBER_BIR_YEAR
ON MEMBER(TO_CHAR(MEM_BIR, 'YYYY'));

SELECT MEM_ID
     , MEM_NAME
     , MEM_JOB
     , MEM_BIR
FROM MEMBER
WHERE TO_CHAR(MEM_BIR, 'YYYY') = '1975';


--P230 인덱스삭제
DROP INDEX IDX_MEMBER_BIR;

--IDX_MEM_BIR_YEAR 인덱스는 REBUILD(목차에 변경이 생길 시 다시 목차 생성,최신정보로 업뎃)
ALTER INDEX IDX_MEMBER_BIR_YEAR REBUILD;

--INDEX KEY COLUMN의 변형을 막는 QUERY문 사용권장
SELECT BUY_DATE
    ,  BUY_PROD
    ,  BUY_QTY
FROM BUYPROD
WHERE BUY_DATE -10 = '2005-02-20';

--재구성
SELECT BUY_DATE
    ,  BUY_PROD
    ,  BUY_QTY
FROM BUYPROD
WHERE BUY_DATE  = TO_DATE('2005-02-20') +10;

--PK_CART 인덱스가 걸려 있는데 SUBSTR함수에 의해 변형
SELECT CART_NO
    , CART_PROD
    , CART_QTY
FROM CART
WHERE SUBSTR(CART_NO,1,8)= '20050405';

--변형되지 않도록 보정

SELECT CART_NO, CART_PROD, CART_QTY
FROM CART
WHERE CART_NO LIKE '20050405%';

SELECT CART_NO
    , CART_PROD
    , CART_QTY
FROM CART
WHERE CART_NO > '2005040500000'
AND     CART_NO < '2005040599999';

--P225
--VIEW
--TABLE과 유사한 객체(가상의 테이블)
--CREATE는 생성, REPLACE는 원래 있는 것을 재정의
CREATE OR REPLACE VIEW VIEW_PROD1(분류,상품수)
AS
SELECT PROD_LGU
      , COUNT(*)
FROM PROD
GROUP BY PROD_LGU;

SELECT*FROM VIEW_PROD1
WHERE 상품수>1;

--VIEW MEMBER생성
CREATE OR REPLACE VIEW VIEW_MEMBER
(memName, memId, memPass, memMile, memBir, memJob, memLike)--카멜표기법
As
SELECT MEM_NAME
    ,  MEM_ID
    ,  MEM_PASS
    ,  MEM_MILEAGE
    ,  MEM_BIR
    ,  MEM_JOB
    , MEM_LIKE
FROM MEMBER;

SELECT*FROM VIEW_MEMBER; --물리적 공간차지X

--VIEW MEMBER생성
--회원명, 아이디, 마일리지. 생일, 직업

CREATE OR REPLACE VIEW VIEW_MEMBER
(memName, memId, memPass, memMile, memBir, memJob, memLike)
As
SELECT MEM_NAME
    ,  MEM_ID
    ,  MEM_PASS
    ,  MEM_MILEAGE
    ,  MEM_BIR
    ,  MEM_JOB
    , MEM_LIKE
FROM MEMBER
WHERE MEM_MILEAGE>1000
WITH CHECK OPTION;
--WITH CHECK OPTION
--가상의 테이블인 뷰를 통해 실 데이터를 INSERT/UPDATE/DELETE 시
--WHERE 조건을 준수하는지 CHECK하는 OPTION임

--READ ONLY
--읽기 전용 뷰. 뷰를 통해서 INSERT/UPDATE/DELETE할 수 없음

--WITH CHECK OPTION과 READ ONLY는 동시에 사용할 수 없음

UPDATE VIEW_MEMBER
SET memMile = 3000--2300->900(1000미만으로)
WHERE memId = 'b001';

INSERT INTO VIEW_MEMBER(memName,memId,memPass,memMile,memBir,memJob,memLike)
VALUES('개똥이','A011','1111',2014,'2015/12/23','프로그래머','자바');

SELECT * FROM VIEW_MEMBER;

DELETE FROM VIEW_MEMBER
WHERE memId = 'a011';

SELECT * FROM MEMBER;

--P242 JOIN
--RDB의 핵심, 관계형DB의 큰 장점은 많은 테이블을 조인하여 원하는 결과 도출하는 것

--p243 조인 기초 쿼리
SELECT PROD_ID AS PROD1
      ,PROD_NAME AS PROD2
      ,LPROD_GU AS PROD3
      ,PROD_LGU AS PROD4
      ,LPROD_NM AS LPROD2
FROM LPROD, PROD; --모든 행과열의 조합으로 총 66행나옴

--CARTESIAN PRODUCT(카티전프로덕트)
--모든 행과 열이 조합

SELECT LPROD_GU AS LPROD1
      ,BUYER_ID AS BUYER1
      ,BUYER_LGU AS BUYER2
FROM LPROD, BUYER;


SELECT LPROD_GU AS LPROD1
    , LPROD_NM AS LPROD2
      ,BUYER_ID AS BUYER1
      ,BUYER_NAME AS BUYER2
      ,BUYER_LGU AS BUYER3
FROM LPROD, BUYER;-- 실행결과 이상함=>분류코드가 같은 것만 찾아야함

--EQUAL JOIN 동등조인, 내부조인
SELECT LPROD_GU AS LPROD1
    , LPROD_NM AS LPROD2
      ,BUYER_ID AS BUYER1
      ,BUYER_NAME AS BUYER2
      ,BUYER_LGU AS BUYER3
FROM LPROD, BUYER
WHERE LPROD_GU = BUYER_LGU;