쿼리를 짜다보면 여러 행을

하나의 행으로 묶어서 보여줘야 할 때가 있다. 

여러 행을 하나로 합치는 건 기본적으로 집계이다.

그렇기 때문에 GROUP BY를 사용해 기준을 주어서 묶고 

함수를 사용해서 결과를 만들어야 한다.

이때, 여러 행을 ', '로 구분해서 합쳐주는 함수가

WM_CONCAT 이다. 

예시

다음의 예시를 통해 사용방법을

쉽게 익힐 수 있을 것이다.

개인별로 지원해본 회사명을 

입력한 테이블이 있다고 가정할 때,

이 테이블의 내용을 개인당 한 행으로

합쳐서 보이도록 하는 예시이다.

SELECT 이름,WM_CONCAT(회사명) AS "회사명"
FROM (
    SELECT 이름,회사명
    FROM TB_COM 
    )
GROUP BY 이름

도움이 되었다면

로그인이 필요없는 공감 버튼 꾹 눌러주세요! 

WITH 문

WITH [테이블명] AS (
    SELECT ... 
    FROM ...
    -- [임시테이블 SQL]
)
SELECT ..
FROM ... 
-- [사용 테이블]

쿼리를 만들다 보면 하나의 SQL문에서

가공한 테이블을 계속해서 사용해야 할 때가 있다.

가공한 테이블이 쿼리 안에서 계속 쓰다 보면 

가독성이 떨어져 혼란스럽고 개발하기 복잡해지는 경우가 종종 있다.

예시 

다음의 쿼리는 별로 복잡하지 않지만
엄청 복잡한 쿼리라고 가정해보자

-- 자주 조회할 테이블

SELECT T1.키,T1.컬럼1, T2.컬럼2
FROM T1 
    LEFT OUTER JOIN T2 
    ON T1.키 = T2.키

이제 이 쿼리는 복잡한 쿼리를 가져와서 

조건, 조인 등으로 사용하면서 더더욱 복잡해질 것이다.

-- 복잡해진 쿼리

SELECT A.키, TEST1.컬럼2-A, TEST2.컬럼2-B
FROM A
    LEFT OUTER JOIN 
    (
    SELECT T1.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
    ) TEST1
    ON A.키 = TEST1.키
    AND TEST1.구분코드 = 'A'
    
    LEFT OUTER JOIN 
    (
    SELECT T2.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
    ) TEST2
    ON A.키 = TEST2.키
    AND TEST2.구분코드 = 'B'

복잡해서 잘 안읽히는 코드는 유지보수에도 어려움이 있기 때문에 
최대한 보기 좋게 짜는 것도 요령인듯하다
이런 점에서 WITH 문을 활용할 수 있다

-- 간결해진 쿼리

-- 자주 조회할 테이블
WITH TEMP AS(
    SELECT T1.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
)
-- 메인 쿼리
SELECT A.키, TEST1.컬럼2-A, TEST2.컬럼2-B
FROM A

    LEFT OUTER JOIN TEMP TEST1
    ON A.키 = TEST1.키
    AND TEST1.구분코드 = 'A'
    
    LEFT OUTER JOIN TEMP TEST2
    ON A.키 = TEST2.키
    AND TEST2.구분코드 = 'B'

도움이 된 정보였다면

로그인이 필요 없는 공감 버튼 꾹 눌러주세요! 

테이블에 컬럼 추가하는건 가끔하는데

매번 까먹어서 찾는다 ㅠㅠ

나름 DB 공부를 열심히 한다고 했고

DB 관련 자격증도 몇개 취득했음에도...

자주 사용하지 않으면 까먹는 건 어쩔 수 없나보다..!

화면에서 게시글이 보여질지 여부를 판단하기 위해서

컬럼을 하나 추가하는 작업이 었다.

오라클에 사용했고

DEFAULT로 'true'가 들어가게 해놓았다.

-- ALTER TABLE 테이블명 ADD (컬럼명 자료형 DEFAULT '값');
ALTER TABLE BOARD_CONTENT ADD (VIEW_YN varchar (5) DEFAULT 'true');

