잡동사니 보물섬

SQL튜닝(2)(1.10) - index ( index range scan, index full scan, index fast full scan, index skip scan )

워키토키3s 2016. 4. 1. 01:27

2016. 4. 1. 01:27

3) full table scan

- High water mark 까지 스캔하는 방법

(HWM - 포멧된 디스크와 포멧되지 안은 디스크의 경계선)
- full table scan 방법은 인덱스 스캔은 아니지만 인덱스가 없는 경우 발생하는 기본 스캔임으로 알아둘 필요가 있다.

⑴ Full table scan을 할 수 밖에 없는 경우

① 인덱스가 없을 때

② full 힌트를 사용하면

③ 인덱스를 생성할 때

④ 테이블의 통계정보를 수집할 때

⑵ Full table scan을 할 수밖에 없다면 full table scan 속도를 높이는 방법

① full table scan 에 관련한 오라클 파라미터를 조정

- 파라미터 : db_file_multiblock_read_count

( 이 파라미터는 full table scan 을 할 때 한번에 읽어들이는 블럭의 갯수를 결정하는 파라미터 )

alter session set db_file_multiblock_read_count = 128;

② 병렬로 작업한다.

select /*+ full(emp) parallel(emp,4) */ ename, sal, job

from emp

where job = 'SALESMAN';

⑶ full table scan 속도 향상 테스트

① set timing on

② create table sales800

select * from sh.sales ;

③ alter session set db_file_multiblock_read_count=4 ;

alter session set workarea_size_policy=manual ;

alter session set sort_area_size=10 ;

④ create index sales_index

on sales800(amount_sold, prod_id) ;

-- 시간 확인

⑤ drop table sales800 purge ;

⑥ 다시 scott 으로 접속해서

⑦ 2번부터 다시 수행하는데 아래와 같이 128로 설정하고 테스트

alter session set db_file_multiblock_read_count=128 ;

alter session set workarea_size_policy=manual ;

alter session set sort_area_size=10000000 ;

4) index range scan

- 인덱스의 일부분만 범위 스캔해서 DATA를 엑세스 스캔 방법

힌트 : /*+ index (emp emp_sal) */

(기본적으로 사용하는 hint 는 index range scan을 이용하라는 의미이다.)

⑴ 실습 환경 구성

drop table sales500 purge

create table sales500

select rownum rn , s.*

from sh.sales s;

create index sales500_rn on sales500(rn) ;

create index sales500_promo_id on sales500(promo_id);

튜닝 전 : select /*+ full(sales500) */ *

from sales500

where rn=299;

튜닝 후 : select /*+ index(sales500 sales500_rn) */ *

from sales500

where rn=299;

* 실행 통계 및 자동 추적 사용 시 (SQL gate )

db block gets ┐

consistent gets ┘ ------------> 메모리 영역

physical reads ------------------> 디스크 영역

5) index full scan

⑴ index full scan 의 종류

- index full scan : 인덱스 full 로 스캔

(힌트 : index_fs )

- index fast full scan : 인데스 full 로 스캔하는데 index full scan 보다 더 속도가 빠르다.

(힌트 : index_ffs ) (multiblock i/o를 하기 때문에)

⑵ index full scan 과 index fast full scan 의 차이

index full scan vs index fast full scan

- 인덱스 구조에 따라 스캔 세그먼트 전체를 스캔

- 순서가 보장 (정렬 됨) 순서가 보장이 안된다. (정렬이 보장이 안된다는 것이지 아예 안된다는 것은 아니다.)

- single block i/o multi block i/o

- 병렬 스캔이 블가능하다. 병렬스캔이 가능하다.

* SELECT /*+ index_fs(sales500 sales500_rn) parallel_index(sales500,sales500_rn,4) */ count(rn)

from sales500;

( 위의 SQL 은 index full scan으로 hint 를 사용하여 병렬처리는 실행계획에 포함되지 않는다.)

6) index skip scan 스캔 방법

- 인덱스를 full 또는 fast full 로 전체를 스캔하는 것이 아니라 중간 중간 skip 을 해서 사용

(힌트 : index_ss)

⑴ index skip scan 이란

- 인덱스의 첫번째 컬럼이 where 조건이 없어도 인덱스를 사용할 수 있게 한다.

( 이전에는 결합 컬럼 인덱스의 첫번째 컬럼이 where 조건에 있어야만 결합 컬럼 인덱스를 엑세스 할 수 있다. )

예시 ) index의 컬럼 순서에 따라서 index를 사용하지 못하는 경우

create index emp_deptno_sal

on emp(deptno, sal) ;

select ename, sal

from emp

where sal = 3000; ----------------> 위의 index 사용 못 함

select ename, sal

from emp

where deptno = 10; -----------------> 위의 index를 사용 함

( 실무에서는 인덱스를 임의로 추가하기 힘들다.)

SELECT /*+ index_ss(emp emp_deptno_sal) */ ename, sal

from emp

where sal = 3000;

⑵ index skip scan 스캔 방법 설명

SELECT /*+ index_ss(emp emp_deptno_sal) */ ename, sal

from emp

where sal = 2975;

DEPTNO SAL ROWID

10 1300 AAAg7WAAZAAAAfrAAQ

10 2975 AAAg7WAAZAAAAfrAAF

20 800 AAAg7WAAZAAAAfrAAN

20 1100 AAAg7WAAZAAAAfrAAP

20 2975 AAAg7WAAZAAAAfrAAG

