쿼리를 짜다보면 여러 행을

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

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

그렇기 때문에 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'

도움이 된 정보였다면

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

DML은 자주 쓰다보니 잊어버리지 않는데 

아무래도 DDL은 빈도가 낮다보니 자주 잊어버린다 😫

"컬럼 변경하기"

아래 예시 코드는

WORSHIP 테이블의 version 컬럼을 변경하는 쿼리이다.

컬럼명을 order_version 으로 변경하고

데이터 타입은 INT 로,

NOT NULL 제약을 적용하고 

기본값을 0으로 지정하는 쿼리이다. 

ALTER TABLE `WORSHIP` 
CHANGE COLUMN `version` `order_version` INT 
NOT NULL 
DEFAULT 0 
COMMENT 'version' ;

마지막에 COMMENT는

컬럼에 대한 코멘트라고 이해하면 되는데

ERD 자동화 툴을 사용하면

컬럼의 논리명이 COMMENT에 들어간다.

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

매번 까먹어서 찾는다 ㅠㅠ

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

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

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

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

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

오라클에 사용했고

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

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

1.USER 패스워드 분실  

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

(windows10 환경)

2.USER 패스워드 변경

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

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

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

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

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

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

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

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

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

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

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

• 라이브러리 캐시 최적화

• 데이터베이스 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/)의 내용을 참고하시면 좋을 것같아요.

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

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

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

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