기술노트 블로그

* Youtube Link - https://www.youtube.com/watch?v=98y70kDWxwM&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=26

인덱스의 성능 비교

대용량 데이터를 이용한 성능비교를 해보려고 한다.

INDEX를 생성하지 않은 Emp 테이블

INDEX를 생성한 Emp_idx 테이블을 만들어서

성능 비교를 해보겠다.

전체 총 데이터는 300024건이다.

ORDER BY DBMS_RANDOM.VALUE를 통해서

랜덤으로 ORDER BY를 하였다.

SELECT DBMS_RANDOM.VALUE FROM dual
UNION ALL
SELECT DBMS_RANDOM.VALUE FROM dual
UNION ALL
SELECT DBMS_RANDOM.VALUE FROM dual;

를 해보면 다음과 같이 소수점으로 데이터가 임의의 값으로 출력되는 것을 볼 수 있다.

그리고 이렇게 출력을 해보면 조회가 잘 되는 것을 볼 수 있다.

USER_INDEXES 테이블에서 조회를 해보면

EMP 테이블과 EMP_INDEX 테이블에 INDEX 생성이 안 된 것을 볼 수 있다.

이번에는 EMP 테이블에 empNo에 INDEX를 생성하였다.

그리고 USER_INDEXES 테이블에서 확인을 해보면

EMP_INDEX 테이블에 INDEX가 생성된 것을 볼 수 있다.

리프 블록은 총 669개가 만들어진 것을 볼 수 있다.

DISTINCT_KEYS 를 통해서 각 PK가 다른 것을 볼 수 있다.

NUM_ROWS 를 통해서 약 30만건의 데이터에 INDEX를 생성한 것을 볼 수 있다.

그러면 이렇게 데이터 블록 위헤

인덱스 블록 (루트 블록, 중간 블록, 리프 블록)이 생성되는 것을 볼 수 있다.

인덱스 블록은 실제 데이터 블록의 주소를 참조한다.

- 버퍼 비우기

ALTER SYSTEM FLUSH BUFFER_CACHE;

ALTER SYSTEM FLUSH SHARED_POOL;

이 두 명령어를 통해서 버퍼를 계속 비우고 다음 쿼리들을 실행해보겠다.

- 자동추적

아이콘들 중 자동 추적을 클릭한다.

인덱스가 없는 테이블을 자동추적을 해보았다.

physical reads가 1827건이다.

30만건의 데이터 중 1827건의 데이터를 조회해서 결과를 불러왔다.

이번에는 인덱스가 있는 테이블을 조회를 해서 자동추적을 해보았다.

physical reads 를 보면 325건의 데이터만 조회가 되었다.

굉장히 빠르게 조회가 되는 것을 볼 수 있다.

숫자에 연연하기 보다도 인덱스가 있는 테이블이 physical reads가 훨씬 더 적다. 이 부분에 의미를 두면 될 것 같다.

- 범위 자동추적

인덱스가 없는 테이블에 범위 검색을 해보았다.

자동 추적을 해보니 physical reads가 1039건이다.

인덱스가 있는 테이블에 범위 검색을 해보았다.

자동 추적을 해보니 physical reads가 366건이다.

훨씬 적은 것을 볼 수 있다.

물론 이 physical reads는 매번 건수가 다르다.

인덱스가 없으면 TABLE FULL SCAN을 하면 데이터 블록에서 일일이 전체를 찾아야 한다.

하지만 인덱스를 만들면 인덱스 블록에서 찾아서 데이터 블록과 왔다 갔다 하면서 데이터를 찾는다.

- 힌트

SELECT /*+ FULL(Emp_idx) */ 
    * FROM Emp_idx WHERE emp_no < 11000; -- 약 999건을 조회함

이렇게 힌트를 주면

TABLE FULL SCAN을 할 수 있다.

옵티마이저가 인덱스 블록에서 데이터 블록까지 왔다 갔다 해야할 수가 많으면

자동으로 TABLE FULL SCAN을 한다.

physical reads가 366건이 나왔다.

힌트를 통해서 인덱스를 사용하도록 해보았다.

때로는 힌트를 사용하는 것 보다 옵티마이저가 실행하는 것이 더 옳을 수 있다.

SELECT /*+ INDEX(Emp_idx IDX_EMPIDX_EMONO) */ 
    * FROM Emp_idx WHERE emp_no < 11000;