20 3000 AAAg7WAAZAAAAfrAAM

20 3000 AAAg7WAAZAAAAfrAAO

30 950 AAAg7WAAZAAAAfrAAK

30 1250 AAAg7WAAZAAAAfrAAH

30 2975 AAAg7WAAZAAAAfrAAL

30 3500 AAAg7WAAZAAAAfrAAJ

30 3600 AAAg7WAAZAAAAfrAAI

30 4050 AAAg7WAAZAAAAfrAAE

- deptno 번호별 해당 sal 의 data 가 있으면 값을 찾고 다름 deptno 그룹으로 넘어간다.

- 해당 경우에서는 deptno의 종류가 적을 수록 효과적이다.

( 앞의 컬럼이 나누어지는 경우의 수가 적을 수록 더 빠르게 값을 찾을 수 있다.)

* 결합 컬럼 인덱스의 첫번째 컬럼이 where 절에서 선분조건 (betwwen .. and like) 으로 사용 된다면

인덱스를 넓게 읽을 수 밖에 없기 때문에 성능이 저하된다. 그럴때는 두가지 방법으로 튜닝한다.

1 index skip scan 사용

2 between .. and 를 in으로 변경

(그림은 index 사용 시에 대한 실행 내용)

문제 17. 아래 SQL 의 성능을 높일 수 있도록 인덱스를 생성하시오 !

튜닝 전 :
select *

from sales500

where cust_id=35834 ;

튜닝 후 :

CREATE INDEX sale500_cust ON SALES500(cust_id);

select /*+ index (sales500 sales500_cust) */ *

from sales500

where cust_id=35834 ;

문제18. EMP테이블에 사원 수가 몇명인지 조회하시오.

튜닝 전 :

select count(*)

from emp ;

튜닝 후 :

select /*+ index (emp emp_sal) */ sal

from emp ;

문제 19. 아래의 SQL 의 성능을 높일 수 있도록 인덱스를 생성하고 힌트를 주시오 !

튜닝전 :

select count(ename)

from emp;

튜닝 후 :

(인덱스 생성 후 create index emp_ename on emp(ename) ; )

SELECT /*+ index(emp emp_ename) */ COUNT (ename)

FROM EMP ;

더 성능이 좋게 하려면

SELECT /*+ index(emp emp_ename) */ COUNT (ename)

FROM EMP

WHERE ename > ' ' ;

- 더 성능이 좋을 것이라고 판단하는 것은 위의 SQL 은 index full scan이 실행계획에 포함되지만

- 아래 SQL은 index range scan 이 실행계획에 포함된다.

* ename 컬럼에 not null 제약이 걸려있어야 더 확실히 옵티마니져가 index full scan 이나 index fast full scan 을 할 수 있다.

문제 20. 직업에 인덱스를 걸고 아래의 SQL 의 성능을 높이시오

튜닝 전 :

select count(job) from emp ;

튜닝 후 :

CREATE INDEX EMP_JOB ON EMP(job);

SELECT /*+ index_ffs(emp emp_job)*/COUNT(job)

FROM EMP;

- 이 경우는 어차피 테이블의 모든 row를 보아야 하고 테이블도 작기 때문에 fast full scan을 하는 것이 더 나을 수 있다.

* 오라클 버전에 따라서 not null 조약에 따라서 index full scan이 되지 않을 수도 있다.

문제 21. 아래의 SQL의 성능을 높이시오 !

튜닝 전 :

select count(*)

from sales500;

튜닝 후 :

create index sales500_rn on sales500(rn) ;

select /*+ index_ffs (sales500 sales500_rn) count(rn)

from sales500;

문제 22. demo를 다시 돌리고 월급에 인덱스를 생성한 후에 월급이 0 이상인 사원들의 월급을 출력하는데 index fast full scan 이 되도록 하시오 !

SELECT /*+ index_ffs(emp emp_sal) */ sal

FROM EMP

WHERE sal > 0 ;

* 이 경우 index full scan hint를 주더라도 실행계획은 rang scan으로 짠다.

( 더 효율적인 방식으로 인식하여)

SELECT /*+ index_fs(emp emp_sal) */ sal

FROM EMP

WHERE sal > 0 ;

문제 23. 이름에 EN 또는 IN을 포함하고 있는 사원들의 이름과 월급과 직업과 부서번호를 출력하시오!

SQL> select ename, sal, job, deptno

from emp
where ename like '%EN%'
or ename like '%IN%'

* regexp_like 를 사용하여도 된다.

SQL> select ename, sal

from emp

where regexp_like(ename,'(EN|IN)');

문제 24. 아래의 환경을 구성하고 아래의 SQL을 튜닝하시오.

create table customers

as select * from sh.customers ;

create index customers_indx1

on customers (cust_street_address) ;

SELECT *

FROM customers

WHERE cust_street_address LIKE '%Ceneva%'

or cust_street_address LIKE '%Mcintosh%';

consistent gets -- 1528

* 각 튜닝 방법 적용 후에 consistent gets의 값

1) where 절에 like 대신 instr 함수를 사용한 경우

SELECT /*+ index(customers customers_indx1) */ *

FROM customers

WHERE INSTR(cust_street_address, 'Ceneva') >0

or INSTR(cust_street_address, 'Mcintosh') >0 ;

consistent gets -- 389

2) where절에 like를 사용한 경우

SELECT /*+ index(customers customers_indx1) */ *

FROM customers

WHERE cust_street_address LIKE '%Ceneva%'