1. ORA-28000 에러

SCOTT 계정으로 접속해서 EMP테이블로 실습을 하려고하는데

다음과 같은 오라클 에러가 나왔어요... ㅠ 

계정이 잠겨있다는 말인데 계정의 잠김 여부는 어디서 확인할 수 있고 어떻게 풀 수 있는지 확인해보겠습니다!

2. 해결

USER의 잠김 상태확인

SYS계정으로 접속해서 DBA권한으로 유저들을 확인해보면 잠겨있는지 여부를 확인할 수 있어요.

수많은 USER들이 나오기 때문에 조건문에 SCOTT을 추가하여 검색해보겠습니다. 

SCOTT의 ACCOUNT_STATUS를 보니 패스워드 기한만료와 계정 잠금상태였네요.

여기서 잠깐 ACCOUNT_STATUS 상태를 정리하자면 아래와 같아요.

USER의 잠금해제

계정의 잠그거나 해제하는 방법은 아래와 같습니다.

위와 같은 방법으로 SCOTT 계정의 잠금 상태를 해제해보겠습니다.

USER의 패스워드 재생성

잠금은 풀렸으나 아직 패스워드가 파기된 상태입니다.

파기된 패스워드는 다시 재생성 해주어야 합니다.

패스워드 재생성은 이전에 올린 게시글 (클릭)을 참조하시면 될 것같아요

패스워드를 재생성 해주니 드디어 계정이 'OPEN' 상태가 되었네요.

OPEN된 계정으로 접속한 결과입니다. 

예상대로 잘 접속됩니다! :-) 

패스워드를 생성하는 것과 반대로 패스워드를 파기할 수도 있습니다.

구문은 아래와 같습니다.

1.USER 패스워드 분실  

오라클 사용자 비밀번호를 잊어버려서 접속이 안 되는 경우 어떻게 해야 할까요? 

(windows10 환경)

2.USER 패스워드 변경

이런 경우 SYS 계정에 접속해서 USER의 패스워드를 변경해주시면 됩니다.

사용자 패스워드 변경방법은 아래와 같아요!

SYS 계정의 패스워드도 잊어버렸다면 어떡할까?

명령 프롬프트에서 패스워드 입력 없이 접속이 가능합니다. (실습환경에서)

첫번째는 sqlplus 접속과 동시에 sys계정으로 들어가는 방법입니다. 

두번째 방법으로는, SQL Plus에 로그인 없이 접속하여 SYS로 연결하는 방법도 있습니다.

접속했다면 아래와 같이 SYS계정의 패스워드도 변경하시고 

원래 접속하려고 했던 USER의 패스워드까지 변경해주신다면 끝!

*참고로 데이터베이스에 USER의 목록을 확인하고 싶다면

위의 SELECT 문으로 확인하실 수 있습니다.

1.ERROR : ORA-28009  

: connection as SYS should be as SYSDBA or SYSOPER

SQL Plus에서 sys계정으로 로그인하기 위해 사용자명에 sys를 입력하면 다음과 같은 에러 메시지가 나옵니다.

ERROR:

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

2. 해결

이를 해결하기는 아주 간단합니다.

사용자명을 sys as sysdba로 입력한 후 기존의 설정했던 oracle 패스워드를 입력하면 정상적으로 로그인 됩니다.

0. Oracle database 11g R2 완전제거 

오라클데이터 베이스를 지우려고 시도해본 사람들은 알겠지만 쉽지가 않아요.

어렵게, 어렵게 설치를 끝내고 나서 무언가 빼먹을 때도 종종 있고요! 간단한 설정을 하나라도 빼먹는 날엔 차라리 지우고 완벽하게 다시 설치하고 싶다는 생각이 듭니다..

그렇지만 설치만큼이나 어렵게 느껴지는 것이 삭제! ㅠㅠ

프로그램 추가/제거 안에 있으면 손쉽게 지울 수도 없기 때문에 완전히 지워졌다고 믿기도 어렵고요. 

오라클은 항상 호락호락하지 않네요. 

