DataBase에서 table을 create하고 column의 data type을 varchar2 type으로 설정하고 data를 insert한다.
varchar2 type의 장점은 data의 길이가 가변적이라는 것이다.
따라서 varchar2(100)으로 선언한 후 'SAMPLE' 라는 data를 저장하면 'SAMPLE'만큼의 메모리만 할당하여 저장한다.
만약 후에 더 많은 data(100 이하)를 저장할 일이 있을 때 메모리 공간이 가변적으로 더 많이 할당되어 저장할 수 있다.
하지만 이 때 생각해 볼 것이 있다. 이미 'SAMPLE'만큼의 data가 저장되면 메모리상에서 그 이후에 다른 data들이 저장되어 있을 것이다.
하지만 이 column을 'SAMPLE...bla...bla...bla...'로 저장하면 현재 data의 이후의 data들은 모두 뒤로 밀려나야 하나?
이렇게 되면 update할 때 마다 성능에 치명적인 영향을 미칠텐데...?
정답은 '아니다.'이다.
varchar2 type으로 사용할 경우 column의 data가 더 크게 update되면 현재 공간에 삽입이 불가능하게 되고 이럴 때 그 data는 메모리의 다른 부분으로 옮겨가서 저장된다. 그리고 현재 위치에는 data가 옮겨가서 저장된 메모리의 새로운 주소가 저장되게 된다. 만약 이후에 계속 update가 여러번 발생해서 data block이 계속 옮겨가게 된다면 그 전의 메모리 주소에는 새로운 주소가 계속 저장되게 되고, 이러한 현상이 반복되면 이 data를 reference할 때 링크를 계속 따라가야하므로 많은 성능저하가 일어나게 된다.
이러한 현상을 row migration이라 한다.
이 migration은 성능에 치명적인 영향을 미치게 된다.
따라서 이러한 현상으로 인한 성능저하를 피하기 위해서, data를 모두 삭제 후 다시 insert를 하면 메모리에 다시 순서대로 저장되 된다. 이렇게 일정한 주기적으로 DataBase를 관리해주어야 한다.
PS. varchar2 type은 'abc', 'abc ', 'abc '모두 다르게 인식, 저장한다.
'SQL'에 해당되는 글 10건
- 2016.12.07 [MSSQL] MERGE INTO - UPDATE or INSERT
- 2015.02.24 [Oracle/SQL] MAX,MIN 구하기 - KEEP, DENSE_RANK()
- 2014.09.07 [Oracle/SQL] PARTITION BY, OVER
- 2014.07.16 [Oracle 10 to 9] WM_CONCAT 함수 대체 XmlAgg
- 2014.05.21 [DB개념.용어] SQL이란/트랜잭션
- 2013.12.26 [SQL] 두 테이블의 값 비교, 한쪽에 없는 것 추출방법은?
- 2012.01.12 [SQL] CONNECT BY = WITH
- 2009.05.12 [데이타타입] VARCHAR2
- 2009.05.12 [SQL] DECODE 함수
- 2008.07.25 [SQL] inner join, outer join 의 차이점
[MSSQL] MERGE INTO - UPDATE or INSERT
Posted on 2016. 12. 7. 11:48
Filed Under DB
조건에 맞는 데이터가 있으면 UPDATE 수행, 없으면 INSERT수행하도록 하기
--CASE 1: 기본형
MERGE INTO TABLE_NAME
USING DUAL
ON (A = 1 AND B = 2)
WHEN MATCHED THEN
UPDATE SET COL1 = 1,
COL2 = 2
WHEN NOT MATCHED THEN
INSERT (COL1, COL2)
VALUES (1, 2)
--CASE 2: USING 안에 SELECT문
MERGE INTO SYS_USER A
USING DUAL
ON (A.ORG_ID = #{orgId}
WHEN MATCHED THEN
UPDATE SET A.USER_NM = #{userNm},
A.RMK = #{rmk},
A.TEL_NO = #{telNo},
A.EDIT_DT = SYSDATE
WHEN NOT MATCHED THEN
INSERT (USER_ID, USER_NM, TEL_NO, RMK, EDIT_DT, INS_DT)
VALUES (B.USER_ID, #{userNm}, #{telNo}, #{rmk}, SYSDATE, SYSDATE)
또는
MERGE INTO SYS_USER A
USING (SELECT USER_ID, ORG_ID, COPR_ID
FROM SYS_USER_DTL
WHERE ORG_ID = #{orgId}) B
ON (A.USER_ID = B.USER_ID)
WHEN MATCHED THEN
UPDATE SET A.USER_NM = #{userNm},
A.TEL_NO = B.TEL_NO,
A.RMK = #{rmk},
A.EDIT_DT = SYSDATE
WHEN NOT MATCHED THEN
INSERT (USER_ID, USER_NM, TEL_NO, RMK, EDIT_DT, INS_DT)
VALUES (B.USER_ID, #{userNm}, B.TEL_NO, #{rmk}, SYSDATE, SYSDATE)
[Oracle/SQL] MAX,MIN 구하기 - KEEP, DENSE_RANK()
Posted on 2015. 2. 24. 13:00
Filed Under DB
집계함수, KEEP은?
- 그룹별 집계결과를 1개 행으로 반환한다.
/* Syntax */
MAX() KEEP (DENSE_RANK FIRST[LAST] ORDER BY)
MIN() KEEP (DENSE_RANK FIRST[LAST] ORDER BY)
/* 사용예 */
-- PATH의 마지막 순서의 시설명을 얻는다. 동일한 순서이면 시설명 중 큰값을 얻는다.
SELECT MAX (TO_NE_NM) KEEP (DENSE_RANK LAST ORDER BY PATH_SEQ)
♣ 참고 URL
- MAX() KEEP(DENSE_RANK FIRST[LAST] ORDER BY) 2014/10/07
- DENSE_RANK 사용법 2012/08/08
- RANK(), ROW_NUMBER(), DENSE_RANK() 함수 - 순위함수 partition by 까지 설명 : 네이버 블로그 2015/01/27
[Oracle/SQL] PARTITION BY, OVER
Posted on 2014. 9. 7. 17:35
Filed Under DB
PARTITION BY : 특정 컬럼 기준으로 데이터를 나눈다.
OVER : PARTITION BY(그룹핑) 한 데이터를 정렬해서 뽑는다.
사용예:)
-- 예시1:
-- 데이터를 A, B별로 나눠서 (PARTITION BY)
-- RANK 역순 정렬 (나뉜 데이터 내부 정렬)
-- 상위 3건 데이터를 추출 (나뉜 데이터별 3건)
SELECT *
FROM
(SELECT GRP, MGNO, NAME, RANK() OVER (PARTITION BY A, B ORDER BY RANK DESC) RANK
FROM TBL_NAME
)
WHERE RANK <= 3;
-- 예시2: 특정 조건의 COUNT 구하기
SELECT A.COL1, A.COL2,
COUNT(*) OVER (PARTITION BY A.COL1, A.COL2) AS CNT
FROM TBL A, TBL B
WHERE A.ID = B.ID
AND B.CONDITION = 'VALUE';
참고1: PARTITION BY 구문
참고2: http://www.gurubee.net/lecture/1284 (다른예제들)
참고3: RANK(), DENSE_RANK(), ROW_NUMBER(), PARTITION BY
[Oracle 10 to 9] WM_CONCAT 함수 대체 XmlAgg
Posted on 2014. 7. 16. 09:25
Filed Under DB
DBMS가 Oracle 10g에서 9i로 대체되어서 WM_CONCAT 함수 미지원 상태가 되었을때 방법,
TO_CHAR(WM_CONCAT(C.OP_INFO)) --> 결과:'A,B,C,E' 를
SUBSTR(XMLAgg(XMLElement(X, ',') --> 결과: 'A,B,C,,E'
-- 이 경우 값이 NULL인 경우 토큰(,)만 추가되므로 DECODE로 ',,'를 제거하자.
SUBSTR(XMLAgg(XMLElement(X, DECODE(C.OP_INFO, NULL, '', ',')
-- 이렇게 수정하면 WM_CONCAT과 같은 결과 출력
(2017.02.15 추가) SUBSTR(를 빠뜨리면 한글이 깨져서 나오더라..
<참고>
http://www.gurubee.net/article/53637 ▶ XMLAgg 힌트
http://blog.naver.com/webdilh/30120533693 ▶ 상세설명
[DB개념.용어] SQL이란/트랜잭션
Posted on 2014. 5. 21. 13:41
Filed Under DB
SQL(Structured Query Language)
데이터베이스에 저장된 데이터를 조회, 입력, 수정 삭제하는 등의 조작이나, 테이블을 비롯한 다양한 객체(시퀀스. 인덱스 등)를 생성 및 제어하는 역할을 합니다.
SQL의 종류
데이터 정의어(DDL: Data Definition Language)
- 데이터베이스 객체를 생성, 삭제, 변경하는 SQL문(CREATE, ALTER, DROP 등)
- 데이터베이스 관리자나 응용 프로그래머가 데이터베이스의 논리적 구조를 정의하기 위한 언어로서 데이터 사전(Data Dictionary)에 저장 됩니다.
데이터 조작어(DML: Data Manipulation Language)
- 테이블 안의 데이터를 조작(SELECT, INSERT, UPDATE, DELETE 등)
- 데이터베이스에 저장된 데이터를 조작하기 위해 사용하는 언어로서 데이터 검색(Retrieval), 추가(Insert), 삭제(Delete), 갱신(Update) 작업 수행 합니다.
- 특정 테이블의 마지막 DML 시각 조회하기:
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM 테이블명 ORDER BY SCN_TO_TIMESTAMP(ORA_ROWSCN) DESC;
데이터 제어어(DCL: Data Control Language)
- 주로 DB관리자가 사용
- 데이터베이스 객체에의 권한 제어, 인덱스 재생성, 백업, 복원, 스케쥴 부여 등(BACKUP, GRANT, REVOKE, TRANSACTION, COMMIT, SAVEPOINT 등)
- 데이터에 대한 접근 권한 부여 등의 데이터베이스 시스템의 트랜잭션을 관리하기 위한 목적으로 사용되는 언어입니다.
트랜잭션 제어어(TCL: Transaction Control Language)
- COMMIT, ROLLBACK
SQL문 기본형
SELECT ... FROM ... WHERE ...
트랜잭션(Transaction)
트랜잭션이란, 전체가 성공하거나 전체가 실패하는 것이라고 이해하면 됩니다. 트랜잭션에 속한 어떤 작업이 하나라도 실패하면 데이터베이스의 상태는 트랜잭션이 시작되기 이전 상태로 돌아갑니다 (Roll Back). 트랜잭션은 서버에 있는 데이터의 일관성을 보장하기 위한 것입니다.
// 트랜잭션 시작 BeginTransaction() // SQL 1 실행 bResult := ExecSQL(SQL1); if bResult := False then begin // SQL문 실행 실패시 복원 Rollback(); Exit(); end // SQL 2 실행 bResult := ExecSQL(SQL2); if bResult := False then begin // SQL문 실행 실패시 복원 Rollback(); Exit(); end ... // SQL 모두 실행 성공시 커밋 Commit();
♣ 참고
데이터베이스(DB) 객체와 DDL, DML, DCL 을 알아보자 | chsmanager | 2013/11/13
[SQL] 두 테이블의 값 비교, 한쪽에 없는 것 추출방법은?
Posted on 2013. 12. 26. 10:17
Filed Under DB
create table tableA ( EMPNO int)
create table tableB ( EMPNO int)
insert into tableA values (1)
insert into tableA values (2)
insert into tableA values (3)
insert into tableA values (4)
insert into tableA values (5)
insert into tableB values (1)
insert into tableB values (2)
insert into tableB values (3)
insert into tableB values (4)
insert into tableB values (7)
-- A 테이블에만 존재하는 항목
select A.EMPNO
from tableA A left outer join tableB B on A.EMPNO=B.EMPNO
where B.EMPNO is null
-- B 테이블에만 존재하는 항목
select B.EMPNO
from tableA A right outer join tableB B on A.EMPNO=B.EMPNO
where A.EMPNO is null
-- A, B 테이블에 서로 없는것을 동시에
select A.EMPNO
from tableA A left outer join tableB B on A.EMPNO=B.EMPNO
where B.EMPNO is null
UNION ALL
select B.EMPNO
from tableA A right outer join tableB B on A.EMPNO=B.EMPNO
where A.EMPNO is null
-- 다른 방법으로 (A, B 테이블에 서로 없는것을 동시에)
select isnull(A.EMPNO,B.EMPNO)
from tableA A full outer join tableB B on A.EMPNO=B.EMPNO
where B.EMPNO is null or A.EMPNO is null
<출처: 네이버지식IN, 식물신 답변>
[SQL] CONNECT BY = WITH
Posted on 2012. 1. 12. 10:17
Filed Under DB
or COLUMN을 ROW로 쪼갤때도 사용(검색Key: column to row)
SELECT TO_CHAR(TO_DATE('201201','YYYYMM')+LEVEL-1, 'YYYYMMDD') AS DT,
TO_CHAR(TO_DATE('201201','YYYYMM')+LEVEL-1, 'DAY') AS WEEK
FROM DUAL
CONNECT BY LEVEL <= TRUNC(TO_DATE('201201','YYYYMM')+32,'MM')-TO_DATE('201201','YYYYMM');
DT WEEK
20120101 SUNDAY
20120102 MONDAY
20120103 TUESDAY
20120104 WEDNESDAY
20120105 THURSDAY
20120106 FRIDAY
20120107 SATURDAY
20120108 SUNDAY
20120109 MONDAY
20120110 TUESDAY
20120111 WEDNESDAY
20120112 THURSDAY
20120113 FRIDAY
20120114 SATURDAY
20120115 SUNDAY
20120116 MONDAY
20120117 TUESDAY
20120118 WEDNESDAY
20120119 THURSDAY
20120120 FRIDAY
20120121 SATURDAY
20120122 SUNDAY
20120123 MONDAY
20120124 TUESDAY
20120125 WEDNESDAY
20120126 THURSDAY
20120127 FRIDAY
20120128 SATURDAY
20120129 SUNDAY
20120130 MONDAY
20120131 TUESDAY
<출처: http://hermestop.tistory.com/145>
[데이타타입] VARCHAR2
Posted on 2009. 5. 12. 13:38
Filed Under DB
[SQL] DECODE 함수
Posted on 2009. 5. 12. 13:37
Filed Under DB
if 문의 역할을 수행하는 function
사용법:)
decode(expr, search, result, .., default)
사용예:)
SELECT code, name
FROM table
ORDER BY DECODE(code, '02', 1, '03', 2, '06', 3, '05', 4, 5)
의미:)
if( code == '02' ) code = 1
else if( code == '03') code = 2
else if( code == '06') code = 3
else if( code == '05') code = 4
else code = 5 // default
예제환경:) Oracle 9i
[SQL] inner join, outer join 의 차이점
Posted on 2008. 7. 25. 18:04
Filed Under DB
INNER JOIN :
- 두개의 집합 (A, B) 의 교집합 이라고 이해하시면 됩니다.
A {1,2,3} , B {2,3,4 }
이면 {2,3} 이 교집합입니다. 모두의 값에 있는 행들만 포함시키고 그렇지 않는 행들은 제외 시킵니다.
OUTER JOIN :
- OUTER JOIN 에는 LEFT , RIGHT, FULL OUTER JOIN 등의 세가지 형식이 있습니다.
두 테이블에서 지정된 쪽인 LEFT 또는 RIGHT 쪽의 모든 결과를 보여준후 반대쪽에 매칭되는 값이 없어도 보여주는 JOIN 입니다.
JOIN 이전에 나오는 테이블이 왼쪽(LEFT)테이블이 되고, JOIN 이후에 나오는 테이블은 오른쪽(RIGHT)테이블이 됩니다.
예를 보겠습니다.. ^^*
----------------------------------------------------------------------------------
CREATE TABLE T1 ( A INT PRIMARY KEY, B VARCHAR(20) )
INSERT INTO T1 (A,B) VALUES (1,'김대중')
INSERT INTO T1 (A,B) VALUES (2,'김영삼')
INSERT INTO T1 (A,B) VALUES (3,'노태우')
INSERT INTO T1 (A,B) VALUES (4,'전두환')
CREATE TABLE T2 ( A INT PRIMARY KEY, C VARCHAR(20) )
INSERT INTO T2 (A,C) VALUES (2,'SM5')
INSERT INTO T2 (A,C) VALUES (3,'SONATA')
--INNER JOIN
--T1, T2 의 INNER JOIN 이면 차를 소유하고 있는 사람만 추출된다.
-- ANSI SQL
SELECT T1.A,T1.B, T2.C
FROM T1 INNER JOIN T2 ON T1.A=T2.A
-- T-SQL
SELECT T1.A,T1.B, T2.C
FROM T1 , T2
WHERE T1.A = T2.A
-- LEFT OUTER JOIN : 좌측을 기준
-- ANSI SQL
SELECT T1.A,T1.B, T2.C
FROM T1 LEFT OUTER JOIN T2 ON T1.A=T2.A
-- T-SQL
SELECT T1.A,T1.B, T2.C
FROM T1 , T2
WHERE T1.A *= T2.A
-- RIGHT OUTER JOIN : 우측을 기준
-- ANSI SQL
SELECT T1.A,T1.B, T2.C
FROM T1 RIGHT OUTER JOIN T2 ON T1.A=T2.A
-- T-SQL
SELECT T1.A,T1.B, T2.C
FROM T1 , T2
WHERE T1.A =* T2.A
-- FULL OUTER JOIN : 양쪽 테이블을 둘다 기준으로
-- ANSI SQL
SELECT T1.A,T1.B, T2.C
FROM T1 FULL OUTER JOIN T2 ON T1.A=T2.A
-- T-SQL
SELECT T1.A,T1.B, T2.C
FROM T1 , T2
WHERE T1.A =* T2.A
UNION
SELECT T1.A,T1.B, T2.C
FROM T1 , T2
WHERE T1.A *= T2.A
출처: 네이버 지식IN
다른 예:)
◆ 조인 조건이 있을시!
FROM table1, table2
WHERE table1.a = table2.b
WHERE table1.a = table2.b // Inner Join
☞ Inner Join : 양쪽에 모두 값이 있어야 결과값으로 주신다.
WHERE table1.a = table2.b(+) // Outer Join
☞ 왼쪽 테이블 기준(모든 값)
/오른쪽 테이블에서는 왼쪽테이블의 table1.a값과 같은 table2.b값 + (null) 값
WHERE table1.ab(+) = table2.b // Outer Join
☞ 오른쪽 테이블 기준(모든 값)
/왼쪽테이블에서는 왼쪽테이블의 table1.a값과 같은 table2.b값 + (null) 값