01day SQL tunning

data_dictionary

• 접두사 + 관심 키워드+ 복수어
• USER_ : 내가 생성한 것과 관련된 정보
• ALL_ : 접근 권한이 있는 것과 관련된 모든 정보
• DBA_: 관리자만 봐야할 정보
• V$ :동적인것(접속자 수) , 성능과 연관된것(현재 메모리 사용)
• 튜닝할 떄 봄

--사용자 이름을 앞에 띄워줌
SQL> set sqlprompt "_USER>"
SCOTT>

--뷰 권한
SQL> grant create view to scott;

--뷰 생성
SCOTT>create view empvw30
as
select empno, ename, sal
from emp
where deptno =30;

--뷰의 물리적 형태는 텍스트
--별도의 저장장소가 있는것이 아님
SCOTT>select text
  2  from user_views
  3  where view_name='EMPVW30';

--뷰 선택
SCOTT>select * from empvw30;
--DB는 테이블명을 먼저 찾아보고 뷰이름을 찾아봄 --뷰이름이 있을때 쿼리문을 가져와서 채워넣음
SCOTT>select * from (select empno, ename, sal from emp where deptno=30);
--from 절에 사용되는 서브쿼리를 인라인뷰라고 함

• 뷰의 장점
• 복잡한 셀렉트문을 단순화 시킴
• 저장공간을 거의 사용하지 않음

--뷰 수정
--데이터 딕셔너리에 있는 object를 변경 하려면 create or replace
SCOTT>create or replace view empvw30
  2  as
  3  select empno, ename, comm, deptno
  4  from emp
  5  where deptno= 30;

--사용자 생성
SQL> create user acorn
  2  identified by tiger;
--ROLL connect 접속관련 권한 모아둔것 , resource 저장장소 권한 모아둔것
SQL> grant connect, resource to acorn;

• 권한의 종류
• SYSTEM : DB 전체에 영향 미침 : SYSTEM, SYS
• OBJECT : OBJECT에만 영향 미침 : 생성자

--권한부여
SCOTT> grant select on emp to acorn;

--다른사용자 테이블 보기   (스키마명.테이블명) : 스키마명은 유저명과 동일
ACORN>select * from scott.emp;

--권한 회수
SCOTT>revoke select on emp from acorn;

--뷰 선택권한 부여
SCOTT>grant select on empvw30 to acorn;

--다른사용자 뷰 보기
ACORN>select * from scott.empvw30;

--뷰 업데이트 권한부여
grant update on empvw30 to acorn;

--뷰 업데이트 하면 원래테이블 바뀜
ACORN>update scott.empvw30
  2  set comm=100
  3  where ename='BLAKE';

ACORN>commit;

• 수정가능 뷰 : SIMPLE VIEW
• create view aaa
  as
  select ??????
• 수정불가 뷰 : COMPLEX VIEW
• create view aaa
  as
  JOIN, DISTINCT, GROUP BY, HAVING, GROUP FUNCTION 사용

--수정불가 뷰 생성
SCOTT>create view dept_avg
  2  as
  3  select deptno, avg(sal) avgsal
  4  from emp
  5  group by deptno;

--수정불가 뷰 수정이 안되는 이유는 가상열이기 때문
SCOTT>insert into dept_avg
  2  values(40, 2500);
insert into dept_avg
*
1행에 오류:
ORA-01733: 가상 열은 사용할 수 없습니다