지우고서도 완전히 지워졌긴 한 걸까？ 의문이 들 때가 얼마나 많은지 몰라요,, 

그래서 혹시 저와 같은 분이 있을까 해서, 이번엔 오라클을 지우는 방법을 공유해보기로 했습니다! 

(windows10 환경)

1. De-install 파일 다운로드

오라클에 데이터베이스 설치 웹페이지 (http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html) 에 들어가보면 deinstall 파일을 다운받을 수 있습니다.

저는 C드라이브 바로 밑에 압축을 해제해두었답니다. (C:\deinstall)

사실 오라클 설치할 때 ORACLE_HOME에 같이 깔리기도 하니(~\app\user\product\11.2.0\deinstall) 확인해보시면 찾을 수도 있을 거에요. 

(저같은 경우 클라이언트도 설치하고 지우는 등 이것저것 반복하다보니...사라졌습니다. ㅜㅜ)

2. 명령 프롬프트 실행

설치가 완료됐으면 명령 프롬프트상에서 삭제를 시작할텐데, 주의할 점은 그냥 실행하지마시고 관리자 권한으로 명령프롬프트를 실행해주시면 됩니다.

*참고로 관리자 권한으로 실행하면 명령프로프트의 기본 디렉토리가 system32로 되어있는 것을 확인할 수 있을 거에요

3. De-install 수행

De-install 파일이 있는 경로를 입력 한 후 ‘-home’, 오라클홈 경로 입력 후 Enter!

저의 경우는 다음과 같은 경로를 입력하였답니다.

 -> C:\deinstall\deinstall -home C:\app\jaehyun\product

이 Oracle 홈에 구성된 데이터베이스 이름 목록을 지정하십시오. []:에서 Enter!

(만약에 [] 안에 데이터베이스 이름의 목록이 존재한다면 그 중 삭제하고자하는 데이터베이스의 이름을 대소문자 구분해서 정확히 입력하여주면 됩니다.)

계속하겠습니까(y – 예, n – 아니오)? [n]:에서 y를 입력해서 계속 삭제를 진행해주시면 삭제가 완료됩니다.

4. 레지스트리 삭제

레지스트리에서 oracle 에 대한 레지스트리를 삭제합니다.

[실행] - ‘regedit’ 입력 – HKEY_LOCAL_MACHINE – SOFTWARE – ORACLE을 삭제 합니다.

5. Program Files에서 삭제

C:\Program Files\oracle 폴더 또한 삭제합니다.

프로그램 폴더까지 삭제하면 모든 제거를 완료하신 것이랍니다.

잘 해결되셨다면 로그인이 필요없는 공감 버튼 부탁드려요!ㅎㅎ  

데이터베이스 성능 튜닝에서

• 라이브러리 캐시 최적화

• 데이터베이스 Call 최소화

• I/O 효율화 및 버퍼캐시 최적화

3가지를 3대 핵심요소라고 합니다.

이 중에서 I/O 효율화 원리부분입니다.

1. 블록단위 I/O

"모든 DBMS는 블록단위로 I/O 한다."

즉, 하나의 컬럼를 읽으려고 할 때도 레코드가 속한 블록 전체를 읽는다는 것이죠.

그렇기 때문에 아래의 SQL문의 일량이 같고 수행하는데에 성능이 동일하다고 볼 수 있는 것이지요.

그래서 액세스 블록수 액세스블록수가 성능에 가장 큰 영향을 미치는 성능지표가 되는 것이랍니다.

이러한 성능지표는 당연히 옵티마이저가 어떠한 방식으로 액세스할지 

(랜덤액세스를 할지, Full Table Scan을 할지) 결정할 때 쓰이게 됩니다.

"블록단위  I/O는 어떠한 시기에 적용되는 걸까"

블록단위 I/O의 적용시기는 다음과 같습니다.

• 메모리 버퍼 캐시에서 블록을 읽고 쓸 때

• 데이터파일에 직접 읽고 쓸 때 (Direct Path I/O)

• 데이터파일에서 DB버퍼캐시로 블록을 적재할 때

• 버퍼캐시에서 변경된 블록을 다시 데이터파일에 저장할 때