or cust_street_address LIKE '%Mcintosh%';

consistent gets -- 586

3) where 절에 like를 사용하고 index full scan을 시도한 경우

SELECT /*+ index_fs(customers customers_indx1) */ *

FROM customers

WHERE cust_street_address LIKE '%Ceneva%'

or cust_street_address LIKE '%Mcintosh%';

consistent gets -- 1462 ( 실제 실행계획이 full table scan 함 )

4) where 절에 like를 사용하고 index fast full scan을 시도한 경우

SELECT /*+ index_ffs(customers customers_indx1) */ *

FROM customers

WHERE cust_street_address LIKE '%Ceneva%'

or cust_street_address LIKE '%Mcintosh%';

consistent gets -- 1462 (실제 실행계획이 full table scan 함)

5) Rowid를 사용해서 테이블 스캔이 일어나도록 하는 방식

SELECT /*+ no_query_transformation rowid(customers) */ a.*

FROM customers a, (SELECT /*+ index(customers customers_indx1) */ ROWID

FROM customers

WHERE cust_street_address LIKE '%Ceneva%'

or cust_street_address LIKE '%Mcintosh%') b

WHERE A.ROWID = B.ROWID ;

consistent gets -- 1725

* where 절에 like 사용 보다 instr 사용이 더 빨라진다.

문제 25. 부서번호의 종류가 몇가지 인지 출력하시오!

SQL> SELECT COUNT (*)

FROM (SELECT DISTINCT (deptno)

FROM EMP

WHERE deptno IS NOT null) ;

SQL> SELECT COUNT( DISTINCT deptno)

FROM EMP;

문제 26. 아래의 SQL을 튜닝하시오!

create index sales500_channel_custid

on sales (channel_id, cust_id)

튜닝 전 :

select /*+ full(sales500) */ *

from sales500

where cust_id = 2869 ;

consistent gets -- 4452

튜닝 후 :

select /*+ index_ss(sales500 sales500_channel_custid) */ *

from sales500

where cust_id = 2869 ;

consistent gets -- 200

* 해당 테이블에 alias를 사용하였다면 index 사용시 table 명에도 사용하는 alias를 사용해야 한다.

(alias를 사용하지 않으면 hint 가 제대로 사용되지 않는다. )

select /*+ index_ss(s sales500_channel_custid) */ *

from sales500 s

where cust_id = 2869 ;

문제 27. 아래의 SQL을 튜닝하시오 !

