Oracle
19. Chapter12 Other DB Objects(20081105)
Gungume
2009. 1. 9. 10:50
Types of View (P12-7)
- Simple View
기반 테이블이 하나
함수 사용안함
그룹화 안함
Update, Insert, Delete 가능
- Complex View
기반 테이블이 여러개
함수 사용
그룹화 함
Update, Insert, Delete 불가능
- 부서번호가 10인 부서의 부서이름, 부서번호를 보는 뷰 생성
- // Simple View
- SQL> CREATE OR REPLACE VIEW dept10
2 AS
3 SELECT deptno, dname
4 FROM dept
5 WHERE deptno=10; - View created.
- SQL> SELECT * FROm dept10;
- DEPTNO DNAME
---------- --------------
10 ACCOUNTING - // Simple View라서 Update 가능
- SQL> UPDATE dept10
2 SET dname = 'accounting'
3 WHERE deptno=10; - 1 row updated.
- 부서번호별로 평균급여를 보는 뷰 생성
- // Complex View
- SQL> CREATE OR REPLACE VIEW avgsal
2 AS
3 SELECT deptno, avg(sal) as dasl
4 FROM emp
5 GROUP BY deptno; - View created.
- SQL> SELECT * FROM avgsal;
- DEPTNO DASL
---------- ----------
10 5000
20 5000
30 5000 - // Complex View라서 DELETE 문 안먹힘
- SQL> DELETE FROM avgsal WHERE deptno=10;
DELETE FROM avgsal WHERE deptno=10
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
- View를 통해서 INSERT 한경우 베이스테이블에 적용은 되지만 View를 통해서는 볼수 없는 경우 (P12-5 WITH CHECK OPTION 으로 해결~
- // View를 통해서 Insert
- SQL> INSERT INTO dept10
2 VALUES (90, 'development'); - 1 row created.
- // 실제 Base Table에는 Insert 되있음
- SQL> SELECT * FROM dept;
- DEPTNO DNAME COMMENTS LOC
---------- -------------- ------------------------------ --------------------
10 accounting
20 RESEARCH
30 SALES
40 OPERATIONS
50 Local Branch
90 development - 6 rows selected.
- SQL> CREATE OR REPLACE VIEW dept10
2 AS
3 SELECT deptno, dname
4 FROM dept
5 WHERE deptno=10; - View created.
- // view로 조회해보면 추가한 내용 안보임(view 생성 조건이 deptno가 10인 경우뿐이므로 의미 없음~)
- SQL> SELECT * FROM dept10;
- DEPTNO DNAME
---------- --------------
10 accounting
P12-8 예제
- SQL> conn test00/test00
Connected. - SQL> CREATE OR REPLACE VIEW detp_emp (did, dname, eid, ename)
2 AS SELECT d.id, d.name, e.id, e.last_name
3 FROM s_dept d, s_emp e
4 WHERE d.id = e.dept_id; - View created.
- SQL> SELECT * FROM detp_emp;
- DID DNAME EID ENAME
---------- ------------------------- ---------- -------------------------
50 Administration 1 Velasquez
41 Operations 2 Ngao
31 Sales 3 Nagayama
10 Finance 4 Quick-To-See
50 Administration 5 Ropeburn
41 Operations 6 Urguhart
42 Operations 7 Menchu
43 Operations 8 Biri
44 Operations 9 Catchpole
45 Operations 10 Havel
31 Sales 11 Magee
32 Sales 12 Giljum
33 Sales 13 Sedeghi
34 Sales 14 Nguyen
35 Sales 15 Dumas
41 Operations 16 Maduro
41 Operations 17 Smith
42 Operations 18 Nozaki
42 Operations 19 Patel
43 Operations 20 Newman
43 Operations 21 Markarian
44 Operations 22 Chang
34 Sales 23 Patel
45 Operations 24 Dancs
45 Operations 25 Schwartz - 25 rows selected.
- SQL> COL column_name FORMAT A20
- SQL> SELECT column_name, updatable, insertable, deletable
2 FROM user_updatable_columns
3 WHERE table_name = 'DEPT_EMP'; - COLUMN_NAME UPD INS DEL
-------------------- --- --- ---
DID NO NO NO
DNAME NO NO NO
EID YES YES YES
ENAME YES YES YES
- 부서별 최고급여 받는 사원 이름(Inline View로~, 기존에는 Select문 3개 사용했음)
- SQL> SELECT e.deptno, e.empno, e.ename, e.sal
2 FROM emp e, (SELECT deptno, max(sal) msal FROM emp GROUP BY deptno) dmax
3 WHERE e.deptno = dmax.deptno
4 AND e.sal = dmax.msal; - DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
20 7369 SMITH 5000
30 7499 ALLEN 5000
30 7521 WARD 5000
20 7566 JONES 5000
30 7654 MARTIN 5000
30 7698 BLAKE 5000
10 7782 CLARK 5000
20 7788 SCOTT 5000
10 7839 KING 5000
30 7844 TURNER 5000
20 7876 ADAMS 5000
30 7900 JAMES 5000
20 7902 FORD 5000
10 7934 MILLER 5000 - 14 rows selected.
INDEX (P12-14)
- 테이블 스캔 : SELECT로 검색시 인덱스 없이 처음부터 한개씩 비교하는 것 -> 속도 오래 걸림 -> Index로 해결
- Index는 data에 대한 조회 속도를 높이기 위해 사용
- Index를 위한 저장공간이 필요
- 즉, 데이터 검색 성능은 증가하지만 저장공간이 필요함
- 디스크 I/O과 줄어듬
- Index를 많이 만들수록 검색속도는 빨라지지만 Update, Delete 등의 갱신속도는 떨어짐 (데이터가 변경되면 인덱스도 함께 변경되야함)
- 테이블의 사용용도에 맞게 적절히 사용해야한다.
Types of Index (P12-15)
- Unique <-> Nonunique : 데이터에 중복된 데이터가 있다면 non~
- Automatic created <-> User create : UNIQUE, PRIMARY KEY 제약조건을 주면 자동으로 인덱스 생성됨(Automatic created)
CREATE INDEX (P12-16)
- SELECT * FROM emp WHERE sal*1.1 > 1000 : sal이 인덱스가 지정되있다고 해도 수행안됨
- SELECT * FROM emp WHERE sal > 1000 / 1.1 : 이렇게 해주면 가능, 자주 쓰인다면 해당 내용을 함수로~
Dictionary for Indexes (P12-18)
- SQL> SELECT t.index_name, t.uniqueness, c.column_name, c.column_position
2 FROM user_indexes t, user_ind_columns c
3 WHERE c.index_name = t.index_name
4 AND t.table_name = 'EMP'; - INDEX_NAME UNIQUENES COLUMN_NAME COLUMN_POSITION
------------------------------ --------- -------------------- ---------------
EMP_ENAME_IND NONUNIQUE ENAME 1
PK_EMP UNIQUE EMPNO 1
Needs for the Index (P12-19)
- WHERE절에서 Join 조건에 많이 사용되는 컬럼의 경우
- 데이터가 많은 테이블에서 적은 양의 데이터를 찾는 경우
- 값이 넒은 범위를 가진 컬럼(반대 = 남/여 같은 경우)
- 자주 갱신되지 않는 테이블의 경우
Sequence (P12-20)
- 일련번호 생성에 사용
- Unique한 Integer 사용함
- 게시판의 글번호 등에 사용
Using Sequence (P12-22)
- SQL> CREATE SEQUENCE seq_dept_no;
- Sequence created.
- SQL> SELECT seq_dept_no.nextval from dual;
- NEXTVAL
----------
1 - SQL> SELECT seq_dept_no.currval from dual;
- CURRVAL
----------
1
- 1번부터 시작해서 2씩 증가하는 Sequence(롤백해도 취소 안됨~, insert후 롤백하면 insert 한 데이터는 롤백되겠지만 sequence 넘버는 그대로~)
- SQL> CREATE SEQUENCE seq_dept_no
2 START WITH 1
3 INCREMENT BY 2; - Sequence created.
CREATE/DROP SYNONYM (P12-27)
- SQL> conn system/system
Connected.
SQL>
SQL> CREATE SYNONYM emp for scott.emp; - Synonym created.
- SQL> SELECT * FROM emp;
- EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 5000
20 - 7499 ALLEN SALESMAN 7698 81/02/20 5000 300
30 - 7521 WARD SALESMAN 7698 81/02/22 5000 500
30 - 7566 JONES MANAGER 7839 81/04/02 5000
20 - 7654 MARTIN SALESMAN 7698 81/09/28 5000 1400
30 - 7698 BLAKE MANAGER 7839 81/05/01 5000
30 - 7782 CLARK MANAGER 7839 81/06/09 5000
10 - 7788 SCOTT ANALYST 7566 87/04/19 5000
20 - 7839 KING PRESIDENT 81/11/17 5000
10 - 7844 TURNER SALESMAN 7698 81/09/08 5000 0
30 - 7876 ADAMS CLERK 7788 87/05/23 5000
20 - 7900 JAMES CLERK 7698 81/12/03 5000
30 - 7902 FORD ANALYST 7566 81/12/03 5000
20 - 7934 MILLER CLERK 7782 82/01/23 5000
10
14 rows selected.
이 글은 스프링노트에서 작성되었습니다.