Re: 2025_12_26일 자습시간 문제3개 (이수자 평가 문제 포함)

[22기_권오현]

2025_12_16일 자습시간 문제3개

오늘의 마지막 문제1. (이수자 평가 제출물6번)

아래의 SQL을 튜닝하시오. emp 테이블을 2번 엑세스 하지 말고 1번만 엑세스되게 하고 튜닝 전후의 buffer 의 갯수로 비교해서 캡쳐하세요

--2025_12_16일 자습시간 문제3개
--오늘의 마지막 문제1. (이수자 평가 제출물6번)
--아래의 SQL을 튜닝하시오. emp 테이블을 2번 엑세스 하지 말고 1번만 엑세스되게 하고 
--튜닝 전후의 buffer 의 갯수로 비교해서 캡쳐하세요

--튜닝전
select ename, hiredate
 from emp
 where hiredate = ( select max(hiredate)
                        from emp ) ;

--ADAMS 83/01/15


--튜닝후
select /*+ index_desc(emp emp_hiredate) */ename, hiredate
 from emp
 where hiredate < to_date('9999/12/31','RRRR/MM/DD')
 fetch first 1 rows only;

--ADAMS 83/01/15

오늘의 마지막 문제2.

아래의 SQL을 튜닝하세요. 아래의 SQL이 order by 절을 사용하지 않게 SQL 을 튜닝하는데 인덱스도 알아서 생성하세요.

--오늘의 마지막 문제2. 
--아래의 SQL을 튜닝하세요. 아래의 SQL이 order by 절을 사용하지 않게 SQL 을 튜닝하는데 인덱스도 알아서 생성하세요.

--튜닝전
select  ename, age
 from dba22
 where telecom = 'kt'
 order by age  desc;

/*
장동현 41
권오현 31
황지설 30
김동하 29
박성준 25
김예찬 25
한규진 24
오상민 23
*/


--튜닝후

--인덱스 생성 필요
create index dba22_telecom on dba22(telecom);
create index dba22_age on dba22(age);

select /*+ index_desc(dba22 dba22_age)*/ename, age
  from dba22
 where telecom = 'kt'
   and age >= 0;

/*
장동현 41
권오현 31
황지설 30
김동하 29
박성준 25
김예찬 25
한규진 24
오상민 23
*/

오늘의 마지막 문제3.

아래의 SQL을 튜닝하세요. 버퍼의 갯수가 줄어들겠금 인덱스를 생성하세요. 튜닝 전후의 버퍼의 갯수를 비교해서 캡쳐하고 제출하세요

--오늘의 마지막 문제3. 
--아래의 SQL을 튜닝하세요. 버퍼의 갯수가 줄어들겠금 인덱스를 생성하세요.
--튜닝 전후의 버퍼의 갯수를 비교해서 캡쳐하고 제출하세요

--튜닝전
select  *
 from insurance
 where age = 27;
 /*
 27 male 29.2 0 yes southeast 18246.5
27 male 26 0 no northeast 3070.81
27 male 28.5 0 yes northwest 18310.74
27 female 24.1 0 no southwest 2974.13
27 male 32.6 3 no northeast 4846.92
27 male 42.1 0 yes southeast 39611.76
27 female 24.8 0 yes southeast 16577.78
27 male 18.9 3 no northeast 4827.9
27 female 36.1 0 yes southeast 37133.9
27 male 23.1 0 no southeast 2483.74
27 male 30.3 3 no southwest 4260.74
27 female 31.4 0 yes southwest 34838.87
27 female 23.2 1 no southeast 3561.89
27 female 18 2 yes northeast 15006.58
27 female 30.4 3 no northwest 18804.75
27 male 32.7 0 no southeast 2497.04
27 male 33.7 0 no southeast 2498.41
27 male 30.5 0 no southwest 2494.02
27 female 25.2 0 no northeast 3558.62
27 male 33.2 2 no northwest 4058.71
27 male 31.1 1 yes southeast 34806.47
27 female 34.8 1 no southwest 3578
27 female 30.6 1 no northeast 16796.41
27 female 20 3 yes northwest 16420.49
27 female 21.5 0 no northwest 3353.47
27 female 32.4 1 no northeast 18903.49
27 male 45.9 2 no southwest 3693.43
27 female 31.3 1 no northwest 3956.07
 */

--튜닝후
--인덱스 생성 필요
--create index insurance_age on insurance(age);

select /*+ index_asc(insurance insurance_age)*/ *
 from insurance
 where age = 27;
/*
27 male 29.2 0 yes southeast 18246.5
27 male 26 0 no northeast 3070.81
27 male 28.5 0 yes northwest 18310.74
27 female 24.1 0 no southwest 2974.13
27 male 32.6 3 no northeast 4846.92
27 male 42.1 0 yes southeast 39611.76
27 female 24.8 0 yes southeast 16577.78
27 male 18.9 3 no northeast 4827.9
27 female 36.1 0 yes southeast 37133.9
27 male 23.1 0 no southeast 2483.74
27 male 30.3 3 no southwest 4260.74
27 female 31.4 0 yes southwest 34838.87
27 female 23.2 1 no southeast 3561.89
27 female 18 2 yes northeast 15006.58
27 female 30.4 3 no northwest 18804.75
27 male 32.7 0 no southeast 2497.04
27 male 33.7 0 no southeast 2498.41
27 male 30.5 0 no southwest 2494.02
27 female 25.2 0 no northeast 3558.62
27 male 33.2 2 no northwest 4058.71
27 male 31.1 1 yes southeast 34806.47
27 female 34.8 1 no southwest 3578
27 female 30.6 1 no northeast 16796.41
27 female 20 3 yes northwest 16420.49
27 female 21.5 0 no northwest 3353.47
27 female 32.4 1 no northeast 18903.49
27 male 45.9 2 no southwest 3693.43
27 female 31.3 1 no northwest 3956.07
*/