( 환경 설정 :

CREATE TABLE mcustsum

SELECT rownum custno

, '2008' || lpad(CEIL(rownum/100000), 2, '0') salemm

, decode(MOD(rownum, 12), 1, 'A', 'B') salegb

, round(dbms_random.VALUE(1000,100000), -2) saleamt

FROM dual

CONNECT BY level <= 1200000 ;

CREATE INDEX mcustsum_IDX2 ON mcustsum(salemm, salegb);

EXEC dbms_stats.gather_schema_stats('SCOTT');

튜닝 전 :

select count(*)

from mcustsum t

where salegb = 'A'

and salemm between '200801' and '200812';

-- 3367

-- index fast full scan : 실행계획

-- index range scan : 실제 실행계획

튜닝 후 :

SELECT /*+ index_ss(t mcustsum_IDX2) */ count(*)

from mcustsum t

where salegb = 'A'

and salemm between '200801' and '200812';

-- 300

-- index skip scan

* between .. and는 index range scan 과 index fast full scan 이 가능하다.

* 2가지를 고려해서 테스트 한 후 더 성능이 좋은 것을 사용하면 된다.

문제 28. 위의 SQL을 힌트쓰지 말고 재작성하는데 Between .. and 가 아니라 IN으로 변경해서 실행하시오

SELECT /*+ index(t mcustsum_IDX2) */ count(*)

from mcustsum t

where salegb = 'A'

and salemm IN ( '200801', '200802','200803','200804','200805','200806','200807','200808','200809','200810','200811', '200812' );

-- 304

* in을 사용해서 실행계획에서 index range scan이 사용 된다.

* in 사용 시 index_ss(skip scan) 을 사용해도 skip scan 으로 실행계획이 세워지지 않는다.

* 과거 skip scan 기능이 나오기 전에 사용한 튜닝 방법이다.

* 점심시간 문제 : 아래의 SQL 을 튜닝하시오!

(직업과 월급에 각각 인덱스가 있다.)

튜닝 전 :

select ename,job, sal, deptno

from emp

where job||sal = 'SALESMAN3000' ;

-- 41

-- full table scan

튜닝 후 :

select ename, job, sal ,deptno

from emp

where job = 'SALESMAN'

and sal = 3000;

-- 1

-- index range scan

* 비교 방법

db block gets ┐

consistent gets ┘ ------------> 메모리 영역

physical reads ------------------> 디스크 영역

* 인덱스를 각각 생성하지 않아도 가능하다.

CREATE INDEX emp_job_sal ON EMP(job, sal);

* 오늘의 마지막 문제:

아래 SQL 을 튜닝하시오 !

(그룹 함수 쓰지 말고 sal 인덱스만 이용해서 결과를 출력하시오!)

튜닝 전 :

select max(sal)

from emp ;

튜닝 후 :

SELECT /*+ index_desc(emp emp_sal) */ sal

FROM EMP

WHERE sal > 0

AND ROWNUM = 1 ;

( - 따로 sal 에 index를 걸어주었을 때)

SELECT sal

FROM (SELECT /*+ index_ss_desc(emp emp_deptno_sal) */ sal

FROM EMP

WHERE sal > 0)

WHERE ROWNUM = 1;

( - 기존에 걸어둔 emp_deptno_sal (deptno, sal) index를 사용하려면 )

* 실행 통계를 보기 전에 analizy를 하는 것이 더 정확하다

* 간혹 SQL gate 버전에 따라서 실행 통계 값이 부정확 할 수 있다.

'ORACLE > SQL 튜닝' 카테고리의 다른 글

SQL튜닝(4) - IOT (index organizaion table) 구성, 클러스터 테이블 구성, 다중 클러스터 테이블 구성 (0)	2016.04.11
SQL튜닝(3) - index(index merge scan, index bitmap merge scan, index join, index uniqe scan), table random access 부하 (0)	2016.04.06
SQL튜닝(1) - index (index 구조, index 사용 시 주의 사항, index 스캔 방식의 종류 ) (0)	2016.03.15

SQL(3)- order by 복습, 단일 함수 中 문자 함수

워키토키3s 2016. 3. 29. 17:36

2016. 3. 29. 17:36

문제 59. 직업이 SALESMAN이 아닌 사원들의 이름과 월급과 직업과 입사일을 출력하는데,

먼저 입사한 사원순으로 출력하시오

SQL> 3 SELECT ename, sal, JOB, hiredate
1 FROM EMP
2 WHERE JOB != 'SALESMAN'

4 ORDER BY hiredate ASC;

*keyword 앞에 번호들은 실행 순서

문제 60. 20번 부서번호에서 근무하는 사원들의 이름과 월급과 연봉(sal*12)을 출력하는데

연봉이 높은 사원부터 출력하시오

SQL> SELECT ename, sal, sal*12 as "연봉"
FROM EMP
WHERE deptno = 20

ORDER BY "연봉" DESC;

문제 61. 우리반 테이블에서 이름과 나이를 출력하는데 나이가 높은 사람부터 출력하시오!

SQL >SELECT ename, age

FROM emp2

ORDER BY age DESC;

문제 62. 우리반 테이블에서 여학생의 이름과 주소와 나이를 출력하는데

나이가 높은 순서대로 출력하시오!

SQL>SELECT ename, address, age

FROM emp2
WHERE gender = '여자'

ORDER BY age DESC;

문제 63. 우리반 테이블에서 성씨가 김씨인 학생의 이름과 나이를 출력하시오!

SQL>SELECT ename, age

FROM emp2

WHERE ename LIKE '김%';

문제 64. 나이가 30대인 학생들의 이름과 성별과 나이를 출력하시오!

SQL>SELECT ename, gender, age

FROM emp2

WHERE age BETWEEN 30 AND 39;

문제 65. 서울에 사는 학생들에 이름과 주소를 출력하시오!

SQL>SELECT ename, address

FROM emp2

WHERE address LIKE '%서울%';

문제 66. 전공이null인 학생들의 이름과 전공을 출력하시오!

SQL> SELECT ename, major

FROM EMP2

WHERE major is NULL;

주의! null 같은 '='가 아닌 is 를 사용

문제 67. 전공을 출력하는데 중복제거해서 출력하시오!

SQL> SELECT DISTINCT major

FROM EMP2;

문제 68. 전공이 컴퓨터 공학이 아닌 학생들의 이름과 전공을 출력하시오!

SQL > SELECT ename, major

FROM EMP2

WHERE major <> '컴퓨터공학';

* null 이 아닌 이상 'is'를 쓰지 않는다.

문제 69. 주소가 서울이 아닌 학생들의 이름과 주소를 출력하시오!

SQL > SELECT ename, address

FROM EMP2

WHERE address NOT LIKE '%서울%';

* 'not like' 도 사용가능

문제 70. 학원까지 오는 시간이 가장 적게 걸리는 학생부터 이름과 주소와 시간을 출력하시오!

SQL > SELECT ename, address, distance

FROM EMP2

ORDER BY distance ASC;

문제 71. 전공이 컴퓨터 공학이고 성별이 여학생인 학생들의 이름과 전공과 주소를 출력하는데,

집과의 걸리가 먼 학생순으로 출력하시오

SELECT ename, major, address
FROM EMP2
WHERE major ='컴퓨터공학' AND gender ='여자'

ORDER BY distance DESC;

문제 72. 같은 라인에 있는 학생들의 결과가 아래와 같이 출력되게하시오!

곽민영 학생의 나이는 34이고 전공이 컴퓨터 공학이며 집에서 학원으로 오는 시간은 3분 입니다.

SELECT ename||' 학생의 나이는'||age||'이고 전공이 '||major||'이며 집에서 학원으로 오는 시간은 '||distance||'분 입니다.'
FROM EMP2

WHERE empno BETWEEN 16 AND 19;

* MS_SQL 구현시

SELECT concat( ename,' 학생의 나이는',age,'이고 전공이 ',major,'이며 집에서 학원으로 오는 시간은 ',distance,'분 입니다.')

FROM EMP2

WHERE empno BETWEEN 16 AND 19;

3장 함수

- 함수를 왜 배워야하는가?

아래와 같은 정보을 알기 위하여 배워야 한다.

ex)

우리반 테이블에서 전공, 전공별 인원수를 출력하시오!

우리반 테이블에세 가장 나이가 어린 학생의 이름을 출력하시오!