옵티마이저가 자동으로 하기 때문에

가끔 INDEX를 사용해야 하는데 안 사용하고, INDEX를 사용하면 안 되는데 사용하는 경우가 많다.

그러한 경우 힌트를 사용하면 좋다.

- 주의점(1): 인덱스 컬럼에 연산을 가하지 마라, 인덱스를 사용하지 못하고 TABLE FULL SCAN을 한다.

INDEX 열에 어떠한 연산을 가하면

인덱스를 사용하지 않는다.

다음과 같이 TABLE FULL SCAN을 한 것을 볼 수 있다.

SELECT * FROM Emp_idx WHERE emp_no = 20000/1;

이러한 경우 이렇게 컬럼이 아닌 비교할 데이터에 연산을 가하면 피할 수 있다.

다음과 같이 인덱스를 정상적으로 실행한 것을 볼 수 있다.

- 주의점(2): 중복도가 높다, 선택도가 낮다면 인덱스를 사용하는 것이 보통 훨씬 더 나쁜 결과를 낳는다.

개수는 30만건인데, 키의 개수는 2건밖에 없다.

이러한 경우는 의미가 없는 인덱스이다.

인덱스를 사용을 하면 지금은 빠르지만

일반적으로 훨씬 나쁜 결과를 낳는다.

또는 있거나 없거나다. 정렬할 필요가 없기 때문이다.

인덱스 블럭에서 데이터 블럭까지 왔다 갔다하는 횟수가 많기 때문이다.

작동개념을 알고 있어야 인덱스를 생성해야 하는 경우와 그렇지 않은 경우를 알 수 있다.

인덱스의 효과

인덱스는 열 단위에 생성된다.

WHERE절에서 사용되는 열에 인덱스를 만들어야 한다.

WHERE절이어도 자주 사용되어야 한다.

데이터의 중복도가 높은, 선택도가 낮은 열은 인덱스를 만들어도 별 효과가 없다.

JOIN을 하는 열에 인덱스를 생성시켜주는 것이 좋다.

INSERT/UPDATE/DELETE가 많은 테이블은 인덱스를 최소화해주는 것이 좋다.

사용하지 않는 인덱스는 제거해주는 것이 좋다.

인덱스는 주기적으로 재구성해서 조각화를 최소화하는 것이 시스템의 성능을 최상으로 유지하는 방법이다.

인덱스는 자동정렬한다.

- 인덱스 재구성

다음과 같이

ALTER INDEX idx_Emp_gender REBUILD; 명령어를 통해서 

ALTER문으로 INDEX를 REBUILD(재구성)할 수 있다.

주기적으로 하면 시스템 성능에 도움이 된다.

인덱스는 계속 조각화되기 때문이다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=mzvtZU4Y0ko&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=25

9.4 인덱스 생성/변경/삭제

- 자동 생성 인덱스: PRIMARY KEY, UNIQUE

- 직접 생성 인덱스: CREATE문을 사용해서 만들 수 있음

9.4.3 인덱스 제거

실습

PK에 다시 이름을 지어서 만들기 위해

01. ALTER TABLE userTBL

DROP PRIMARY KEY CASCADE; 를 실행하였다.

CASCADE를 통해서 PK와 FK를 지울 수 있다.

그리고 

02. ALTER TABLE userTBL

ADD CONSTRAINT FK_userTbl_buyTbl

FOREIGN KEY (userID)

REFERENCES userTBL(userID);

FK를 지정할 수 있다.

- USER_INDEXES 및 USER_IND_COLUMNS의 INNER JOIN

INDEX의 이름과 INDEX_TYPE 그리고 UNIQUENESS를 확인하면 된다.

- USER_INDEXES

- LEAF_BLOCKS: 1개

- DISTINCT_KEYS: 10개 (PK인 userID들의 종류의 개수)

- NUM_ROWS: 10개 (행 개수)

로 조회가 되었다.

1 블럭은 8k이다. 

총 10건의 데이터이기 때문에 1블럭이면 충분하다. (8k)

- 계획 설명

3번째 버튼인 '계획 설명...(F10)' 버튼을 클릭하면

SELECT문의 계획 설명을 볼 수 있다.

인덱스를 사용한 것을 볼 수 있다. UNIQUE SCAN을 하였다.

인덱스가 생성되지 않은 열을 WHERE절을 통해서 검색을 해보니

