데이터 타입
✅ Oracle 에서 사용하는 데이터 타입은 문자, 숫자, 날짜, 그 외 로 단순합니다.
| 데이터 타입 | 설명 |
|---|---|
| VARCHAR2(size) | 최대 길이가 size 인 가변길이 문자값으로 최소길이는 1, 최대길이는 2000 |
| CHAR(size) | 길이가 size 인 고정길이 문자값으로 기본길이는 1, 최대길이는 255 |
| NUMBER | 38자리까지 유효한 부동 소수점 숫자 |
| NUMBER(p,s) | 38의 범위 중에서 p 의 자릿수까지 유효한 숫자값으로 전체 자릿수 p, 소수점 자릿수 s |
| DATE | B.C 4712년 1월에서 A.D 4712년 12월 31일 사이의 일자와 시간 |
| LONG | 2GB 까지의 가변길이 문자값으로 테이블당 한개의 LONG 열만 허용 |
| RAW, LONGRAW | 각각 VARCHAR2, LONG 과 같지만 이진 테이터를 저장하는데 사용 |
작명 규칙
Database Object Names and Qualifiers : ORACLE 에서 규정하고 있는 DB 에서의 표준 작명 규칙입니다.
고정길이 & 가변길이
고정길이 타입을 사용해야 할지 가변길이 타입을 사용해야 할지는 상황에 따라 다릅니다.
✅ 고정길이와 가변길이는 속도와 저장공간 사이의 trade-off 관계입니다.
-
고정길이
- 항상
고정된 저장공간을 할당합니다. - 지정한 길이보다 작은 길이의 데이터는
공백을 채워 넣어서 저장합니다. - 데이터가 모두 동일한 크기이므로
접근 속도가 빠릅니다. 저장공간의 낭비가 발생할 수 있습니다. 특히 데이터의 길이 편차가 크다면 그렇습니다.
- 항상
-
가변길이
실제 데이터 크기만큼 저장공간을 사용합니다.저장공간을 효율적으로활용할 수 있습니다. 특히 데이터의 길이 편차가 크다면 그렇습니다.- 데이터마다 길이가 달라서
접근속도가 느릴 수 있습니다. 데이터의 길이 정보 또한 따로 저장해야 합니다.
홑따옴표 & 쌍따옴표
✅ 문자열 리터럴을 표현할 때에는 반드시 홑따옴표 '' 로 감쌉니다.
✅ 테이블명, 컬럼명을 사용할 때에는 따옴표를 사용하지 않는다면 모두 대문자로 취급합니다.
✅ 예약어를 피하거나 소문자 테이블명, 컬럼명을 사용하고 싶다면 쌍따옴표 "" 로 감쌉니다.
FROM 절 JOIN | WHERE 절 JOIN
JOIN 구문을 사용하는 방법은 WHERE 절, FROM 절 JOIN 2개가 있습니다.
두 방법 중에 어떤 방법을 사용하는 것이 더 좋을까요?
✅ WHERE 절은 관계대수에서 selection( σ ) 연산이 목적입니다.
join 연산이 목적인 JOIN 구문은 WHERE 절과 구분하는 것이 가독성 측면에서 더 유리합니다.
✅ 나아가 FROM 절 JOIN 을 사용하면 다양한 JOIN 방식을 좀더 명확하게 명시할 수 있습니다. (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN)
✅ FROM 절 JOIN 을 사용하는 것이 깔끔하고 효율적인 쿼리를 작성하는 방법입니다.
-- WHERE 절 JOIN 방식의 지저분한 쿼리
SELECT *
FROM TABLE_A, TABLE_B, TABLE_C
WHERE TABLE_A.COL_A (+) = TABLE_B.COL_B
AND TABLE_C.COL_C (+) = TABLE_A.COL_C
AND TABLE_A.COL_A = 'DATA'
AND TABLE_B.COL_B = 1234
;-- FROM 절 JOIN 방식의 깔끔한 쿼리
SELECT *
FROM TABLE_A
RIGHT JOIN TABLE_B ON TABLE_A.COL_A = TABLE_B.COL_B
LEFT JOIN TABLE_C USING(COL_C)
WHERE TABLE_A.COL_A = 'DATA'
AND TABLE_B.COL_B = 1234
;SUBQUERY IN PIVOT
PIVOT 함수는 다음과 같이 사용합니다.
SELECT *
FROM (SELECT REGION, CATEGORY, QUANTITY, UNIT_PRICE FROM SALES_DATA)
PIVOT (
SUM(QUANTITY * UNIT_PRICE)
FOR CATEGORY IN ('의류', '가전', '도서')
)
ORDER BY REGION;만약 FOR _ IN (...) 에 들어가야 할 값이 정말 많다면 쿼리를 작성하는 것이 어려울 것입니다.
그래서 다음과 같이 작성하고 싶을 수 있습니다.
SELECT *
FROM (SELECT REGION, CATEGORY, QUANTITY, UNIT_PRICE FROM SALES_DATA)
PIVOT (
SUM(QUANTITY * UNIT_PRICE)
FOR CATEGORY IN (SELECT DISTINCT CATEGORY FROM SALES_DATA)
)
ORDER BY REGION;⚠️ 그러나 PIVOT 함수의 FOR _ IN (...) 에는 SUBQUERY 를 사용할 수 없습니다.
그 이유는 PIVOT 함수는 내부적으로 다음과 같이 변경되기 때문입니다.
SELECT REGION,
SUM(CASE WHEN CATEGORY = '의류' THEN QUANTITY * UNIT_PRICE ELSE 0 END) AS "의류",
SUM(CASE WHEN CATEGORY = '가전' THEN QUANTITY * UNIT_PRICE ELSE 0 END) AS "가전",
SUM(CASE WHEN CATEGORY = '도서' THEN QUANTITY * UNIT_PRICE ELSE 0 END) AS "도서",
FROM SALES_DATA
GROUP BY REGION
ORDER BY REGION;PIVOT 함수는 컬럼명 그 자체를 바꾸는 구조이기 때문에 정적인 CASE 문에 동적인 SUBQUERY 의 결과값을 가져오려 하는 것은 컴파일 시점에 명확하지 않아 오류가 발생하는 것입니다.
이를 동적 SQL로 해결하는 방법도 있습니다.
CREATE OR REPLACE PROCEDURE get_sales_pivot (
result_cursor OUT SYS_REFCURSOR
)
AS
sql_query CLOB;
col_list CLOB;
BEGIN
-- 1. CATEGORY 리스트를 '의류', '가전', '도서' 같은 문자열로 생성
SELECT LISTAGG('''' || CATEGORY || '''', ', ')
WITHIN GROUP (ORDER BY CATEGORY)
INTO col_list
FROM (SELECT DISTINCT CATEGORY FROM SALES_DATA);
-- 2. 동적 SQL 생성
sql_query := '
SELECT *
FROM (
SELECT REGION, CATEGORY, QUANTITY * UNIT_PRICE AS SALES
FROM SALES_DATA
)
PIVOT (
SUM(SALES)
FOR CATEGORY IN (' || col_list || ')
)
ORDER BY REGION';
-- 3. 동적 SQL 실행 및 결과를 커서로 반환
OPEN result_cursor FOR sql_query;
END;
/
VARIABLE rc REFCURSOR;
EXEC get_sales_pivot(:rc);
PRINT rc;그러나 이는 오히려 더 복잡한 과정을 거쳐야 합니다.
(PIVOT 에 사용될 컬럼의 값이 계속해서 추가된다고 예상되면 적절한 해결 방안이 될 수 있습니다.)
PIVOT 함수의 목적을 생각해 보면 그 결과를 눈으로 확인하기 위함이 대부분입니다.
만일 PIVOT 의 대상이 되는 컬럼값의 종류가 매우 많다면 PIVOT 을 사용해 확인하는 것은 적절하지 않을 수 있습니다.
✅ 따라서 PIVOT 함수를 사용해야 하는 적절한 상황에서는 FOR _ IN(...) 에 고정된 컬럼 이름 리스트를 명시하는 것이 적합합니다.
CASE & PIVOT
앞서 말한 것처럼 PIVOT 에서는 서브쿼리를 사용할 수 없습니다.
게다가 집계함수가 적용되는 부분에는 다른 함수를 사용할 수 없습니다.
이러한 제약 때문에 원하는 결과를 만들어 내기가 다소 복잡해 질 수 있습니다.
반면, 표준 함수인 CASE 를 사용하면 PIVOT 과 동일한 쿼리를 작성할 수 있고,
CASE 에서는 자유롭게 다른 함수나 서브쿼리를 사용할 수 있습니다.
따라서 간단한 수준에서는 PIVOT 을 사용할 수 있지만,
좀더 다듬어진 결과가 필요하다면 CASE 를 사용하는 것이 더 쉬울 수 있습니다.
-- CASE 사용
WITH CATEGORY_SALES AS (
SELECT NVL(CATEGORY, '기타') CATEGORY, EXTRACT(MONTH FROM SALE_DATE) "MONTH", QUANTITY * UNIT_PRICE SALES
FROM SALES_DATA
)
,MONTHS AS (
SELECT EXTRACT(MONTH FROM DATE '2024-01-01') + LEVEL - 1 "MONTH"
FROM DUAL
CONNECT BY LEVEL <= 12
)
SELECT CATEGORY,
SUM(CASE WHEN "MONTH" = 1 THEN SALES ELSE 0 END) AS "1월",
SUM(CASE WHEN "MONTH" = 2 THEN SALES ELSE 0 END) AS "2월",
SUM(CASE WHEN "MONTH" = 3 THEN SALES ELSE 0 END) AS "3월",
SUM(CASE WHEN "MONTH" = 4 THEN SALES ELSE 0 END) AS "4월",
SUM(CASE WHEN "MONTH" = 5 THEN SALES ELSE 0 END) AS "5월",
SUM(CASE WHEN "MONTH" = 6 THEN SALES ELSE 0 END) AS "6월",
SUM(CASE WHEN "MONTH" = 7 THEN SALES ELSE 0 END) AS "7월",
SUM(CASE WHEN "MONTH" = 8 THEN SALES ELSE 0 END) AS "8월",
SUM(CASE WHEN "MONTH" = 9 THEN SALES ELSE 0 END) AS "9월",
SUM(CASE WHEN "MONTH" = 10 THEN SALES ELSE 0 END) AS "10월",
SUM(CASE WHEN "MONTH" = 11 THEN SALES ELSE 0 END) AS "11월",
SUM(CASE WHEN "MONTH" = 12 THEN SALES ELSE 0 END) AS "12월"
FROM CATEGORY_SALES CS
JOIN MONTHS M USING(MONTH)
GROUP BY CATEGORY;
-- PIVOT 사용
WITH ALL_MONTHS AS (
SELECT LEVEL AS "MONTH" FROM DUAL CONNECT BY LEVEL <= 12
),
ALL_CATEGORIES AS (
SELECT DISTINCT NVL(CATEGORY, '기타') AS CATEGORY FROM SALES_DATA
),
MONTHLY_CATEGORIES AS (
SELECT AM."MONTH", AC.CATEGORY
FROM ALL_MONTHS AM
CROSS JOIN ALL_CATEGORIES AC
),
CATEGORY_SALES AS (
SELECT NVL(CATEGORY, '기타') CATEGORY, EXTRACT(MONTH FROM SALE_DATE) "MONTH", QUANTITY * UNIT_PRICE SALES
FROM SALES_DATA
)
SELECT *
FROM (
SELECT MC.CATEGORY, MC."MONTH", NVL(CS.SALES, 0) AS TOTAL_SALES
FROM MONTHLY_CATEGORIES MC
LEFT JOIN CATEGORY_SALES CS ON MC.CATEGORY = CS.CATEGORY AND MC."MONTH" = CS."MONTH"
)
PIVOT (
SUM(TOTAL_SALES)
FOR "MONTH" IN (1 AS "1월", 2 AS "2월", 3 AS "3월", 4 AS "4월", 5 AS "5월", 6 AS "6월",
7 AS "7월", 8 AS "8월", 9 AS "9월", 10 AS "10월", 11 AS "11월", 12 AS "12월")
)
ORDER BY CATEGORY;ROLLUP, CUBE & GROUPING()
ROLLUP, CUBE, GROUPING SETS 와 같은 GROUP 함수의 확장 함수들은 지정된 컬럼들을 그룹화 해서 집계함수를 한번 더 적용합니다.
만일 지정한 컬럼에 NULL 이 있다면 그 또한 집계의 대상이 됩니다.
이는 소계 혹은 총계와 구분이 어려울 수 있습니다.
SELECT REGION, CATEGORY, SUM(QUANTITY * UNIT_PRICE) "총 판매액"
FROM SALES_DATA
GROUP BY CUBE(REGION, CATEGORY)
ORDER BY 1, 2;만약 SELECT 문의 TARGET LIST 컬럼에 직접적으로 NVL 이나 COALESCE 를 적용한다면
집계 대상이 되는 NULL 과 소계나 총계를 의미하는 NULL 모두 적용대상이 되어 논리적인 오류가 발생하고,
CUBE(...) 의 컬럼에 NVL 이나 COALESCE 를 적용하면 구문 오류가 발생합니다.
✅ 이때 사용할 수 있는 함수가 GROUPING() 입니다.
GROUPING 함수는 해당 행이 지정된 컬럼의 소계 또는 총계를 나타내면 1을, 그렇지 않으면 0을 반환합니다.
GROUPING 함수를 이용해 다음과 같이 해결할 수 있습니다.
SELECT
CASE
WHEN GROUPING(REGION) = 1 THEN '총합'
ELSE NVL(REGION, '미정')
END AS "지역",
CASE
WHEN GROUPING(CATEGORY) = 1 THEN
CASE
WHEN GROUPING(REGION) = 1 THEN '총합' -- 전체 총합
ELSE '총합 (지역별)' -- 지역별 총합
END
ELSE NVL(CATEGORY, '기타')
END AS "카테고리",
SUM(QUANTITY * UNIT_PRICE) AS "총 판매액"
FROM SALES_DATA
GROUP BY CUBE(REGION, CATEGORY)
ORDER BY 1, 2;시퀀스
✅ 시퀀스는 마치 번호표 발급기처럼 요청할 때마다 미리 정의된 규칙에 따라 증가하거나 감소하는 고유한 숫자를 제공해주는 객체입니다.
✅ 특정 테이블이나 컬럼에 종속적인 인덱스와 달리 시퀀스는 독립적인 객체입니다.
✅ 단일 행 삽입 또는 업데이트 시 유일한 ID 값을 생성하는 용도로 직접적으로 사용되는 것이 안전합니다. 이 외의 경우 SEQUENCE 를 사용하지 못할 수 있습니다.
⚠️ PK를 생성하기 위한 시퀀스는 CYCLE 속성을 부여할 경우 무결성과 유일성 측면에서 문제가 발생할 수 있습니다.
⚠️ CACHE 속성은 한 CYCLE 보다 작고 1 보다 큰 값이 할당 되어야 합니다.
인덱스
✅ 인덱스는 효율적인 데이터 탐색을 목적으로 사용됩니다.
✅ WHERE 절 조건이나 JOIN 조건으로 자주 사용되는 컬럼에 대해 인덱스를 생성하는 것이 좋습니다.
-
인덱스를
생성한다는 것은키 값(지정한 컬럼의 값)과ROWID(물리적인 저장 장소)를 가지고B-TREE를 생성한다는 뜻입니다.- B-TREE(BALANCED TREE) 는 모든 LEAF NODE 의 깊이가 같은 트리입니다.
-
이렇게 생성된 B-TREE 의 요소들은 다음과 같은 속성을 가지고있습니다.
- ROOT NODE & BRANCH NODES: 키 값에 대한 범위를 가지고 있어 하위 탐색의 방향을 제시합니다.
- LEAF NODES: 키 값과 ROWID 를 가지고 있고, LINKED LIST 형태로 연결되어 있습니다.
-
UNIQUE제약 조건을 가지고 있는 컬럼(PK 포함)은자동으로 인덱스를생성합니다.- 일반적인 테이블은 PK 를 가지고 있고, 대부분의 JOIN 은 NATURAL JOIN 이기 때문에 우리는 대부분의 쿼리에서 인덱스를 활용하고 있는 것입니다.
- WHERE 절 조건에 자주 사용될 것 같은 컬럼에 대해서 인덱스 생성을 고려하면 되겠습니다.
⚠️ 인덱스가 모든 상황에서 효율적인 것은 아닙니다.
- B-TREE 의 특성상 삽입, 수정, 삭제 작업의 오버헤드가 큽니다.
이러한 작업이 빈번하게 일어나는 테이블은 특히나 인덱스를 많이 만드는 것을 주의해야 합니다. - 테이블의 크기가 매우 작거나, 고유값의 수가 매우 적은 컬럼은 인덱스를 사용하는 것이 오히려 비효율적입니다.
테이블 전체를 스캔하는 것이 더 유리하거나 비슷한 비용이 들기 때문입니다. - DBMS 의 옵티마이저는 인덱스가 있더라도 이것을 활용하는 것이 더 효율적인지 스스로 판단하고 선택합니다.
사용자가 강제로 인덱스를 사용하게끔 지정할 수 도 있습니다.
ANSI/ISO SQL
SQL은 1986년에 미국 국립 표준 협회 (ANSI) 의 표준이 되었고 , 1987년에는 국제 표준화 기구 (ISO) 의 표준이 되었습니다. 그 이후로 이 표준은 더 많은 기능을 포함하고 공통 확장을 통합하기 위해 여러 번 개정되었습니다. 표준이 존재함에도 불구하고, 현존하는 구현은 거의 없으며, 대부분의 SQL 코드는 다른 데이터베이스 시스템 으로 이식되기 전에 최소한 어느 정도 변경이 필요합니다 .
-출처-
ORACLE 고유의 기능과 이를 완전히 대체하는 표준 기능 들이 있습니다.
예를 들면 ORACLE 의 NVL() 함수는 표준의 COALESCE() 함수로 대체 가능하고, ORACLE 의 DECODE() 함수는 표준의 CASE 문으로 대체 가능합니다.
❓ 어떤 기능을 사용하는 것이 더 좋을까요?
ORACLE 은 오픈소스가 아니기 때문에 성능면에서 어떤 기능이 더 뛰어나다고 단정지을 수는 없습니다.
그러나 ORACLE 은 고유 기능이 있음에도 표준 기능을 구현했다는 점을 미루어 보아, 표준 기능보다 ORACLE 고유 기능이 동일하거나 더 뛰어난 성능을 가지고 있을거라고 생각해 볼 수 있습니다.
✅ 만약 성능이 중요한 경우 실제로 쿼리를 실행해보고 비교하는 방식이 가장 확실합니다. 그렇지 않은 경우 다른 DBMS 와 호환이 가능한 표준 기능을 사용하는 것이 재사용성 측면에서 유리합니다.