메일 도메인, 메일 도메인별 건수를 출력하시오!

- 함수의 종류 2가지?

1. 단일행 함수

- 문자

- 숫자

- 날짜

- 변환

- 일반

1) 문자 함수

1. 대소문자 변환 함수

- lower : 소문자로 출력하는 함수

- upper : 대문자로 출력하는 함수

- initcap: 첫번째 철자는 대문자 나머지는 소문자로 출력하는 함수

2) 문자 조작 함수

- concat : 두개의 컬럼의 값을 연결하는 함수

- substr : 특정 철자를 잘라내는 함수

- length : 철자의 길이를 출력하는 함수

- instr : 특정 철자의 위치를 출력하는 함수

- lpad : 왼쪽에 특정값을 채워넣는 함수

- rpad : 오른쪽에 특정값을 채워넣는 함수

- trim : 특정 철자나 값을 잘라내는 함수

- replace : 특정 철자로 변경하는 함수

2. 복수행 함수

- max

- min

- count

- avg

- sum

문제 73. 아래와 같이 결과를 출력하시오!

SQL> select upper(ename), lower(ename), initcap(ename)

from emp;

* MS_SQL 로 작성시

- MS_SQL은 inticap 함수가 없어서 Oracle의 inticap 함수 처럼 나타내기 위해서는 아래와 같이 사용해야 한다.

SQL> select upper(ename), lower(ename), concat(upper(left(ename,1)),lower(substring(ename,2,10)))

from emp;

문제 74. 이름이 smith인 사원의 이름과 월급을 출력하시오!

아래 ? 부분을 채워 넣으시오!

where ? = 'smith'

SQL> SELECT ename, sal
FROM EMP

WHERE LOWER(ename) = 'smith';

* 자료가 대문자 인지 소문자 인지 확실히 모를 때 유용하게 사용할 수 있다.

문제 75. 이름과 월급을 붙여서 출력하시오!

SQL> select concat(ename, sal)

from emp;

* concat은 2개의 컬럼만 붙일 수 있다.

* MS_SQL 에서 concat 함수

- MS_SQL에서는 concat 안에 여러개의 컬럼은 붙일 수 있다.

문제 76. 우리반 테이블에서 이름과 나이를 붙여서 출력하시오!

SQL> SELECT CONCAT(ename, age)

FROM EMP2;

문제 77. 이름을 출력하는데 이름의 첫번째 철자만 출력하시오!

SQL> select ename, substr(ename,1,1)

from emp;

* substr(컬럼명, 숫자1, 숫자2)

- 숫자1 : 시작점을 의미한다.

- 숫자 2: 시작점 부터 몇개를 출력할 것인지 입력한다.

* MS_SQL 에서 해당 철자만 사용하기

- Oracle substr 함수에 해당 하는 MS_SQL 함수는 substring 이다.

SQL> select ename, substring(ename,1,1)

from emp;

문제 78. 이름의 세번째 철자만 출력하는데 소문자로 출력하시오!

SQL>SELECT LOWER(substr(ename,3,1))

FROM EMP;

* MS_SQL 에서 출력 방법

SQL> SELECT LOWER(substring(ename,3,1))

FROM EMP;

문제 79. 아래의 쿼리의 결과를 initcap 사용하지 말고,

upper, lower, substr || 를 사용해서 출력하시오!

예시) select initcap(ename)

from emp;

SQL> SELECT UPPER(substr(ename,1,1))||LOWER(substr(ename,2,8))

FROM EMP;

문제 80. 우리반 테이블에서 성씨가 김씨, 이씨, 박씨인 학생들의 이름과 주소를 출력하시오!

(like 사용하지 말고)

SQL> SELECT ename, address
FROM EMP2

WHERE substr(ename,1,1) IN ('김','이','박');

* MS_SQL에서 출력 방법

SQL> SELECT ename, address
FROM EMP2

WHERE substring(ename,1,1) IN ('김','이','박');

문제 81. 이름과 이름의 철자의 갯수를 출력하시오 !

SQL> select ename, length(ename)

from emp;

* MS_SQL 에서 철자의 갯수 출력 방법

- Oracle length에 해당하는 MS_SQL 의 함수는 len 이다

SQL> select ename, len(ename)

from emp;

문제 82. 이름, 이메일, 이메일의 철자의 갯수를 출력하는데,

이메일의 철자의 갯수가 가장 많은 학생부터 출력하시오!

SQL>SELECT ename,email, length(email)
FROM EMP2

ORDER BY LENGTH(email) DESC;

Tip.

select 절에서 length에 alias 를 주고 order by에 alias를 활용하여도 됨

* MS_SQL에서 위와 같은 사용 법

SQL>SELECT ename,email, len(email)
FROM EMP2

ORDER BY LEN(email) DESC;

문제 83. 이름 이메일의 도메인만 출력하시오!

SQL> SELECT substr(email,instr(email,'@')+1,(instr(email,'.')-instr(email,'@')-1))

FROM EMP2;

* 다른 방법들도 있다. length를 사용해서 뒤에서 부터 빼주는 방법도 있다.

SQL> SELECT substr(email,instr(email,'@')+1, length(email)-instr(email,'@')-4 )

FROM EMP2;

* MS_SQL 로 위의 쿼리 변환 시

- 함수 변화가 필요하다.

SQL> SELECT substring(email,patindex('%@%',email)+1,(patindex('%.%',email)-patindex('%@%',email)-1))

FROM EMP2 ;