FULL TABLE SCAN을 한 것을 볼  수 있다.

INDEX가 없으므로 테이블 전체를 검색했다는 의미이다.

엄청나게 오래 걸렸다. 데이터가 많다면.

- 직접 인덱스 생성

중복을 허용하는 인덱스를 생성하였다.

만일 중복된 데이터가 있는 열은 UNIQUE INDEX를 만들 수 없다.

사용자 이름은 중복된 데이터가 없으므로 UNIQUE INDEX가 잘 만들어진 것을 볼 수 있다.

전체적으로 확인해 보면

이렇게 NONUNIQUE INDEX 1건, UNIQUE INDEX 2건이 만들어진 것을 볼 수 있다.

- UNIQUE INDEX 데이터 입력 오류

USERNAME 컬럼은 UNIQUE INDEX가 걸려있으므로

중복된 데이터를 입력하고자 하면 제약조건에 걸려 오류가 발생한다.

- 두 컬럼을 합쳐서 하나의 INDEX로 생성

잘 생성된다.

그리고 확인을 해보면

BIRTHYEAR, USERNAME 두 컬럼을 합쳐서 IDX_USERTBL_USERNAME_BIRTHYEAR이라는 하나의 INDEX로 만든 것을 볼 수 있다.

그리고 하나의 인덱스로 합쳐진 컬럼들을 조합을 해서 WHERE 절에서 사용을 하면 

실제로 INDEX가 실행이 된다.

굉장히 결과가 빨리 나왔을 것이다.

USERNAME 컬럼에 INDEX가 없더라도

조합되었던 INDEX, USERNAME + BIRTHYEAR 인덱스를 사용을 하면

차선책으로 조합되어 있는 IDNEX를 찾아서 더 빨리 찾게 한다.

무조건 이 것을 사용하는 것은 아니며 오라클이 선택해서 사용을 한다.

- 선택도가 낮은 인덱스

국번을 인덱스로 만들었다.

국번의 경우 종류가 굉장히 적다.

그러면 인덱스를 사용해서 검색을 하면 굉장히 많은 시간이 소요가 되게 되는 것이다. 종류가 적기 때문이다.

데이터의 중복도가 높다. 선택도가 낮다. 이러한 경우 인덱스를 안 만드는 것이 낫다.

- 인덱스의 삭제

INDEX의 삭제는 DROP INDEX를 통해서 삭제가 가능하다.

그러나 PK의 경우 DROP INDEX로 삭제를 할 수 없다.

USER_CONSTRAINTS 테이블에서 조회를 해보면

다음과 같이 BUYTBL에 FK로 지정되어 있기 때문이라는 것을 알 수 있다.

다음과 같이 ALTER TABLE userTBL DROP PRIMARY KEY CASCADE; 명령어를 통해서

CASCADE로 FK로 지정된 것들을 전부 다 같이 삭제해준다.

그러면 PK로 자동 생성되는 INDEX도 삭제할 수 있다.

무조건 팍팍 지우는 것이 바람직하다고 볼 수는 없다. 오류를 통해서 내용을 확인하고 지우는 것이 바람직하다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=fbqJDc5PJJc&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=24

인덱스의 내부 작동

- B-Tree (균형 트리)

루트 블럭과 리프 블럭으로 구성되어 있다.

오라클에서는 한 블럭 당 8k정도 된다.

루트 블럭은 각각의 리프 블럭의 첫 데이터만 가지고 있다고 볼 수 있다.

리프 블럭에 데이터들은 동일하게 들어있다.

찾을 때는 루트 블럭을 통해서 일일이 찾을 데이터를 더 빨리 찾을 수 있다.

예를 들어 3만개의 리프 블럭이 있어도 2-3개의 블럭만에 찾을 수 있도록 하는 것이 바로 B-TREE 구조이다.

- 인덱스 분할

인덱싱해야 하기 때문에

루프 블럭과 리프 블럭에서 '인덱스 분할'이 일어난다.

데이터를 입력하려고 하는데 블럭의 저장공간이 예를 들어 모자라면

블럭을 옮기는 작업을 하는데 이것이 인덱스 분할이다.

페이지를 나누는 일은 페이지 입장에서 굉장히 큰 일을 한 것이다.

인덱스 분할이 많이 일어날수록 성능이 많이 떨어진다.

- 중간 노드의 생성

