💡 집합 연산자의 종류 4가지
union all: 합집합union: 합집합 + 중복 제거intersect: 교집합minus: 차집합
문제1. 부서번호, 부서번호별 토탈월급을 출력하시오
select deptno, sum(sal)
from emp
group by deptno;문제2. 위의 결과를 다시 출력하는데 맨 아래에 전체 토탈월급을 출력하시오
select deptno, sum(sal)
from emp
group by rollup(deptno);문제3. 위의 결과를 rollup 사용하지 말고 union all 로 수행하시오
select deptno, sum(sal)
from emp
group by deptno
union all
select null as deptno, sum(sal)
from emp;💡 두 select의 컬럼명 맞춰줘야됨
union all: 합집합 연산자. 2개의 SQL의 결과를 위아래로 같이 출력해주는 집합 연산자
문제4. 직업과 직업별 토탈월급을 출력하시오
select job, sum(sal)
from emp
group by job;문제5. 입사한 년도를 4자리로 출력하고 입사한 년도별 토탈월급을 출력하시오
select to_char(hiredate, 'RRRR') 입사연도, sum(sal) 토탈월급
from emp
group by to_char(hiredate, 'RRRR');문제6. 위에는 직업, 직업별 토탈월급을 출력하고 아래는 입사연도, 입사한 연도별 토탈월급을 출력하시오
select job 집계컬럼, sum(sal) 토탈월급
from emp
group by job
union all
select to_char(hiredate, 'RRRR'), sum(sal)
from emp
group by to_char(hiredate, 'RRRR');문제7. (SQLD/P문제) 위의 결과를 union all 사용하지 말고 grouping sets 를 이용해서 수행하시오
select job 집계컬럼, to_char(hiredate, 'RRRR') 입사년도, sum(sal) 토탈월급
from emp
group by grouping sets((job), (to_char(hiredate, 'RRRR')));문제8. 아래의 grouping sets 의 결과를 union all 로 수행하시오
select job, to_char(hiredate,'RRRR'), sum(sal)
from emp
group by grouping sets( (job),(to_char(hiredate,'RRRR')), () );
select job 직업, null 입사년도, sum(sal) 토탈월급
from emp
group by job
union all
select null, to_char(hiredate, 'RRRR'), sum(sal)
from emp
group by to_char(hiredate, 'RRRR')
union all
select null, null, sum(sal)
from emp;문제9. SQL 튜닝전후의 보다 충분한 정보를 보기위해서 statistics_level 이라는 파라미터를 all 로 변경하고 db 를 내렸다 올리시오
명령 프롬프트 창에서 sys 유져로 접속해서 다음과 같이 조회합니다.
sqlplus "/as sysdba"
show parameter statistics_level
alter system set statistics_level=all scope=both;
startup force
문제10. SQL튜너들만 사용하는 실행계획 보는 방법을 다음과 같이 실행
-- 튜닝전SQL
select job, null, sum(sal)
from emp
group by job
union all
select null, to_char(hiredate,'RRRR'), sum(sal)
from emp
group by to_char(hiredate,'RRRR')
union all
select null, null, sum(sal)
from emp;
-- 실행계획 확인하기
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 21 |
| 1 | UNION-ALL | | 1 | | 10 |00:00:00.01 | 21 |
| 2 | HASH GROUP BY | | 1 | 14 | 5 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 |
| 4 | HASH GROUP BY | | 1 | 14 | 4 |00:00:00.01 | 7 |
| 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 |
| 6 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 |
| 7 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------💡 BUFFER
: 오라클 메모리에 데이터를 올린 하나의 크기
- 버퍼의 갯수가 많을수록 많은 메모리를 사용한 것
- 같은 결과를 보는 SQL이라도 버퍼의 갯수가 작을수록 튜닝된 SQL이라고 볼 수 있음
select job, to_char(hiredate,'RRRR'), sum(sal)
from emp
group by grouping sets( (job),(to_char(hiredate,'RRRR')), () );
-- 실행계획 확인하기
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 9 | | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 10 |00:00:00.01 | 9 | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660B_9B65C8 | 1 | | 0 |00:00:00.01 | 8 | 1024 | 1024 | |
| 3 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | | |
| 4 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660C_9B65C8 | 1 | | 0 |00:00:00.01 | 0 | 1024 | 1024 | |
| 5 | HASH GROUP BY | | 1 | 1 | 5 |00:00:00.01 | 0 | 1200K| 1200K| |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_9B65C8 | 1 | 14 | 14 |00:00:00.01 | 0 | | | |
| 7 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660C_9B65C8 | 1 | | 0 |00:00:00.01 | 0 | 1024 | 1024 | |
| 8 | HASH GROUP BY ROLLUP | | 1 | 1 | 5 |00:00:00.01 | 0 | 1230K| 1230K|73728 (0)|
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_9B65C8 | 1 | 14 | 14 |00:00:00.01 | 0 | | | |
| 10 | VIEW | | 1 | 1 | 10 |00:00:00.01 | 0 | | | |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_9B65C8 | 1 | 1 | 10 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------💡 튜닝 전 buffer갯수 21개 —> 튜닝 후 buffer 갯수 9개
문제11. 아래의 SQL을 튜닝하시오
select job, null, sum(sal)
from emp
group by job
union all
select null, deptno, sum(sal)
from emp
group by deptno
union all
select null, null, sum(sal)
from emp;
-- 실행계획 확인하기
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));답
select job, deptno, sum(sal)
from emp
group by grouping sets( job, deptno, () );
-- 실행계획 확인하기
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));