문제 84. 이름을 출력하고 그 옆에 이름에 철자 A가 몇번째 있는지 출력하시오!

SQL> select ename, instr(ename, 'A')

from emp;

*instr/ 해당 문자가 몇 번째에 위치하는지 알수 있게 해주는 keyword

* MS_SQL에서 instr 대체 사용법

- MS_SQL에서 instr을 대체하는 함수는 patindex 이다

SQL> select ename, patindex('%A%',ename)

from emp;

문제 85. 우리반 테이블에서 이메일을 출력하고

그 옆에 이메일의 @ 몇번째 자리에 있는지 출력하시오!

SQR> SELECT email, instr(email,'@')

FROM EMP2;

문제 86. 이메일을 출력하는데 @ 다음의 철자부터 출력하시오!

SQR> SELECT substr(email,instr(email,'@')+1)

FROM EMP2;

*substr에서 두번째 자리에 아무것도 쓰지 않으면 끝까지 출력 된다.

* MS_SQL에서 substring 사용법

- MS_SQL에서 substring은 무조건 3개의 인자가 있어야지 쿼리가 실행된다.

- 아래의 쿼리는 필요 인자 부족으로 error가 발생한다.

SQL> SELECT substring(email,patindex('%@%',email)+1)

FROM EMP;

문제 87. 이름과 월급을 출력하는데 월급을 10자리로 출력하고,

월급이 출력되는 나머지 자리에*를 출력하시오!

select ename, lpad(sal, 10, '*')

from emp;

*lpad/rpad

- 자리 개수를 선정하고 나머지는 다른 것으로 채우겠다.

- ex) lpad(컬럼명, 자리개수, 채울 내용)

* MS_SQL 에서 lpad 대체법

- MS_SQL은 lpad가 사용되지 않는다. 그래서 replicate를 사용해서 쿼리를 작성해야 한다.

또한, 숫자와 같은 경우 형변환이 일어나서 기존에 숫자 값도 형변환을 해줘야 한다.

SQL> select replicate('*',10-len(sal))+cast(sal as varchar)

from emp ;

문제 88. 아래의 data를 입력하고 이름이 JACK인 사원의 이름과 월급을 출력하시오!

SQL> insert into emp(empno, ename, sal)

values(1235,' JACK ', 3600);

select ename, sal

from emp

where trim(ename) = 'JACK';

*trim

- 앞, 뒤의 공백을 지워준다. (단, 중간에 공백은 지워주지 못한다.)

* MS_SQL 의 Trim 함수 대체 방법

- MS_SQL은 trim 함수를 사용할 수 없어서 ltrim 과 rtrim을 중첩 사용하여 Oracle의 trim 함수와 같은 효과를 낼 수 있다.

SQL> select ename, sal

from emp

where rtrim(ltrim(ename)) = 'JACK';

문제 89. 이름과 전공을 출력하는데 전공을 출력할때에 과 가 안나오게 하시오!

SQL> SELECT ename, TRIM('과' FROM major)

FROM EMP2;

* MS_SQL 에서 Trim 함수 대체 방법 2

- MS_SQL 에서는 Trim 함수를 사용할 수 없어서 replace를 활용하여서 쿼리를 작성해야 한다.

SQL> SELECT ename, replace(substring(major,1,1),'과','') + substring(major,2,(len(major)-2)) + replace(substring(mjor,len(ename),1),'과','')

FROM EMP2 ;

문제 90. 이름과 월급을 출력하는데 월급을 출력할때에 0을 *로 출력하시오!

SQL> select ename, replace(sal, 0, '*')

from emp;

* replace

- 대체하는 keyword

- replace(컬럼명, 대체하고 싶은 문자 or 숫자, 대체할 숫자 or 문자)

- regexp_replace(sal, '[0-2]', '*')를 사용해서 좀 더 확장할 수 있다.

문제 91. 우리반 테이블에서 20대인 학생들 중에 naver 메일을 사용하는 학생들의 이름과 주소와 나이와 메일주소를 출력하는데, 나이가 높은순서대로 출력을 하고 그리고 컬럼명이 아래와 같이 출력되게 하시오!

학생이름, 사는 곳, 메일주소, 나이

SQL> SELECT ename AS "학생이름", address AS "사는곳", email AS "메일주소", age AS "나이"

FROM EMP2
WHERE age BETWEEN 20 AND 29
AND email LIKE '%naver%'

ORDER BY age DESC;

'ORACLE > SQL' 카테고리의 다른 글

SQL(4) - 단일함수 [숫자(round, trunc, mod), 날짜(between months, add-month, 변화, 일반] (0)	2016.04.05
SQL(2)- as, SQL 작성 규칙, " ", distinct, 대체 인용 연산자(q'[ ]'), 날짜 출력, 날짜 세션 변경, order by, 오름차순(asc), 내림차순 (decsend), 연산자 우선순위 규칙, 테이블 생성, 데이터 입력 (0)	2016.03.23
SQL(1)- select,from, where 연산자(산술, 비교, 논리, 기타논리 (between, in, is null, like(%,_,escape))) (0)	2016.03.15

password file, parameter file , 데이터베이스 환경(standard alone 과 rac), connection과 session의 차이, 오라클의 메모리 구조 (SGA 영역 中 shared pool)

워키토키3s 2016. 3. 23. 17:25

2016. 3. 23. 17:25

5) password file

- 특별한 권한을 가진 유저를 인정해주기 위한 파일 (ocm)