이렇게 QQQ를 삽입하기 위해서 3개의 PAGE가 만들어졌다.

그리고 루트 블럭이 하나가 추가가 되면

이를 중간 블럭으로 바꾼다. 그리고 새로운 루트 블럭을 생성한다. 데이터 조회하는 행의 수를 줄이기 위함이다.

이렇게 블럭들이 추가가 되면 데이터베이스 입장에서는 굉장히 많은 일들을 한 것이다.

실습

이렇게 데이터들을 입력을 하면 오른쪽에 그림과 같이 데이터 블럭으로 나누어 진다.

이는 인덱스가 없는 테이블의 내부 구성이다.

- rowid를 통한 메모리 주소 확인

rowid를 통해서 메모리의 주소를 파악할 수 있다.

이렇게 데이터들이 메모리에 적재된 것을 볼 수 있다.

- 인덱스 생

PK를 생성 후 다시 조회를 했다.

데이터 블럭은 변화가 일어나지는 않는다.

대신 인덱스 블럭이 새로 만들어진다.

리프 블럭 두개에 각각 값만 올라간다. 그리고 PK 값들은 리프 블럭에서 정렬이 된다. 

그리고 각각의 블럭의 루트 블럭이 생성된다. 루트 블럭 또한 정렬되어 있다.

각각의 데이터 블럭은 rowid를 통해서 가리키게 된다.

그리고 2건의 데이터를 입력하였다.

그리고 데이터를 조회해보면

순서대로 입력된 것을 볼 수 있다.

이렇게 데이터 블럭에는 순서대로 데이터가 저장되지만

인덱스 블럭이 생성되어 루트 블럭과 리프 블럭을 통해서 인덱스가 만들어지고 데이터의 메모리 주소가 저장되는 것을 볼 수 있다.

인덱스 블럭에는 다시 PK값과 rowid(메모리 주소)를 저장하고, 인덱스 블럭에서 데이터들이 PK 기준으로 알파벳 순으로 정렬되어 있다. 그래서 빠르게 찾을 수 있다.

하지만  데이터를 범위로 찾는 것이 아니라 개별적으로 대용량의 데이터를 검색한다면 이 또한 성능이 나빠질 수 있다.

여러번 인덱스 블럭과 데이터 블럭을 오가기 때문이다.

- 인덱스가 많아지면 데이터를 입력할 때 그 만큼 만들어야할 인덱스도 많아지게 된다.

- WHERE 절에 해당 인덱스 열이 나와야 인덱스가 사용된다. 그러나 사용되지 않는 경우도 많다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=qqgCwZ81tTs&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=23

대용량 데이터의 경우 인덱스가 없이는 사용이 불가능한 경우가 많다.

9.1 인덱스의 개념

'폭포수 모델'을 찾으려면 책 전체를 찾아봐야 한다.

하지만 책의 찾아보기를 사용을 하면 쉽게 page를 찾을 수 있다.

이것이 바로 인덱스다.

500page를 뒤져서 찾던 것을 간단히 11page안에 찾는다. 엄청나게 효율이 좋다.

인덱스의 장점 및 단점

단점:

- 인덱스를 추가하면 분량이 늘어난다. (10%정도 차지한다.)

- 인덱스가 있다고 무조건 쓰는 것은 아니다. 찾는 데이터가 많다면, 인덱스를 왔다 갔다 하면 호율이 훨씬 악화된다.

- 데이터의 변경 작업에서는 성능이 오히려 나빠진다. 인덱스에도 추가를 해야하기 때문이다.

인덱스에 대한 이해가 굉장히 많이 필요하다.

장점:

- 검색의 속도가 굉장히 빨라질 수 있다.

- 전체 시스템의 성능이 향상이 된다. (사용자 수가 늘어나면 성능의 향상이 곱하기로 늘어난다.)

인덱스를 많이 만들면 오히려 악화된다.

데이터의 분포를 이해하고 적절하게 인덱스를 만들어야 한다.

인덱스의 종류

B-TREE 인덱스와 BITMAP 인덱스, 함수기반 인덱스, 어플리케이션 도메인 인덱스로 나뉜다.

특수한 테이블인 Index-Organized 테이블로 나뉜다.

가장 일반적인 인덱스는 B-TREE 인덱스이다.

- 자동으로 생성되는 인덱스

하나씩 하나당 인덱스를 한다.

