01/05 국비학원 29회차 오라클수업

시험

일반계정으로 다른 계정을 만들고 싶었던 문제...

사용자 계정으로 만들어줘야한다.

권한이 없기 때문이다.

 

계정 생성

  [id] [pw]는 자신이 생각하는 id와 pw로 입력하시면 됩니다.

  저는 sampleid와 samplepw로 설정해봤습니다.

 

create user [id] identified by [pw];

 

GRANT

권한 부여

  계정을 생성 한 후에 권한을 줘야합니다.

  connect(접속 권한), resource(객체 및 데이터 조작 권한), dba를 설정했습니다.

 

grant [권한] to [id];

grant connect, resource, dba to [id];

 

https://ajdahrdl.tistory.com/2

[Oracle] 계정 생성 및 권한 부여 방법

 

Like

https://gent.tistory.com/401

[Oracle] 오라클 LIKE 사용법 완벽 정리 (여러개, 패턴, 대소문자)

오라클 SQL에서 LIKE 연산자는 자주 사용하는 구문 중 하나이다. 문자열에서 와일드카드(%)를 사용하여 원하는 문자가 포함된 자료를 쉽게 검색할 수 있다. 단순 검색 외에도 대문자 소문자 구분 없이 검색, NOT 연산자 사용, 여러 개의 문자를 검색, 언더바(_)를 사용하여 자릿수 검색 등 다양한 사용법이 있다.

 

DECODE문제

직원의 급여를 인상하고자 한다
직급코드가 J7인 직원은 급여의 8%를 인상하고,
직급코드가 J6인 직원은 급여의 7%를 인상하고,
직급코드가 J5인 직원은 급여의 5%를 인상한다.
그 외 직급의 직원은 3%만 인상한다.