DBWR프로세스가 주기적으로 Dirty Buffer를 데이터파일에 한번에 여러블록씩 기록하는데, 

이것도 "버퍼캐시에서 변경된 블록을 다시 데이터파일에 저장할 때" 에 해당합니다.

* Dirty Buffer : 사용자가 사용하여 내용이 변경되었지만 아직 디스크에 기록되지 않은 버퍼를 나타냅니다. 

- 출처 :http://www.gurubee.net/lecture/1887 (by. 김정식)

"오라클이 허용하는 블록의 크기는 어떻게될까"

오라클의 허용 크기는 2K, 4K, 8K, 32K 이며 데이터베이스 생성시 표준 블록크기 설정은 db_block_size의 값으로 설정이 가능합니다.

다른 블록의 크기를 동시에 사용하려면 각각 별도의 테이블 스페이스, 버퍼 Pool를 구성해야 해요.

"Sequential액세스 vs Random액세스"

데이터를 읽어드릴 때 방식으로, Sequential 액세스와 Random 액세스 두가지 방식이 있습니다.

먼저 Sequential액세스 는 레코드 간 논리적 또는 물리적인 

순서에 따라서 차례로 읽어드리는 방식입니다. (그림 6-2, ⑤)

인덱스 스캔시, 리프블록에 위치한 레코드의 포인터를 따라서 논리적 순서로 스캔하며 

테이블 스캔시, 물리적 저장 순서에 따라서 순서대로 읽습니다.

(인덱스를 B* Tree 방식이며 루트, 브랜치, 리프블록으로 구성됩니다.)

Sequential 액세스는 성능향상을 위해서 Multiblock I/O와 인덱스 Prefetch 같은 기능을 사용합니다.

반면 Random액세스를 한번 봐볼까요? 

이름에서 알 수 있듯이 레코드 간 논리적, 물리적 순서에 따르지 않고 읽는 방식입니다.  (그림 6-2, ①~④,⑥)

Sequential 액세스는 여러 건을 한번에 읽기위해서 한 블록에 액세스했다면 

Random 액세스는 한 건을 읽기위해 한 블록씩 접근합니다.(touch)

또한 성능향상을 위해 Pinning, 테이블 Prefetch 와 같은 기능을 사용합니다.

그렇다면 이 두가지 액세스를 어떻게 사용해야 효율적일까요?

해답은 선택도에 있습니다.액세스 효율은 선택도에 따라 결정됩니다.

(선택도 : 총 읽는 레코드에서 결과 집합으로 선택되는 비중)

즉, Sequential액세스의 선택도를 높이고 Random액세스의 발생량을 줄여서 블록에 대한 반복 I/O를 줄이는 것이 액세스 효율을 높이는 방법이라고 할 수 있습니다.

소 개

데이터베이스를 공부하시거나 개발을 하시면 아주아주 쉽게 접할 수 있는 SQL문!

누군가는 너무 너무 쉽다고하고 

누군가는 복잡하다고 하는 바로 그 SQL에 대해 알아가려고합니다.

(사실 모든 학문이 그러겠지만 간단하게 접근하면 쉽고 깊게 접근하면 어렵고 그런거겠죠? ㅎㅎㅎ)

'어떻게 하면 SQL문이 효율적일까' 에 대한 고민을 하면서 

결과적으론 튜닝을 공부하게 될 거에요.

공부하는 방법으로는 책을 정리해나가는 걸로 할 생각인데 

사실 자기만족으로 정리하고 공부하는거라... 혹시 부족하거나 필요한 정보가 있으시면

구루비 (http://www.gurubee.net/)의 내용을 참고하시면 좋을 것같아요.

책은 오라클 성능 고도화의 원리와 해법이라는 책으로 정리할 생각이고요!

"아무나 읽을 수는 있지만, 아무나 이해할 수는 없다."

오라클 성능고도화의 원리와 해법으로 검색을 했을 때 가장 인상깊었던 문구였어요.

누구나 쉽게 이해할 수 없는 내용이라는게 한편으론 더 매력적으로 느껴졌고요.