여러 컬럼을 조합해서 인덱스를 할 수도 있다.

테이블 생성 시에 제약조건 Primary Key 또는 Unique를 사용하면 자동으로 인덱스가 자동 생성된다.

- USER_INDEXES 및 USER_IND_COLUMNS

SELECT I.INDEX_NAME, I.INDEX_TYPE, I.UNIQUENESS, C.COLUMN_NAME, C.DESCEND

FROM USER_INDEXED I

INNER JOIN USER_IND_COLUMNS C

ON I.INDEX_NAME = C.INDEX_NAME

WHERE I.TABLE_NAME = 'TBL1';

- INDEX_TYPE이 NORMAL이면 B-TREE INDEX이다.

- UNIQUENESS가 UNIQUE이면 중복 허용이 안된다는 의미이다.

- COLUMN_NAME을 통해서 열 이름을 확인할 수 있다. A열이 PK이다.

- DESCEND가 ASC 이므로 정렬이 오름차순으로 되어있는 것을 볼 수 있다.

A 컬럼은 PRIMARY KEY, B 컬럼은 UNIQUE, C 컬럼은 UNIQUE로 정의하였다.

3개의 자동 생성 인덱스가 자동 생성된 것을 볼 수 있다.

자동으로 생성된 인덱스들은 테이블의 제약조건이 삭제되면 자동으로 삭제된다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=qA8tflkxop0&list=PLVsNizTWUw7FzFgU1qe-n7_M7eMFA9d-f&index=22

뷰의 개념

SELECT로 조회된 결과는 테이블이다.

이 결과를 테이블로 만든 것이 뷰이다.

뷰를 SELECT하면 결국 SELECT문이 실행되는 것이다.

테이블처럼 접근하는 것이다.

사용자는 뷰를 테이블로 착각하고 접근을 한다.

제약은 있지만 데이터 수정도 가능하다.

뷰의 장점

- 보안에 도움이 된다. (정보를 가리고 보여줄 수 있다.)

- 복잡한 쿼리를 단순화 시켜줄 수 있다. (복잡한 쿼리를 사용을 하면 뷰로 만들어서 처리를 할 수 있다.)

뷰 실습

- 뷰 생성

CREATE OR REPLACE VIEW : 뷰를 생성하거나, 기존의 뷰를 덮어쓴다.

AS "": 뷰를 만들 때 기존의 테이블의 컬럼의 이름을 변경해서 저장할 수 있다.

SELECT에서 띄어쓰기 컬럼을 조회 시 ""(큰따옴표)로 묶어주어야 한다.

- 한글 열 이름 뷰 생성

큰 따옴표를 활용하여 한글 열 이름도 SELECT할 수 있다.

- 뷰 삭제

DROP VIEW: 뷰를 삭제할 수 있다.

- 만든 뷰 보기

SYSTEM 뷰이다.

SELECT * FROM USER_VIEWS: 만든 뷰들을 조회할 수 있다.

- 뷰 업데이트

UPDATE v_userTBL SET addr = '부산' WHERE userID = 'JKW';

뷰의 데이터를 수정할 수 있다.

- 뷰 입력

실제 테이블의 birth 컬럼이 NOT NULL인데 INSERT하는 쿼리에서는 birth 컬럼을 입력하지 않고 있다.

그래서 NULL을 입력할 수 없으므로 오류가 발생한다.

DEFAULT 값을 추가하거나, NULL을 허용하도록 제약조건을 변경하면 가능하다.

- WITH READ ONLY

WITH READ ONLY 옵션을 주면

UPDATE를 뷰에 실행 시 

cannot perform a DML operation on a read-only view 에러가 발생한다.

- 합계 뷰

합계 뷰를 생성할 수 있다.

INSERT는 되지 않는다.

실제 컬럼이 없고, 그 때 그 때 만들어 내는 집계 함수 및 GROUP BY를 사용했기 때문이다.

이를 가상열이라고 부른다.

또는 UNION, DISTINCT는 가상 열을 사용하기 때문에 INSERT가 안 된다.

- 범위 뷰

범위에 해당하지 않는 데이터가 뷰에 INSERT 되는 것을 볼 수 있다.

이러한 경우 WITH CHECK OPTION을 줄 수 있다.

이렇게 WITH CHECK OPTION을 주면

