06day SQL tunning

Optimizer : sql명령문을 parsing 해서 실행계획 세우는 곳

--실행계획을 보는 방법  (권한이 없으면 볼수 없음)
SQL> set autotrace on

--권한 주기
SQL> conn sys/oracle as sysdba

--권한 롤이 저장된 파일

--롤 생성
SQL> @C:\app\user\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
--권한 롤 부여
SQL> grant plustrace to scott;

--명령어 파일저장
SQL> spool scott1
SQL>
SQL> --
SQL>
--현재 스풀중인지 확인
SQL> spool
scott1.LST에 스풀 중입니다.
SQL> spool off
SQL> exit
--새로 접속
SQL> sqlplus scott/tiger
--스풀 이어쓰기 (덮어쓰기가 디폴트)
SQL> spool scott1 append
SQL> --spool 이어쓰기 시작
SQL> spool off
--저장된 메모장 열기
SQL> host
C:\Users\user>notepad scott1.lst
C:\Users\user>exit
C:\Users\user>notepad scott1.lst
SQL>

--실행계획을 봄 (권한을 받은 상태에서는 에러메시지 출력하지 않음)
SQL> set autotrace on
--명령문 실행하면 실행계획 나옴
SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  ;

--실행계획 분석

*Plan hash value : 오라클의 해쉬함수로 찾아갈수 있는 Key 값
*Operation : 들여쓰기 되어있는 부분을 먼저 실행함
*FULL TABLE SCAN : 가장 최악의 속도 (오라클 스캔중 15번째) : 지금은 없지만 이전에 있었던 데이터 용량까지 다 읽음


--pseudo 컬럼
SQL> SELECT deptno, rownum, rowid
  2  FROM dept;
*rownum 행 몇번째
*rowid    행 고유주소 6.3.6.3

--테이블 깨질 때 가로화면 조정
SQL> SET LINESIZE 200

--ROWID 로 태이블 행 한개값을 바로 찾을 수 있음 (최적의 속도 1번째)
SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE ROWID='AAAR3sAAEAAAACXAAH';

--인덱스 있으면 DB가 데이터 빨리 찾기 위해서 따로 색인 테이블을 만들어둠
   인덱스를 알파벳순서대로 정리해두고, 다른 하나의 행은 ROWID값으로 구성됨
--인덱스 만들기
SQL> CREATE INDEX IDX_EMP_ENAME
  2  ON EMP(ENAME);
--인덱스 이용해서 찾기
SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE ENAME ='SMITH';

*INDEX RANGE SCAN : 인덱스테이블에 있을만한  어느정도 범위를 읽음
*TABLE ACCESS BY INDEX ROWID : 인덱스에 있는 ROWID를 찾음
*SELECT STATEMENT :ROWID로 데이터 찾음

--인덱스는 B*Tree(Balanced Tree)로 되어있음
--primary key지정하면 unique index 가 자동으로 생성됨
--다른 인덱스는 nonunique index 중복된값이 있을수 있음  --찾을때 일정 범위를 가져옴

--인덱스 보기
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
  2  FROM USER_INDEXES;

*INDEX_TYPE: NOMAL 은 밸런스 트리

--유니크 인덱스 찾기 VS  논유니크 인덱스
SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE EMPNO =7788
  4  AND ENAME ='SCOTT';

*인덱스로 먼저 찾고 나머지 조건은 필터로 처리

--인덱스 삭제
SQL> DROP INDEX IDX_EMP_ENAME;

--PK 인덱스는 삭제 불가
SQL> DROP INDEX PK_EMP;

--PK 삭제하려면 제약조건을 해제해야함
SQL> ALTER TABLE EMP
  2  DROP CONSTRAINT PK_EMP;

--제약조건 없애면 인덱스도 사라짐
SQL> SELECT INDEX_NAME, TABLE_NAME
  2  FROM USER_INDEXES;

--제약조건 다시 설정
SQL> ALTER TABLE EMP
  2  ADD CONSTRAINT PK_EMP PRIMARY KEY (EMPNO);