(db를 올리고 내릴 수 있는 권한)

* 패스워드 파일의 위치 확인 방법

$cd $ORACLE_HOME/dbs

$ls -l orapw*

* 패스워드 파일에 등록된 유저를 확인하는 방법?

SQL> select *

from v$pwfile_users;

* 패스워드에 등록하기 유저

SQL> grant sysdba to scott;

( But. scott으로만 접속하면 db를 내릴 수 없다. )

(SQL> connect scott/oracle_4U as sysdba 이렇게 접속해야 사용할 수 있다.)

(sysdba의 비밀번호)

* 패스워드 파일을 이용하여 remote로 연결시 sysdba 권한으로 연결하는 방법

sqlplus sys/oracle_4U@orcl as sysdba

( @orcl을 붙여준 이유는 리스너를 타고 접속하겠다는 뜻, 즉 다른 말로 말하면 원격에서 내 db로 sys 유저로 접속할 수 있게 하겠다.)

6) parameter file

- 인스턴스의 구조정보를 담고 있는 파일

* 파라미터 파일의 위치 확인

SQL> show parameter spfile

* 파라미터 파일의 내용을 확인하는 방법

SQL> create pfile='/home/oracle/initorcl.ora'

from sqfile='+DATA/orcl/spfileorcl.ora';

$cd /home/oracle

$vi initorcl.ora

7) database configuration

가. non clustered system

나. clustered system (=RAC)

real Application Cluster

* 노드 (컴퓨터 묶음의 단위) / 기본적으로는 4노드로 주로 사용/ 이론적으로는 100대까지 묶을 수 있음

* 24시간 중단없는 서비스를 지원받을 수 있다는 것이 장점

8) connection 과 session의 차이

* connection 이란

- user process가 instance와 통신할 수 있는 상태가 되었다.

client -----------------> server

user process server process

SQL ------------------> DATA

<-----------------

* instance란

- 오라클의 메모리

* session 이란

- instance에 connection해서 connection을 끊을때까지의 한 단위가 session

9) 오라클 메모리 구조

* 오라클 메모리 2가지는 무엇인가?

가. SGA (System(or shared) Global Area)

- 여러 서버프로세서들과 백그라운드 프로세서들이 공유해서 사용하는 메모리 영역

(현재 sga 영역의 사이즈

SQL> select name, value

from v$sga;)

(1) SGA 영역 중 shared pool의 역활

- parsing을 최소화하기 위한 오라클 메모리 영역

- parsing은 SQL을 기계어로 변경하는 과정

- select 문 처리 과정

① parsing ---> SQL ---> 기계어

- 문법 검사 : SQL 문법에 이상이 없는지 확인

- 의미 검사 : 1) object 확인 : emp 테이블의 존재

2) 권한 확인 : emp 테이블을 엑세스 할 수 있는 권한 체크

② execute ---> Data 를 검색

③ fetch --> 결과를 전달

* shared pool 에 parsing 결과를 올려 놓는다.

(- 결과물 3가지

1. SQL 문장

2. 실행 계획

3. 실행 코드 : SQL 문장을 c언어를 통해 실행코드로 변환시킨다.

(- 3가지 결과물을 동일한 결과가 입력될 시 공유해서 사용하기 위해서 shared pool에 위의 3가지 결과를 올린다.)

2) PGA (Program Global Area)

- 서버 프로세서들의 개별 메모리 영역이고 이 영역에서 data 정렬작업이 발생한다.

문제7. scott 유저를 패스워드 파일 등록리스트에서 제외하시오!

SQL> revoke sysdba from scott;

문제 8. sysdba로 접속하는 유저의 패스워드를 oracle로 지정하고 패스워드의 대소문자를 반드시 구분되게 하시오! (m)

- 패스워드 파일을 rm으로 날린다.

( rm orapw*)

- orapwd file=orapworcl password=oracle ignorecase=N (entries=5 ; 해당 비밀번호를 받을 사람을 5명으로 제한)

(orapw + instance 이름이 규칙)

* orapwd : 오라클 패스워드 파일을 만드는 유틸리티

* SQL> select name from v$database; ( instance 이름을 확인하는 방법)

(인스턴스 (오라클의 메모리) 이름 확인)

문제 9. 오라클에 sysdba 권한을 가진 유저로 오라클 db에 접속할 때 패스워드를 oracle_4U로 지정되게하고 대소문자를 구분하지 않아도 접속되게해보시오!패스워드 파일에 등록될 유저를 3명으로 제한하시오!

$ rm orapw*

$ orapwd file=orapworcl password=oracle_4U ignorecase=Y entries=3

문제 10. scott 과 같은 일반유저로 접속시 대소문자 구분을 안하겠금 설정하려면 어떻게 해야하는가?

SQL> show parameter sec_case_sensitive_logon

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon boolean TRUE

(value 값을 false로 바꿔야 한다.)

SQL> alter system set sec_case_sensitive_logon=false;

문제 11. 오라클의 접속되어져있는 세션들을 확인하시오!

SQL> select username, status

from v$session;

* 실행시 보이지 않는 user은 background 프로세서이다.

문제 12. scott으로 접속한 세션을

SQL> select spid

from v$process

where addr = (

select paddr

from v$session

where sid = ( select sid

from v$mystat

where rownum=1) );

* spid로 확인한 후 수행한 것들

ps -ef |grep 15969

top -p 15969

kill -9 15969