범위에 맞지 않는 데이터는 INSERT되지 않는 것을 볼 수 있다.

그래서 범위 뷰일 시 WITH CHECK OPTION을 주는 것이 바람직하다.

- 복합 뷰

조인된 테이블들의 결과를 저장하는 것이 복합뷰이다.

복합뷰는 INSERT가 되지 않는다.

만일 뷰가 참조하는 테이블을 삭제하면

뷰는 당연히 SELECT되지 않고, 오류를 발생시킨다.

구체화된 뷰

뷰는 가상화된 뷰이다.

구체화된 뷰는 실제 데이터를 가져다 놓는다.

그래서 바로 조회가 된다.

장점은 빨리 가져올 수 있다.

단점은 만일 참조하는 테이블들이 실시간으로 변경이 자주 일어나는 테이블들이라면

구체화된 뷰는 실제 테이블의 데이터를 가져다 놓았는데 그러면 내부적으로 계속 데이터를 변경해서 가져다 놓는다.

그러면 오라클 내부의 성능이 저하될 수 있다.

- 테스트 데이터 생성

CREATE TABLE bigTBL AS SELECT * FROM HR.bigEmployees;

CREATE TABLE smallTBL AS SELECT * FROM HR.Employees;

이렇게 테이블들을 복사했다.

CROSS JOIN을 해서 birth_date의 년도의 평균값을 구하니

시간이 굉장히 오래걸린다.

CREATE MATERIALIZED VIEW: 실체화된 뷰 생성

실제 데이터를 저장하다보니 시간이 오래걸린다.

실제 데이터를 가져와놓은 구체화된 뷰이다보니

조회시간이 거의 0초 수준으로 빠르다.

- BUILD DEFFERED

CREATE MATERIALIZED VIEW mv_AvgYear BUILD DEFFERED AS SELECT문;

BUILD DEFFERED를 사용을 하면 나중에 구체화된 뷰에 데이터를 가져올 수 있다.

그래서 뷰를 SELECT를 해보면 데이터가 아무런 데이터도 조회가 되지 않는다.

EXECUTE DBMS_MVIEW.REFRESH(LIST => 'mv_AvgYear');

를 통해서 구체화된 뷰에 데이터를 가져올 수 있다.

- GENERATED ALWAYS AS 

buyTBL에 sales 가상 열을 추가했다.

GENERATED ALWAYS AS (price * amount); 를 통해서 실시간으로 가상 열이 만들어지도록 판매 실적 가상 열을 생성하였다.

그리고 조회를 해보면 정상적으로 sales 컬럼도 잘 조회된다.

sales 컬럼이 이렇게 생성되었다. 

- BUILD IMMEDIATE, REFRESH COMPLETE, ON COMMIT

BUILD IMMEDIATE: 즉시 빌드됨

REFRESH COMPLETE: 전체 뷰가 새로고침됨

ON COMMIT: 원본 테이블이 COMMIT 되는 즉시 변경됨

실제 테이블인 buyTBL을 UPDATE 후 COMMIT 시

뷰의 결과가 바로 반영되는 것을 볼 수 있다.

SELECT * FROM mv_SumSales;

결과가 바로 반영되었다.

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=n95a3PgW1tQ&list=PL6i7rGeEmTvpLoDkB-kECcuD1zDt_gaPn&index=13

11. 스칼라 서브쿼리 문제

스칼라 서브쿼리에서 ORDER BY는 문법에러

ORDER BY 1은 첫번째 열을 기준으로 오름차순 정렬한다.

서브쿼리에서 여러 개의 행이 반환되면 IN, ANY, ALL과 같은 다중행 서브쿼리 함수를 사용해야 한다.

그러면 이렇게 위 그림처럼 들어갔다는 의미이다.

- 스칼라 서브쿼리: SELECT 절에서 사용하는 서브쿼리

- 인라인뷰 서브쿼리: FROM 절에서 사용하는 서브쿼리

- 중첩 서브쿼리: WHERE 절에서 사용하는 서브쿼리

서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있다.

메인쿼리에서는 서브쿼리의 컬럼을 사용할 수 없다.

12. ROLLUP 문제

정답은 (2)이다.

2.은 잘못되었다.

DNAME, YEAR별 소계도 같이 출력되고

DNAME별 소계 결국 총계도 같이 출력된다.

ROLLUP은 소계를 출력하는 함수입니다.

13. 실수 함수