직원 테이블(EMP)에서 직원명(EMPNAME), 직급코드(JOBCODE), 급여(SALARY), 인상급여(위 조건)을
조회하세요(단, DECODE를 이용해서 출력하시오.) (

SELECT EMPNAME, JOBCODE, SALARY,
                DECODE(JOBCODE,'J7',SALARY*1.08,'J6',SALARY*1.07,'J5',SALARY*1.05,SALARY*1.03) AS "인상급여"
                FROM EMP
                
                                    
SELECT EMPNAME, JOBCODE, SALARY,
                CASE WHEN JOBCODE='J7' THEN SALARY*1.08
                            WHEN JOBCODE='J6' THEN SALARY*1.07
                            WHEN JOBCODE='J5' THEN SALARY*1.05
                            ELSE SALARY*1.03
                            END
                            AS "인상급여"
          FROM EMP

 

멤버쉽문제

(키프트 포인트의 포인트컬럼에서

상품이름이 영화티켓일때) 를 poi라고 정의

기프트멤버 mem

기프트멤버테이블을 mem이라고 정의  

문제2.

포인트 테이블에 있는 

SELECT * FROM t_giftmem
​
SELECT * FROM t_giftpoint
​
회원과 상품의 관계형태는 n:n이다.
​
SELECT NVL(comm, 0) FROM emp
​
SELECT empno FROM emp
​
SELECT
       /*+index_desc(emp pk_emp) */ empno
  FROM emp
 
​
SELECT
       /*+index_desc(emp pk_emp) */ empno
  FROM emp
WHERE rownum =1
​
/*+ */는 힌트문이라고 한다.
힌트문은 개발자가 옵티마이저에게 실행에 대한 요청이나 생각을 전달 할 수 있는
문장입니다.
만일 힌트문에 오타나 오류가 있더라도 에러가 발생하지는 않습니다.
다만 무시당할 뿐이죠
옵티마이저에게 내 생각을 전달할 때 사용한다고 생각하면 됩니다.
아래에서는 사원번호 최대값을 찾아야 하는데 다행히 empno가 pk이고
그래서 index가 제공되고 그러니까 order by 를 쓰지 않더라도 정렬이 일어납니다.
​
SELECT ename FROM emp는 정렬이 되지 않는 것이 그 이유입니다.
​
인덱스가 있는 경우에는 인덱스 이름이 있는데 이것을 이용해서 힌트문으로 옵티마이저에게 
개발자의 실행계획이나 의도를 전달할 수 있는 것임
​
인덱스의 경우 디폴트가 오름차순으로 정렬이 되어 있는데
힌트문을 이용해서 인덱스 access시에 내림차순으로 읽어올 것을 요청하는 것임
그러나 무시될 수도 있다는 것이 함정임
옵티마이저의 동작 원리에는 
​
rule base 옵티마이저 모드와- 15규칙에 순서에 따라 실행계획을 가져가는 방식임
:수동카메라- 개발자가 본인의 의도대로 조작이 가능
​
cost base옵티마이저 모드가 있음 - 이것은 데이터의 분포도와 통계자료를 바탕으로 실행계획을 가져가는 방식
:데이터의 분포도가 최신인가 중요함-잘못된 선택이 된다- 자동카메라- 효과적인 실행계획을 가져갈 확률이 높다
​
SELECT
       empno, ename, dname
  FROM emp, dept
 WHERE emp.deptno = dept.deptno
 
SELECT /*+ all_rows */
       empno, ename, dname
  FROM emp, dept
 WHERE emp.deptno = dept.deptno 
​
​
SELECT
       /*+index_desc(emp pk_emp) */ empno
      ,(empno+1)
  FROM emp
WHERE rownum =1
​
SELECT empno FROM emp
​
SELECT ename FROM emp
​
SELECT rowid rno FROM emp
​
SELECT ename
  FROM emp
 WHERE rowid = 'AAARE8AAEAAAACWAAG';
​
rowid 는 18자리
​
SELECT
       empno
  FROM emp
ORDER BY empno desc
​
​
SELECT
       empno
  FROM (
        SELECT
               empno
          FROM emp
        ORDER BY empno desc  
       )
WHERE rownum = 1
 
SELECT
       empno+1
  FROM (
        SELECT
               empno
          FROM emp
        ORDER BY empno desc  
       )
WHERE rownum = 1
​
--2023년 1월4일 숙제 풀이
​
SELECT * FROM t_giftmem
​
SELECT point_nu FROM t_giftpoint
 WHERE name_vc='영화티켓'
 
WHERE 회원집합.point_nu >= 상품집합.point_nu 
 
SELECT mem.name_vc as "회원명",
       mem.point_nu as "회원보유포인트"
  FROM (
        SELECT point_nu FROM t_giftpoint
         WHERE name_vc='영화티켓'  
       )poi, t_giftmem mem
WHERE mem.point_nu >= poi.point_nu        
​
SELECT mem.name_vc as "회원명"
      ,mem.point_nu as "회원보유포인트"
      ,mem.point_nu-poi.point_nu as "잔여포인트"
  FROM (
        SELECT point_nu FROM t_giftpoint
         WHERE name_vc='영화티켓'  
       )poi, t_giftmem mem
WHERE mem.point_nu >= poi.point_nu   
​
SELECT mem.name_vc as "회원명"
      ,mem.point_nu as "회원보유포인트"
      ,TO_CHAR(mem.point_nu-poi.point_nu,'999,999')||'점' as "잔여포인트"
  FROM (
        SELECT point_nu FROM t_giftpoint
         WHERE name_vc='영화티켓'  
       )poi, t_giftmem mem
WHERE mem.point_nu >= poi.point_nu
​
​
SELECT mem.name_vc as "회원명"
      ,mem.point_nu as "회원보유포인트"
      ,TO_CHAR(mem.point_nu-poi.point_nu,'999,999')||'점' as "잔여포인트"
  FROM t_giftpoint poi, t_giftmem mem
WHERE mem.point_nu >= poi.point_nu
  AND poi.name_vc = '영화티켓'
​
2.문제풀이
​
SELECT '한과세트', '김유신' FROM dual
​
SELECT
       poi.name_vc, mem.name_vc
  FROM t_giftpoint poi, t_giftmem mem
  
SELECT
       poi.name_vc, mem.name_vc
  FROM t_giftpoint poi, t_giftmem mem
 WHERE mem.name_vc = '김유신'  
  
SELECT
       poi.name_vc, mem.name_vc, poi.point_nu, mem.point_nu
  FROM t_giftpoint poi, t_giftmem mem
 WHERE mem.name_vc = '김유신'  
   AND poi.point_nu <= mem.point_nu
   
SELECT ename, max(sal) FROM emp  
​
SELECT max(sal) FROM emp    
   
SELECT max(ename), min(ename), max(sal) FROM emp  
​
SELECT
       poi.name_vc, mem.name_vc, max(poi.point_nu)
  FROM t_giftpoint poi, t_giftmem mem
 WHERE mem.name_vc = '김유신'  
   AND poi.point_nu <= mem.point_nu   
    
   
SELECT
       max(poi.point_nu)
  FROM t_giftpoint poi, t_giftmem mem
 WHERE mem.name_vc = '김유신'  
   AND poi.point_nu <= mem.point_nu   
   
SELECT name_vc, point_nu
  FROM t_giftpoint      
 WHERE point_nu = 50000 
​
​
SELECT name_vc, point_nu
  FROM t_giftpoint      
 WHERE point_nu = (
                    SELECT
                           max(poi.point_nu)
                      FROM t_giftpoint poi, t_giftmem mem
                     WHERE mem.name_vc =: uname 
                       AND poi.point_nu <= mem.point_nu    
                  )

일의 양을 줄이는데 실행계획이 중요하다

토드에서 컨트롤+E 실행계획 볼 수 있다.

그룹함수

 하나 이상의 행을 그룹으로 묶어 연산하여 총합(SUM), 평균(AVG) 등 하나의 결과로 나타낸다.

count 는 그룹함수이고 전체범위처리를 한다.

--그룹함수는 전체범위 처리를 한다. 속도가 느리다.
--전체범위 처리에 반대말은 부분범위처리이다.-속도 빠르다.
--count는 전체범위 처리를 한다.
--운반단위가 차면 fetch를 해서 화면에 출력이 나갈 수 있는 처리

 

삼성그룹에 직원이 50만명이라고 가정

김씨는 25만명이다라고 가정

해씨는 2명있다

 

김씨찾기 해씨찾기 더 빠른것은?

해씨가 훨 느리다.

김씨는 지나가는애 잡아도 둘중하나 김씨다 바로바로 찾아서 퍼올릴 수 있다.

 

김씨찾기가 훨씬 빠르다.

 

전체범위처리에서 부분범위처리로 바꿔야 빨라진다.

3초 안에 안튀어나오면 실패다. 빨리빨리 되어야한다.

 

rownum

stop키의 역할을 한다.

조회결과 중에서 한 건만 남김

 

힌트문 /*+ */

힌트문은 개발자가 옵티마이저에게 실행에 대한 요청이나 생각을 전달 할 수 있는 문장입니다.

만일 힌트문에 오타나 오류가 있더라도 에러가 발생하지는 않습니다.

다만 무시당할 뿐이죠

옵티마이저에게 내 생각을 전달 할 때 사용한다고 생각하면됩니다.

아래에서는 사원번호 최대값을 찾아야 하는데 다행히 empno가 pk이거

그래서 index가 제공되고 그러니까 order by 를 쓰지 않아도 정렬이 일어납니다.

select ename from emp는 정렬이 되지 않는 것이 그 이유입니다.

 

인덱스가 있는 경우에는 인덱스 이름이 있는데 이것을 이용해서 힌트문으로 옵티마이저에게

개발자의 실행 계획이나 의도를 전달할 수 있는것임

 

인덱스의 경우 디폴트가 오름차순으로 정렬이 되어 있는데 힌트문을 사용해서 인덱스 access시에 내림차순으로 읽어 올 것을 요청하는것이다.

그러나 무시될 수도 있다는 것이 함정임

 

옵티마이저의 동작원리

1. rule base 옵티마이저 모드

  -수동카메라. 개발자가 본인의 의도 대로 조작이 가능하다.

 

2. cost base 옵티마이저 모드

-이것은 데이터 분포도와 통계자료를 바탕으로 실행계획을 가져가는 방식

데이터의 분포가 최신인가가 중요함-잘못된선택이된다-자동카메라-효과적인 실행계획을 가져갈 확률이 높다.

SELECT empno FROM emp
​
SELECT
       /*+index_desc(emp pk_emp) */ empno
  FROM emp
 
​
SELECT
       /*+index_desc(emp pk_emp) */ empno
  FROM emp
WHERE rownum =1
​
SELECT
       /*+index_desc(emp pk_emp) */ empno
      ,(empno+1)
  FROM emp
WHERE rownum =1
​
SELECT
       empno
  FROM emp
ORDER BY empno desc
​
​
SELECT
       empno
  FROM (
        SELECT
               empno
          FROM emp
        ORDER BY empno desc  
       )
WHERE rownum = 1
 
SELECT
       empno+1
  FROM (
        SELECT
               empno
          FROM emp
        ORDER BY empno desc  
       )
WHERE rownum = 1

정렬하지도 않았는데 정렬해서 보여준다. 왜?

PK라서 인덱스가 있다.

ename은 인덱스가 없다. 

 

테이블 안에 각 데이터마다 튜플안에정보를 관리하는 메모리공간안에서의 주소번지를 갖고있다.

물리적인 공간이있다.저장소의 고유한 식별자 값을 통해서 접근하는것이다.

rowid값이다.

max는 전체함수라서 느리다.

그래서 힌트문을 사용