문제 13. Parsing을 과다하게 일으키는 프로그램을 돌리고 top으로 cpu 사용율을 확인하시오!

<하드 parsing 문>

SQL> declare

type rc is ref cursor;

l_rc rc;

l_dummy all_objects.object_name%type;

l_start number default dbms_utility.get_time;

begin

for i in 1 .. 50000

loop

open l_rc for

'select object_name from all_objects where object_id = ' || i;

fetch l_rc into l_dummy;

close l_rc;

end loop;

dbms_output.put_line ( round( (dbms_utility.get_time - l_start)/100, 2) || 'seconds');

end;

Mem: 2075456k total, 2014260k used, 61196k free, 179876k buffers
Swap: 4128760k total, 148480k used, 3980280k free, 1404340k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

16198 oracle 25 0 634m 137m 119m R 99.6 6.8 4:09.27 oracle

4887 oracle 15 0 410m 23m 11m S 0.3 1.2 83:02.18 oraagent.bin

14408 oracle 15 0 73116 13m 9332 R 0.3 0.7 0:11.75 gnome-terminal

16214 oracle 15 0 2336 1104 792 R 0.3 0.1 0:00.17 top

1 root 15 0 2072 624 532 S 0.0 0.0 0:06.29 init

2 root RT -5 0 0 0 S 0.0 0.0 0:01.33 migra

<이 부분은 추후 스크린샷으로 대체>

문제 14. emp 테이블에 db 에 존재하는지 확인하시오!

SQL> select table_name

2 from dba_tables

3 where table_name = 'EMP'

* user < all < dba 순으로 많은 정보를 가지고 있다.

문제 15. scott 유저에서 아래의 SQL을 실행하고 shared pool에 올라가져 있는지 확인하시오!

-scott 유저

SQL> select empno, ename

2 from emp

3 where empno =7788

EMPNO ENAME
---------- ----------
7788 SCOTT

- sys 유저

SQL> select sql_text, executions
2 from v$sql
3 where sql_text like 'select empno%';

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
select empno, ename from emp where empno =7788

* 똑같은 문장이 들어와야지 동일한 쿼리 결과를 이용할 수 있다.

* 똑같은 문장 ; 1. 대소문자가 동일, 2. 공백도 동일, 3. 소유자도 동일

문제 16. 아래의 문장을 이번에는 대문자로 실행하고 shared pool에 새롭게 파싱해서 올렸는지 아니면 기존의 parsing 정보를 활용했는지 확인하시오!

SQL> select EMPNO, ENAME

from EMP

where EMPNO =7788

SQL> select sql_text, executions

2 from v$sql

3 where sql_text like 'SELECT EMPNO%'or

4 sql_text like 'select empno%' ;

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
select empno, ename from emp where empno =7788
2

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO=7788

문제 17. 아래의 SQL을 수행하고 다시 파싱했는지 확인하시오!

SQL> select empno, ename from emp where empno =7788

(공백이 다르게 들어감)

select sql_text, executions

2 from v$sql

3 where sql_text like '%EMPNO%'

SQL> ed
Wrote file afiedt.buf

1 select sql_text, executions

2 from v$sql

3 where sql_text like '%empno%' /

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
select sql_text, executions from v$sql where sql_text like 'select empno%'
         2

select sql_text from v$sql where sql_text like 'SELECT EMPNO%'or       sql_text
like 'select empno%'
         1

select empno,             ename from emp where empno =7788
         1

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------

select empno, ename from emp where empno =7788
         2

select sql_text, executions from v$sql where sql_text like '%empno%'
         1

select sql_text, executions from v$sql where sql_text like 'empno%'
         1

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------

select sql_text from v$sql where sql_text like 'select empno%'
         4

select sql_text, executions from v$sql where sql_text like 'SELECT EMPNO%'or
    sql_text like 'select empno%'
         1

8 rows selected.

문제 18. 아래의 SQL을 수행하는데 smith 유저를 생성해서 수행해보시오

- scott

select empno, ename from where empno = 7788;

- smith

select empno, ename from emp where empno=7788;

1. smith 유저 생성

2. smith 유저로 접속해서

3. demobld.sql을 돌린다.

* 고민해야 할 문제 (이부분은 Linux 문제) :

asm 를 입력하면 asmcmd> 로 바로 들어갈 수 있도록 하시오

$ export ORACLE_SID=+ASM

$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid

$asmcmd

asmcmd>

$.oraenv

$ ? +ASM

$ asmcmd

* export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid

- application을 작동 시킬때는 해당 application이 실행 될 수 있도록 home 값을 지정해주어야 한다.

'ORACLE > ORACLE Admin' 카테고리의 다른 글

Admin1(1) - 오라클의 접속환경, database 구성요소(datafile, controlfile, redo log file, archive logfile ) (0)	2016.03.15

PREV 이전 1 ···23 24 25 26 27 28 29 ···35 NEXT 다음

잡동사니 보물섬

SQL튜닝(2)(1.10) - index ( index range scan, index full scan, index fast full scan, index skip scan )

'ORACLE > SQL 튜닝' 카테고리의 다른 글

SQL(3)- order by 복습, 단일 함수 中 문자 함수

'ORACLE > SQL' 카테고리의 다른 글

password file, parameter file , 데이터베이스 환경(standard alone 과 rac), connection과 session의 차이, 오라클의 메모리 구조 (SGA 영역 中 shared pool)

'ORACLE > ORACLE Admin' 카테고리의 다른 글

+ Recent posts

티스토리툴바