- FLOOR() 함수: 실수 내림 함수

- CEIL() 함수: 실수 올림 함수

- ROUND() 함수: 실수 반올림 함수

- TRUNC() 함수: 실수 버림 함수

두번째 옵션은 소수점 자리 수를 의미한다.

14. SQL문의 실행 순서

정답은 (2)이다.

15. Oracle과 SQL Server

오라클은 AUTO COMMIT이 FALSE이다. (DDL은 자동 COMMIT, DML은 직접 COMMIT)

SQL SERVER는 AUTO COMMIT이 TRUE이다. (DML, DDL 자동 COMMIT)

정답은 (4)이다.

원래 SQL은 BEGIN TRANSACTION과 COMMIT (TRANSACTION)이 존재한다.

이를 명시적 TRANSACTION이라고 한다.

TRANSACTION은 처리과정을 의미한다.

16. SQL문 실행 계획을 읽는 순서

정답은 (1)이다.

17. PL/SQL에서 FETCH 이후에 수행해야 하는 것

정답은 (3)이다.

커서는 반복작업을 위한 단위이다.

SQL문은 데이터를 가져오기 위한 절차를 기술하지 않는다.

하지만 PL/SQL을 통해서 SQL을 절차적으로 사용할 수 있다. (PL: PROCEDURAL LANGUAGE)

DECLARE는 변수를 선언하는 부분이다.

SELECT employee_id, first_name, last_name, salary FROM HR.employees WHERE salary > 14000;

의 결과(테이블)을 CURSOR emp_cursor IS 의 emp_cursor로 저장한다.

이렇게 커서와 자료형 변수들을 만들어 준다.

BEGIN 부터 로직이 실행된다.

OPEN emp_cursor; 는 CURSOR를 열겠다는 의미이다.

FETCH는 CURSOR의 데이터 결과(테이블)을 하나씩 꺼내서 사용하겠다는 의미이다.

그래서 변수에 하나 하나씩 넣어주는 것이다.

그리고 이 변수들을 활용한 로직 처리가 가능할 것이다.

로직이 종료되면

CLOSE emp_cursor; 로 CURSOR를 닫아주어야 한다.

닫는 이유는 자원 낭비를 방지하기 위함이다. (메모리 부족 현상 방지)

EXCEPTION WHEN OTHERS THAN DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END

명령어는 예외가 발생하면 이쪽으로 보내겠다는 명령어이다.

흐름을 알고 있으면 좋다.

18. SQL SERVER 문제

오라클에서 볼 수 없는 내용들이 나올 것이다.

오라클에는 TOP() 함수가 없다. 아쉬운 부분이다.

MySQL, MS SQL 등은 

 WITH TIES를 사용을 하면

동일한 순위 팀을 같이 뽑을 수 있다.

19. LIKE 함수

ORACLE은 regular expression이 따로 있다. regexp_like 정규식이다.

MS SERVER는 regular expression은 []를 사용한다.

K%는 K가 첫글자이고 이후에 0개 이상의 문자들이 나오는 것이다.

%K%는 K 사이에 0개 이상의 문자들이 나오는 것이다.

