2016년 8월 26일 금요일

08day SQL tunning

--인덱스 보기
SCOTT>col index_name format a15
SCOTT>col table_name format a15
SCOTT>select index_name, table_name
  2  from user_indexes;
--인덱스 삭제
SCOTT>drop index idx_emp_sal;
SCOTT>drop index idx_emp_ename;

--인덱스 생성 : 정렬 방법을 정할수 있음
SCOTT>create index idx_emp_sal
  2  on emp(sal desc);
**sal은 월급 많은 순으로 많이 보기때문에 내림차순으로 인덱스 만들어줬음

--인덱스 조건절 셀렉트
SCOTT>SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE SAL>2000;

--인덱스 생성
SCOTT>CREATE INDEX IDX_EMP_ENAME
  2  ON EMP(ENAME);

 --인덱스 2개사용시 OPTIMIZER는 하나를 선택함
SCOTT>SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE ENAME='SCOTT'
  4  AND SAL=3000;
**인덱스는 밸런스 트리를 구성하는데 최근에 만들어진 인덱스가 밸런스 트리를 가장 잘 유지하고 있음

--최근 SQL문장을 기본편집기에 적어줌
SCOTT>ED

--인덱스 2개 사용시 그중에 유니크 인덱스 있을 경우, 유니크 인덱스 사용함
  1  SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE ENAME='SCOTT'
  4* AND EMPNO=7788

--인덱스가 존재하지만 인덱스를 사용하지 못하게 하는것이 성능이 좋을떄가 있음
--EMP: 군인
--DEPTNO: 성별
--10: 남 , 20: 여 라고 가정해보자
--인덱스 생성
SCOTT>CREATE INDEX IDX_EMP_DEPT
  2  ON EMP(DEPTNO);
--그냥 셀랙트하면 인덱스를 참조함
SCOTT>SELECT EMPNO, ENAME, DEPTNO
  2  FROM EMP
  3  WHERE DEPTNO = 10;
--조건 컬럼에 연산을 하면 인덱스를 쓰지 않음
SCOTT>SELECT EMPNO, ENAME, DEPTNO
  2  FROM EMP
  3  WHERE DEPTNO+1 = 10+1;
--조건 컬럼에 연산을 하면 인덱스를 쓰지 않음2
SCOTT>SELECT EMPNO, ENAME, DEPTNO
  2  FROM EMP
  3  WHERE DEPTNO+0 = 10;
--문자열 인덱스를 사용하지 않을 경우 문자열 연산넣음
SCOTT>SELECT EMPNO, ENAME, DEPTNO
  2  FROM EMP
  3  WHERE ENAME||'' ='SCOTT';

--인덱스를 사용해야하는데 사용못하게 하는 자주있는 실수
  1  SELECT EMPNO, ENAME, DEPTNO
  2  FROM EMP
  3* WHERE UPPER(ENAME) ='SCOTT'
--함수를 사용하고도 인덱스를 사용하고 싶을경우 컬럼말고 값에 연산함
  1  SELECT EMPNO, ENAME, DEPTNO
  2  FROM EMP
  3* WHERE ENAME =UPPER('SCOTT')

--인덱스를 NULL 찾으면 FULL TABLE스캔
SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE ENAME IS NULL;

--문자 LIKE 연산 경우 와일트 캐릭터가 뒤에 올경우만 인덱스RANGE SCAN를 사용함
SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE ENAME LIKE 'SCO%';

--LIKE 연산 와일드 캐릭터가 앞에 올경우 인덱스 FULL스캔
SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  WHERE ENAME LIKE '%SCO%';

--연산한 결과를 조건절에 자주 사용한다면 인덱스를 만들때 부터 연산을 하고 만듦
SQL> SELECT EMPNO, ENAME, SAL*12, COMM
  2  FROM EMP
  3  WHERE SAL*12 >=36000;
--연산해서 인덱스 생성
SQL> CREATE INDEX IDX_EMP_SAL12
  2  ON EMP(SAL*12);
--다시 연산값 검색하면 인덱스 스캔을 하는 것을 볼수 있음
SQL> SELECT EMPNO, ENAME, SAL*12, COMM
  2  FROM EMP
  3  WHERE SAL*12 >=36000;

--조인
  1  SELECT E.ENAME, D.DNAME
  2  FROM EMP E, DEPT D
  3* WHERE E.DEPTNO = D.DEPTNO
** 읽는 순서
** 3번 인덱스 풀스캔
** 2번 태이블을 다읽음 결과적으로 테이블을 다읽은것
** 5번 태이블 풀 스캔
** 4번 2개 테이블을 정렬을 한뒤
** 1번 합쳐서 비교함
** 0번 선택
--가장 최악의 정렬이 SORT MERGE 임 인덱스는 읽으나 마나임
-- 해결책은 두테이블에 조인조건이 되는 컬럼에 인덱스를 모두 걸어주면 해결됨
   HASH JOIN 사용함

--인덱스 삭제
SQL> DROP INDEX IDX_EMP_SAL;
SQL> DROP INDEX IDX_EMP_SAL12;
--인덱스 생성
SQL> CREATE INDEX IDX_EMP_SAL
  2  ON EMP(SAL);

--급여가 가장 많은 사람의 이름 급여?
SQL> SELECT ENAME, SAL
  2  FROM EMP
  3  WHERE SAL IN (SELECT MAX(SAL) FROM EMP);
**인덱스가 정렬되어있음에도 불구하고 정렬을 다시함  SORT AGGREGATE
--OPTIMIZER에 인덱스 사용 힌트를 줄수 있음, 타당하다고 생각하면 힌트를 사용함
  1  SELECT /*+ INDEX_ASC(EMP, IDX_EMP_SAL) */ ENAME, SAL
  2  FROM EMP
  3  WHERE ROWNUM =1
  4  AND SAL>=0



--책 추천
대용량 데이터베이스 솔루션 1,2

댓글 없음:

댓글 쓰기