[__K]%는 오라클의 경우 [를 문자로 간주한다. 하지만 MS SERVER에서는 []를 regular expression으로 사용을 한다.

그래서 오라클의 경우에는 '[AAK]AA' 등을 출력할 수 있다.

MS SERVER는 _,_,K 를 _ 또는 _또는 K인 첫 글자 중 %이므로 0개 이상의 문자를 출력한다.

__K%는 K가 세번째이고, %이므로 다음이 0개 이상의 문자를 출력한다.

20. 해시 조인 문제

감사합니다.

* Youtube Link - https://www.youtube.com/watch?v=qCAXD2ukvTE&list=PL6i7rGeEmTvpLoDkB-kECcuD1zDt_gaPn&index=12

01. 파티셔닝 기법

파티셔닝이란

어떤 테이블을 수평으로 쪼개는 것이다.

그런데 이 테이블을 물리적으로 쪼개는 것이 아니라 논리적으로 쪼갠다.

실제 테이블은 하나인데, 가상의 논리적으로 

이렇게 하나의 테이블에 마치 3개의 테이블이 있는 것 처럼 사용하겠다.

물리적으로 분할하지 않는다의 의미이다.

- range partition: 데이터 값의 범위를 기준으로 파티션을 수행 (주로 주기, 날짜 범위)

- list partition: 특정 값을 지정해서 파티션을 수행 (서울, 주기 x 항목)

- hash partition: 직접 조건으 주는 것이 아니다. 임의의 값을 주면 큰 공간안에 알아서 데이터를 저장하는 것이다.

- composite partition: 범위와 해시를 복합적으로 사용해서 파티션을 수행한다.

데이터의 삭제도 파티셔닝을 기준으로 삭제할 수 있다.

list partition의 경우 항목을 지정해서 파티션을 수행하기 때문에 쉽게 데이터를 삭제할 수 없다.

02. 속성의 특성에 따른 분류

정답은 (2)이다.

구성방식에 따른 속성의 분류도 있다.

03. 비식별 관계

직원과 직원연락처 관계는 

직원은 여러개의 직원 연락처를 갖을 수 있다.

그래서 중복을 피하여 PK를 만들기 위해

직원ID + 구분코드 (예를 들면, 집전화, 휴대폰, 회사 연락처) 등을 연결해서 유일한 식별자를 만든다.

강한 식별 관계와 약한 식별관계는 이렇다.

직원의 PK는 직원 연락처에서 반드시 갖고 있어야 하며 이 PK를 기본키로 사용을 한다.

이러한 경우 강한식별관계라고 한다.

하지만 부서의 PK는 직원에서 하나의 속성과 같다. 

이러한 경우 약한 연결 관계, 즉 비식별자관계라고 한다.

BARKER 표기법에서는 비식별자관계를 점선으로 표시하지 않는다.

BAKER 표기법에서는 1이 식별자 관계를 말한다. 1이 없으면 비식별자 관계인 것이다.

IE 표기법에서는 비식별자관계를 점선으로 표시한다.

04. ER 모델링의 표기법

05. 이상현상

정답은 (3)이다.

이상현상에는 삽입 이상현상, 갱신 이상현상, 삭제 이상현상이 있다.

예를 들어 반정규화 상태의 테이블에 P004라는 냉장고 상품만 넣고 싶은 것이다.

그런데 고객ID, 고객명은 우리가 넣고 싶지 않은 데이터인데 넣어야 하는 것이다.

하지만 테이블이 뜯어져있었다면 상품 데이터만 넣을 수 있겠죠?

이러한 경우가 삽입 이상이다.

또는 만약에 드라이기의 가격을 바꾸고 싶다면 전부 다 바꿔주어야 한다.

이를 갱신 이상이라고 한다.

또는 노트북 정보를 삭제하고 싶다. 그런데 얘기치 못하게 고객의 정보까지 삭제가 되버리는 현상을 삭제 이상이라고 한다.

06. 3차 정규화

정답은 (4)이다.

1차 정규형 도메인 원자성: 속성에 하나의 값만 있어야 한다.

2차정규형 부분 종속성 제거: 컬럼에 PK 복합속성에서 부분적으로 종속되는 컬럼은 테이블을 나누어 주는 것이다.

3차 정규형 이행 종속성 제거: 

4차 정규화 다치종속성 제거:

5차 정규화 조인 속성 제거: 

07. 키 엔터티

정답은 (1)이다.

기본 엔터티 - 독립적 엔터티

중심 엔터티 - 계좌, 주문, 취소, 체결등의 엔터티이다. 기본 엔터티와 행위 엔터티의 중간이다.

행위 엔터티 - 주문이력, 체결 이력 등이 있다.

08. 파티션 처리

정답은 (1)이다.

09. 데이터 모델링 3단계(개념-논리-물리)

재사용성이 가장 높은 것은 바로 논리적 데이터 모델링이다.

IE/BARKER -> ERD -> CREATE문 

등으로 볼 수 있다.

설계는 논리적 데이터 모델링에서 대부분 설계가 되어야 한다. 가장 중요하고 시간도 오래 걸린다.

데이터를 쪼개면서 이상이 발생하지 않도록 정규화를 수행한다.

10. 

엔터티의 개념 자체가 집합이다.

그리고 요 하나 하나의 데이터를 인스턴스라고 했다.

집합을 의미하기 위해서는 무조건 두개 이상의 인스턴스가 있어야 한다.

인스턴스는 하나 이상의 속성을 가질 수 있다.

속성에는 하나의 속성값만 있어야 한다.

감사합니다.