'database'에 해당되는 글 18건

1. 원본 데이터베이스 스키마 스크립트 생성

   스크립팅 옵션 설정에서 [고급]버튼을 눌러 기본키, 외래키, 인덱스등도 포함되도록 설정


2. 타겟 데이터베이스에서 같은 이름으로 데이터베이스 생성

   생성 옵션 중 데이터정렬을 원본 데이터베이스와 같게 맞춘다.


3. 스키마 스크립트를 불러와 실행


4. 원본 데이터베이스에서 데이터 내보내기 기능을 이용

   자동증가 ID가 있는 테이블은 해당 테이블 마다 [매핑편집]버튼을 클릭하여 [ID입력 가능]을 체크해준다. SET IDENTITY_INSERT TableName ON


enter image description here

http://stackoverflow.com/questions/20948819/error-0xc0202049-data-flow-task-1-failure-inserting-into-the-read-only-column

'database > MSSQL' 카테고리의 다른 글

저장프로시저에서 CURSOR사용 자제  (0) 2015.07.21
export schema  (0) 2014.10.21
데이터 타입과 크기  (0) 2014.08.01
동적쿼리  (0) 2014.07.16
메일 발송  (0) 2014.01.28
블로그 이미지

란마12

,

아래와 같은 방법으로 CURSOR의 기능을 대체할 수 있다.


--Declare the Table variable 
DECLARE @Elements TABLE
(
    Number INT IDENTITY(1,1), --Auto incrementing Identity column
    ProductName VARCHAR(300) --The string value
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @N INT 

--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT

--Populate the TABLE variable using some logic
INSERT INTO @Elements SELECT Name FROM dbo.Products

--Initialize the looper variable
SET @N = 1

--Determine the number of rows in the Table
SELECT @Count=max(Number) from @Elements

--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);

--Loop through until all row processing is done
WHILE @N <= @Count

BEGIN
    --Load current value from the Table
    SELECT @CurrentValue = ProductName FROM @Elements WHERE Number = @N
    --Process the current value
    print @CurrentValue
    --Increment loop counter
    SET @N = @N + 1;
END

http://www.codeproject.com/Articles/34142/Understanding-Set-based-and-Procedural-approaches

'database > MSSQL' 카테고리의 다른 글

데이터 내보내기, 가져오기  (0) 2015.08.20
export schema  (0) 2014.10.21
데이터 타입과 크기  (0) 2014.08.01
동적쿼리  (0) 2014.07.16
메일 발송  (0) 2014.01.28
블로그 이미지

란마12

,

export schema

database/MSSQL 2014. 10. 21. 21:40

Set oSS = CreateObject("SQLDmo.SqlServer") 

oSS.LoginSecure = True 

oSS.Connect "000.000.000.000", "dbid", "dbpass"

Set oT = CreateObject("SQLDMO.Transfer")

Set oDb = oSS.Databases("dbname")

oT.CopyAllTables = True

oT.CopyAllViews = True

oT.CopyAllFunctions = True

oT.CopyAllStoredProcedures = True

oT.CopyAllTriggers = True

oDb.ScriptTransfer oT, 2, "c:\schema_export.sql"


oSS.disconnect

'clean up

set oT = Nothing

set oSS = nothing


WScript.Sleep 3000

'database > MSSQL' 카테고리의 다른 글

데이터 내보내기, 가져오기  (0) 2015.08.20
저장프로시저에서 CURSOR사용 자제  (0) 2015.07.21
데이터 타입과 크기  (0) 2014.08.01
동적쿼리  (0) 2014.07.16
메일 발송  (0) 2014.01.28
블로그 이미지

란마12

,

설치

database/mysql 2014. 8. 27. 09:51

### 재설치 한다면...

프로그램 추가/제거에서 MySQL을 삭제하고 수동으로 다음 폴더들도 삭제해야

재설치 시 이전 설치의 설정영향을 받지 않는다.

C:\Program Files\MySQL

C:\Documents and Settings\All Users\Application Data\MySQL


### 설치후..

"mysql -u root -p" 로그인 테스트


### 한글을 위하여..

C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6\my.ini

default-character-set=euckr 추가


### 외부접속 허용

특정 사용자 계정의 외부접속 허용하기 - 여기서는 root 를 예로 든다. 


1. mysql 접속 후 mysql database 선택 

mysql> use mysql;


2. user 테이블 살펴보기 

mysql> select host, user, password from user;


root 의 host 값들은 localhost, 127.0.0.1 등으로 기본 등록되어 있지만, 외부접속을 나타내는 값이 없다. 특정 아이피로 지정할 수도 있지만 여기선 % 기호로 어디서든 접속 가능하게 만든다. 


3. 권한 설정 

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root의 패스워드';

Query OK, 0 rows affected (0.03 sec)


4. 등록확인하기

mysql> select host, user, password from user;


root 계정의 host 필드에 % 가 등록되었는지 확인한다. 


5. refrash

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)


위 단계는 my.cnf 파일 수정후 서버를 재시작할 것이기 때문에 굳이 안해도 된다. 


6. my.cnf 에서 외부접속 관련사항 변경하기 

user@home:~$ sudo vim /etc/mysql/my.cnf 


파일 내용중 

bind-address = 127.0.0.1 

부분 주석처리후 저장하기 


7. mysql 재시작 

user@home:~$ sudo /etc/init.d/mysql restart 


8. 완료. 

이제 외부 클라이언트 프로그램에서 접속이 가능하다 ^^ 


http://beizix.egloos.com/2560401

블로그 이미지

란마12

,

분류

데이터 타입

범위

저장소크기

정수

Bit

O 또는 1

bit

Int

-2,147,483,648 ~ 2,147,483,647

4 바이트

Smallint

-32,768 ~ 32,767

2 바이트

Tinyint

0 ~ 255

1 바이트

Bigint

-2^63 ~ 2^63-1

8 바이트

부동소수점

Float[n]

-1.79E+308 ~ 1.79E+308 
n = 1~24

4 바이트

Float[n]

-1.79E+308 ~ 1.79E+308 
n = 25~53

8 바이트

Real

-3.40E + 38 ~ 3.40E + 38

4 바이트

문자데이터

char[n]

n = 1~8000

n 바이트

Varchar[n]

n = 1~8000

varchar [ ( n | max ) ]

max는 최대 저장소 크기가 2^31-1바이트임

입력한 데이터의 길이

Text

최대 2,147,483,647자의 가변길이

 

유니코드 
문자데이터

Nchar

n = 1~4000

n*2 바이트

nvarchar

n = 1~4000

nvarchar [ ( n | max ) ]

max는 최대 저장소 크기가 2^31-1바이트임

입력한 데이터의 길이*2 바이트

Ntext

최대 1,073,741,823자의 가변길이

 

이진데이터

binary

n = 1~8000

n+4 바이트

varbinary

n = 1~8000

varbinary [ ( n | max) ]

max는 최대 저장소 크기가 2^31-1바이트임

입력한 데이터의 길이+4 바이트

Image

최대 2,147,483,647자의 가변길이

 

날짜와시간

datetime

1753/1/1~9999/12/31

8 바이트

smalldatetime

1900/1/1~2079/6/6

4 바이트

화폐

money

-922,337,203,685,477.5808~ +922,337,203,685,477.5807

8 바이트

smallmoney

-214,748.3648~214,748.3647

4 바이트


기타 데이터 형식

decimal , numeric, cursor, timestamp, sql_variant, uniqueidentifier, table, xml


 

msdn 글~

text, ntext 및 image 데이터 형식 대신 varchar(max), nvarchar(max) 및 varbinary(max) 데이터 형식을 사용합니다.


ntext, text 및 image 데이터 형식은 Microsoft SQL Server 의 다음 버전에서 제거될 예정입니다. 향후 개발 작업에서는 이 데이터 형식을 사용하지 않도록 하고 현재 이 데이터 형식을 사용하는 응용 프로그램은 수정하십시오. 대신 nvarchar(max), varchar(max) 및 varbinary(max)를 사용합니다. 자세한 내용은 큰 값 데이터 형식 사용을 참조하십시오.


출처: http://cafe.naver.com/devweb/32

'database > MSSQL' 카테고리의 다른 글

저장프로시저에서 CURSOR사용 자제  (0) 2015.07.21
export schema  (0) 2014.10.21
동적쿼리  (0) 2014.07.16
메일 발송  (0) 2014.01.28
중복데이터 제거  (0) 2013.03.05
블로그 이미지

란마12

,

동적쿼리

database/MSSQL 2014. 7. 16. 14:08

DECLARE @sSql NVARCHAR(MAX);

DECLARE @a_sParam2 NVARCHAR(MAX);

SET @a_sParam2 = '''000000010'',''000000011''';

SET @sSql = 'UPDATE TB_TEST SET COL1 = ''1'' WHERE COL2 IN(@sMcodes)';

PRINT @sSql;

PRINT @a_sParam2;

EXEC SP_EXECUTESQL @sSql, N'@sMcodes NVARCHAR(MAX)', @sMcodes = @a_sParam2;


SQL INJECTION등 위험성 때문에 위의 코드는 작동하지 않는다.

결국 동적쿼리 쓰지 않고 파싱해서 임시테이블 생성함수 만들어 사용

UPDATE TB_PPS_PRS SET COL1 = '4', WHERE COL2 IN(SELECT ITEM FROM DBO.DelimitedSplit8K('000000010,000000011', ','))



'database > MSSQL' 카테고리의 다른 글

export schema  (0) 2014.10.21
데이터 타입과 크기  (0) 2014.08.01
메일 발송  (0) 2014.01.28
중복데이터 제거  (0) 2013.03.05
원격 접속 허용하기  (0) 2011.07.07
블로그 이미지

란마12

,

CREATE TABLE TEST(ID INT , name CHAR(4))

CREATE TABLE TESTB(BID INT , name CHAR(4))

GO


INSERT INTO test VALUES(1,'aaaa')

INSERT INTO test VALUES(2,'bbbb')

INSERT INTO test VALUES(3,'cccc')

INSERT INTO test VALUES(4,'dddd')

INSERT INTO testb VALUES(1,'xxxx')

INSERT INTO testb VALUES(2,'yyyy')

INSERT INTO testb VALUES(3,'zzzz')

GO


SELECT * FROM test

SELECT * FROM testb

GO

 


-- T-SQL OUTER JOIN

SELECT a.id,a.name, b.bid, b.name

FROM TEST a, TESTB b

WHERE a.id*=b.bid

AND b.name IS NULL


-- 일단 오류발생: 쿼리에서 ANSI 형식이 아닌 외부 조인 연산자("*=" 또는 "=*")를 사용합니다. 이 쿼리를 수정하지 않고 실행하려면 저장 프로시저 sp_dbcmptlevel을 사용하여 현재 데이터베이스의 호환성 수준을 80 이하로 설정하십시오. 가장 좋은 방법은 ANSI 외부 조인 연산자(LEFT OUTER JOIN, RIGHT OUTER JOIN)를 사용하여 쿼리를 다시 작성하는 것입니다. SQL Server의 다음 버전에서는 ANSI 형식이 아닌 조인 연산자는 역호환성 모드에서도 지원되지 않습니다.

 

-- 결과 (b.name IS NULL 필터링 후 조인)

1 aaaa 1 xxxx

2 bbbb 2 yyyy

3 cccc 3 zzzz

4 dddd NULL NULL




-- ANSI-SQL의 OUTER JOIN

SELECT a.id, a.name, b.bid, b.name

FROM TEST a LEFT OUTER JOIN TESTB b

            ON a.id=b.bid

WHERE b.name IS NULL


-- 결과 (조인 후 필터링)

4 dddd NULL NULL



'database' 카테고리의 다른 글

varchar냐 date냐  (1) 2011.08.19
오라클과 MSSQL 함수 비교  (0) 2010.06.18
DBMS에 따른 날짜포맷 변환  (0) 2010.06.18
tinyxml 수정사항  (0) 2010.01.15
블로그 이미지

란마12

,

메일 발송

database/MSSQL 2014. 1. 28. 11:08








다음작업을 수행하여 데이터베이스 메일 설치 -> 다음

프로필이름, 설명 적고, 아랫쪽 추가 클릭

계정이름 및 SMTP서버 정보 입력


DECLARE @body1 varchar(100)

SET @body1 = 'Server :'+@@servername+ ' My First Database Email '

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'MY_PROFILE_NAME' -- 프로필이름

,    @recipients = 'yonjunhee@gmail.com'

, @copy_recipients = null

, @blind_copy_recipients = null

, @body_format = 'TEXT'

,    @body = @body1 

, @importance = 'Normal'

, @sensitivity = 'Normal'

, @file_attachments = null

,    @query = 'SELECT * FROM [DBNAME].[DBO].TABLE1'

,    @subject = @body1 

,    @attach_query_result_as_file = 1  -- 쿼리문으로 만들어진 파일은 한개

,    @query_attachment_filename = 'SelectQuery.csv' -- 출력형태는 mssql 에서 지원하는 형태가 몇개 있츰 첨부될 파일명 지정

, @query_result_header = 1

, @query_result_width = 32767 -- can go to 32767 for query width

, @query_result_separator = ','

, @exclude_query_output = 0

, @append_query_error = 1

, @query_result_no_padding =1 -- turn off padding of fields with spaces


발송내역 확인

 SELECT * FROM [msdb].[dbo].[sysmail_allitems]


http://blog.naver.com/vfxx?Redirect=Log&logNo=100168933260


'database > MSSQL' 카테고리의 다른 글

데이터 타입과 크기  (0) 2014.08.01
동적쿼리  (0) 2014.07.16
중복데이터 제거  (0) 2013.03.05
원격 접속 허용하기  (0) 2011.07.07
BCP  (0) 2010.06.29
블로그 이미지

란마12

,

DELETE TB_DATE_IDXa FROM

(

SELECT

ROW_NUMBER() OVER (PARTITION BY HCODE_DT, KBNUM, BARCODE ORDER BY HCODE_DT) AS ROWNUM

FROM TB_DATE_IDX

) AS TB_DATE_IDXa

WHERE TB_DATE_IDXa.ROWNUM > 1

'database > MSSQL' 카테고리의 다른 글

동적쿼리  (0) 2014.07.16
메일 발송  (0) 2014.01.28
원격 접속 허용하기  (0) 2011.07.07
BCP  (0) 2010.06.29
간단한 내용들  (0) 2010.06.21
블로그 이미지

란마12

,

varchar냐 date냐

database 2011. 8. 19. 17:42

데이터베이스 설계시 날짜형의 컬럼을 일반적으로 date형으로 지정하여 줍니다.

조금 생뚱맞은 질문 일지도 모르나 정말 궁금해서 문의 드립니다.

date형으로 지정 하지않고 char형 등으로 지정 하였을때, 어떤 단점과 어려움이
있는지..
혹은 어떤 장점 등이 있는지.. 좀 알려주시기 바랍니다.

그럼 항상 즐겁고 행복한 일 많이 많이 일어나는 나날 되셔요.

이 글에 대한 댓글이 총 16건 있습니다.

장점은

데이터 핸들링이 편합니다. 이유는 date 타입 핸들링시 많은 제약 조건이 있습니다.

단점은

날짜형 컬럼에 잘 못된 날자가 입력 될수 있다는 점입니다.

제 주관적인 생각 입니다.^^

단테님이 2005-09-06 08:17:10에 작성한 댓글입니다. Edit

저는 그냥 Varchar2(10)으로 사용 합니다.

청구일,정산일,발행일...등등등 모두 이렇게 사용 하는데

불편함을 모드겠습니다.

아! 하나 날짜형 연산시 형변환을 해주어야 한다는것

아폴론님이 2005-09-06 08:57:40에 작성한 댓글입니다. Edit

날짜는 날짜로 하는 것이 편하다고 생각합니다...

님과 비슷한 문제를 올리셨던 것을 보고 제가 올렸던 답글입니다.

"

date로 하십시오

간혹 날짜형태이지만 날짜가 아닌 자료형도 있을 수 있습니다.

예를 들어 회계의 전표에서 이월 자료라던가 뭐 이런것을 00월이나 00일로 표현하기 위해 날짜를 문자형으로 사용하는 경우가 있습니다.

이런 경우라도 가능하면 날짜는 날짜로 가져가는 것이 좋습니다.

또 다차원 형태의 설계에서 날짜의 주기성을 고려한 날짜 차원 테이블을 가져가는 경우도 있습니다.

물론 이런 경우도 날짜형과 날짜 차원과의 매핑 정보 테이블을 가져가는 것이 좋습니다.

등등

그렇지만 날짜는 가능하면 날짜로 사용하시기를....

날짜를 썼을 때 여러모로 유리한 경우가 많습니다(특히 연산)

단 한가지 불리한 점은 LIKE 연산에서 묵시적으로 문자열로 형변환되어 인덱스를 안타게되는 경우가 있지만

제 경우는 날짜를 LIKE로 해야하도록 설계를 하지 않는 주의입니다.

만약 날짜를 LIKE로 해야 하는 경우가 있다면 사용자에게는 날짜 컬럼을 노출하고 문자열 형태의 숨긴 컬럼을 유지하여 데이타를 중복시킵니다.그리고 before 트리거로 값을 유지하구요

결론적으로

날짜같지만 응용프로그램의 성격상 날짜가 아닌 특별한 컬럼을 제외하고는 날짜형태는 DATE로 하십시오.

추가적으로 가능하면 날짜를 기본키에 넣지 마십시오.

그 이유는 날짜는 의미성이 강하기 때문에 변경가능성이 큽니다.

변경 가능성이 큰 경우는 기본키를 삼지 않는 것이 좋습니다.

대부분 날짜가 기본키가 되어야 하는 경우는 시계열적으로 발생하는 자료입니다.

또한 그런 자료는 대부분 기간을 두고 마감을 하는 형태가 많고요

시계열적 자료형태는 사실 단순 순번으로 키를 삼아도 응용상에 전혀 문제가 되지 않습니다(논리적 측면에서...그리고 대부분의 경우 성능 어쩌고 하지만 전체 응용 프로그램에서 성능에 영향을 줄 정도의 테이블들은 소업무당 많아야 3~4개 정도입니다.너무 이른 최적화는 좋지 않다고 생각합니다.성능 자체가 목적인 경우를 제외하면요.설계의 중점사항은 업무에 대한 반영과 유연성 규모가변성 및 업무적 적용 확장성등입니다. 이점을 놓지시면 안됩니다.)

단 집계성 테이블의 기본키를 잡을 경우는 날짜의 임의 변경 가능성은 감소합니다.

그러니 기본키로 삼으셔도 됩니다.

"

김흥수(protokhs)님이 2005-09-06 09:15:06에 작성한 댓글입니다.

저는 날짜 타입은 가급적 varchar2나 char로 하자는 주의 입니다.

대부분 조회시에 날짜를 기간으로 조회를 하게 되는데요,

형변환을 하지 않고도 바로 조회가 가능하기 때문입니다.

경우에 따라서 날짜에 시분초까지 가지고 있어야 할 경우가 있는데(예를 들면 등록일시, 수정일시 등) 그럴 경우는 date 타입으로 하지만,

꼭 char 타입이나 varchar2타입으로 컬럼을 하나더 만들어 주기도 합니다.

결국 해당 컬럼이 어떤 용도로 사용되어 지느냐에 따라서 달라지는데,

dba 입장에서야 당연히 date 타입이 관리하기 편하겠지만,

개발자 입장에서 본다면 분명 char 타입이나 varchar2타입이 편할 듯 합니다.

해서 저는 테이블 설계할때 컬럼의 특성에 따라 시분초를 가져가야 할 경우엔 date로 잡고, 조회조건으로 많이 사용될 컬럼의 경우 char 타입이나 varchar2타입으로 잡습니다.

만약 둘다 해당이 될 경우 우선적으로 char 타입이나 varchar2타입으로 잡은 후 date타입도 하나 더 잡아줍니다.

김흥수 님의 말씀에 반론을 굳이 제기 하자면.......

char 타입이나 varchar2타입으로 잡으신다면 기본키로 잡을 수도 있구요,

특히나 회계의 전표같은데서 날짜를 이상날짜 (예를들면 9999-99-99 등)과 같이 넣을 수 있기때문에라도 char 타입이나 varchar2타입으로 잡아야 한다고 생각합니다.

database가 업무를 지원하는거라고 봤을때 데이타베이스에서 받아들이지 못하기 때문에 사용하지 말아야 한다는건 너무 궁색한 변명이기 때문입니다.

char 타입이나 varchar2타입으로 데이타가 들어가 있을때 date로 형변환 해야 할 일이 얼마나 있을까요?

굳이 생각해 본다면 날짜계산 등의 함수를 사용할때 말고는 별로 없을 듯 합니다.

이것또한 제 개인적 생각입니다...^*^

이현석(dreamlhs)님이 2005-09-06 10:34:31에 작성한 댓글입니다.

날짜형은 일반적으로 (mysql 기준이나 오라클도 비슷할것으로 생각됨) '연월일' 은 4byte, '연월일시분초' 는 8byte 를 차지합니다.

'연월일' 을 char 나 varchar 를 쓸 경우 보통 10byte 를 쓰게 되죠..

날짜형 컬럼에 인덱스를 걸 경우 4바이트 일때와 8바이트 일때는 인덱스의 크기가 두배가 차이가 나죠....

그건 달리 말해 한번에 로드할 수 있는 인덱스의 크기가 반으로 줄어든다는 말이죠...

char 형이 사용에 편하다는 장점은 있겠지만 to_char 로 변환하는 오버헤드와 인덱스 사용의 효율을 고려해서 선택하는게 좋을것 같네요.

이경환(babocom)님이 2005-09-06 14:33:05에 작성한 댓글입니다.

이현석님 말씀에 한표...

보통 날짜로 조회하는 경우는 주로 거래일이나 영업일이 대부분인듯 합니다. 누가 게시물 올린 날짜로 조회를 거는 일은 거의 없으니까요.

시분초까지 필요한 데이타 아니면 char 타입으로 잡으시는게 좋을 듯... 물론 varchar도 가능합니다만 varchar는 가변길이이고 char는 고정길이이기 때문에 yyyymmdd 이런 식으로 char(8)을 사용하는게 좋은 듯 합니다.

저도 주로 일자단위로 거래가 일어나는 경우에는 char(8)을 사용하고 생성일시, 업데이트일시 등을 date로 가져갑니다. date로 가는 경우는 주로 추적용이지요.

단, 날짜에 대해서 연산이 많이 필요한 필드의 경우에는 date 타입도 좋을 듯합니다. 아니면 to_date를 한번 더 써야 하기 때문에...^^

꼭 필요하다면 필드를 두개 모두 가져가시구요. 예전처럼 저장공간 몇메가 몇십메가에 목숨거는 시대는 지난거 같으니까요. 너무 남발해도 안되겠지만, 지금은 사소한 저장공간 절약보다도 속도가 더 중요하지 않나 생각해봅니다. 속도 가지고 시비걸어도 저장공간 가지고 시비거는 사람 못봤습니다...ㅋㅋ..나그네 생각이었습니다.^^

추가.

참고로 오라클 date필드형은 고정길이 7바이트로 알고 있습니다. 그런데 문제는 7바이트 안에는 년월일시분초까지 같이 들어 있습니다. 그래서 데이타 저장시 sysdate로 하였다면 시분초까지 들어가게 되는데, 조회조건으로 조건을 받게 되면 년월일만 있으므로 필드값을 형변환하면서 뒤의 시분초를 잘라주여야만 합니다. 그것도 일종이 오버헤드일테구요..

나그네님이 2005-09-06 14:33:20에 작성한 댓글입니다.
이 댓글은 2005-09-06 14:47:14에 마지막으로 수정되었습니다.
Edit

음...

무언가 오해들이 있으신듯 한데요...

첫째로

조회조건에서 사용되므로 문자열 형이 좋다!

이부분에 대해서...

반드시 문자열이어도 CHAR는 쓰시지 않으시는 편이 좋습니다.

VARCHAR2(8)와 CHAR(8) 전부 다 실제 DB 이미지는 9자리가 잡힙니다. 그러니 VARCHAR2나 CHAR를 씁니다에서 CHAR는 빼시는 것이 좋습니다.

CHAR를 쓰시면 안되는 이유는 또 있습니다.

아주 많은 이유가 있는데요..

제가 언젠가 한번 이 부분에 대하여 제 나름대로 조사 연구한 것을 올려드릴 수 있는 기회가 생기면 좋을 듯 합니다.

이야기가 약간 옆길( CHAR)로 샜네요...

조회조건에 사용되어도 VARCHAR2가 좋은 경우가 없습니다.

"왜 없냐? LIKE 연산시 인덱스를 쓸 수 있는데!"

하시는 분들이 계실텐데요... BETWEEN 있습니다.

"뭐! 월만으로 LIKE 할 경우는?예를 들어 나는 전년대비 6월 자료만 필요한데!"

이러시는 분들이 계실 수 있는데요...

어차피 이 경우 VARCHAR2도 인덱스 못씁니다.

그리고 이런 경우(월만을 조건으로 가져오는 경우)의 제대로 된 해결 법은 집계테이블을 사용하거나 날짜의 특성인 주기성을 살려줄 수 있는 차원테이블을 사용하는 것이 정통적인 해결법이 됩니다.

VARCHAR2든 그냥 DATE든 좋은 해결책이 아닙니다.(덧붙여서 이런 전년대비 같은 것을 단순하고 간단히 SQL로 할 경우는 주로 날짜의 BETWEEN에 대비 기준의 group by에 decode sum이 되는 경우가 대부분이므로 조회 조건에 월이 명기되는 경우 자체가 드물죠)

반대로 조회조건으로 사용하기에 더 DATE이면 DATE를 써야 합니다.

DATE의 특성과 VARCHAR2의 특성은 엄연히 다르므로 오라클의 최적화기는 그러한 특성을 감안하여 질의 최적화를 통해 질의 계획을 사용합니다. 특히나 비용기반의 경우 데이타 분포 정보는 DATE와 VARCHAR2는 엄연히 달라집니다. 미묘한 성능의 차이나 최적화기가 더 나은 질의계획을 세울 수 있는 기회는 DATE 쪽에 더 많습니다.

또한 앞으로도 더 많아질 것입니다(오라클의 최적화기가 발전하면 할 수록...)

둘째로 형변환을 하지 않고도 바로 조회할 수 있다.

이 부분은 아마도 주로 문자열 연산환경(웹처럼)에서 개발을 하시는 분들이 이런 생각을 하실 수 있죠....왜냐면 문자열 중심 환경에서는 문자열로 변환을 해야 하니까요....

거꾸로 CS 환경의 사람들은...반대로 생각할 겁니다.파워빌더 같은거 쓰시는 분은 날짜일 때 날짜로 쓰시는 것이 형식화나 마스크 밸리데이션 등등에서 툴의 기능을 100% 사용할 수 있습니다...

또한 sysdate일 때 trunc해야 된다는 말씀도....날짜만 필요하면 select 할때가 아니라 insert 할때 trunc 하시는 것이 정상이구요...

sysdate일 때 trunc가 오버헤드면 문자열의 유의미성을 검증하는 것은 더 큰 오버헤드입니다. 왜냐면 동일한 작업을 하는 C코딩을 구현한다고 볼때 당연 trunc가 더 작은 비용이 됩니다....

셋째 DBA는 날짜가 유리하겠지만 개발자 입장에서는....

저도 11째 개발을 하고 있습니다만.

감히 말씀을 드리자면우리가 응용프로그램을 작성할 때 절대로 하지 말아야 할 것이 하나 있는데요...

무엇이냐면 데이타베이스 설계를 어플리케이션 의존적으로 작성하는 것입니다.

데이타베이스는 가능한 그 줄거리는 "업"에 의존적이어야 합니다.

그다음은 해당 업을 수행하는 조직에 의존적이어야 합니다.

마지막으로 고려 할 것이 어플리케이션이며

이 부분은 가능한한 최소화 하고 또한 가능하면 어플리케이션에서 사용할 메타스키마만을 어플리케이션 의존적으로 설계하셔야 합니다.

즉 데이타베이스 설계시에는 DBA입장이나 개발자 입장이나 이런 것이 기준이 되어서는 안된다는 것입니다.

예전에 저는 VB에서 콤보가 지원이 잘 안된다고...(예를 들어 파워빌더는 콤보가 data-value와 display-value를 따로 분리할 수 있었습니다.)

설계시에 코드를 명칭으로 하는 것을 보았습니다... 뭐 나름의 꽁수였겠지만... 코드가 추가되고 비슷한 이름의 다른 코드로 코드가 분할 될때... 기존 코드의 명칭을 바꾸어야 될 상황이 발생했고 결국은 대대적인 수술을 벌이더군요....

넷째 "특히나 회계의 전표같은데서 날짜를 이상날짜 (예를들면 9999-99-99 등)과 같이 넣을 수 있기때문에라도 char 타입이나 varchar2타입으로 잡아야 한다고 생각합니다."

이부분은.... 제 글을 정확히 읽어보시지 않으신 결과이신듯...

제 글에는 이런 경우를 날짜인듯하나 실제로는 날짜가 아닌 컬럼으로 말하고 있습니다....

특히나 회계 시스템의 날짜는 귀속회기문제와 집계구조의 문제 ...

그리구 과거 레거시 시스템과의 인터페이스, 과거의 회계시스템의 논리적 설계 패턴 등등으로 인하여... 사실은 날짜가 아닌데 날짜의 탈을 쓴 것이나 사실은 날짜인데 날짜가 아닌듯한 탈을 쓴 놈들이 있습니다.

즉 회계시스템의 회기문제와 관련한 날짜 어트리뷰트는 좀 다른 의미일 수 있다는 말입니다. 또한 대부분 날짜의 탈을 쓴 날짜가 아닌 컬럼은 개발자들은 잘 못느낄 수 있지만 설계자 관점에서 말씀을 드리면 설계 미스와 다양한 사용자 요구사항 사이에 끼인 미묘한 설계 관점에서 옵니다... 이말이 무슨 말이냐구요?

자 한번 생각해봅시다.사용자가 본인이 직접 9999-99-99 이런 날짜를 입력하겠다고 하시는 적이 있습니까?

아닐껍니다.제가 한가지 예를 들어보죠.어떤 날짜관련 필드가 NOT-NULL 입니다. 그 이유는 이것을 기준으로 조회를 하기에 인덱스를 걸기위해서 였습니다. 그런데 사용자는 "어! 처음 그자료를 넣을 때는 날짜가 미정입니다." 이렇게 말합니다. 그럼 여러분의 PM은 이렇게 말합니다."에이 그러면요 일단 9999-99-99 나 0000-00-00이라고 넣으세요."

그리고는 우리 개발자는 생각합니다. "와! 잘됐다.... 프로그램 안고쳐도 된다.날짜를 문자열로 하기를 잘했네!"

이렇듯 대부분의 날짜컬럼에 이상한 값이 들어가는 것이 용인되는 경우는 개발자 또는 설계자와 사용자간의 타협에 근거하여 논리적 설계의 미스를 덮어두는 경우가 허다합니다.아니면 숨겨진 컬럼(사용자는 못느끼는 컬럼이며 시스템적으로는 의미있는)의 경우(특히 히스토리관리에서 쓰이죠) 무효한 날짜를 입력하는 경우가 있죠?.... 이것도 역시 사용자의 직접적 요구가 아닌 개발상의 필요성에 의해서입니다.

물론 이부분도 논리 설계 및 집계 설계를 다시 하면 완전히 날짜로 쓸 수도 있습니다...

문제는 논리적 설계에 의존적이라는 뜻입니다.

결론적으로 말씀을 드리면...

저는 기본적으로 소잡는데는 소잡는 칼...

닭잡는데는 닭잡는 칼...

이런 주의 입니다.

날짜를 날짜로 쓰면 혹시 모를 미래의 이득을 얻을 수도 있습니다.

자 하나만 생각해보죠!

과거에 우리는 오라클이 인덱스를 띄엄 띄엄 읽는 방법을 생각해낼 줄을 몰랐습니다!

그런데 오라클은 지금은 인덱스를 그런식으로도 읽습니다!

맞죠?

우리가 날짜를 날짜로 쓰면 이후의 오라클 버전에서는 (특히나 오라클이 계속적으로 OLAP부분 및 OOP를 강화 지원하는 것으로보아) 날짜에 대한 부분 인덱싱 기능 같은 것을 지원하지 말라는 법도 없습니다.

그때가 되면 날짜의 월 인덱싱이나 일 인덱싱 시 인덱싱 등이 안된다는 법도 없죠...왜냐면 어떻게 생각하면 이러한 부분 인덱싱 기능은 일종의 BITMAP인덱스의 기법과 유사하니까요.... 물론 다른 구현도 가능하겠지만...

그럼 우리는 이러한 미래의 예측 불가능한 이득을 어떻게 하면 얻을 수 있을까요? 미래를 예견해서?

아닙니다....

아주 간단한 해답이 있는데요....

뭐냐면 가능한한 문제를 있는 그대로 보고 자연스럽게 접근하고 스스로 불필요한 튜닝이나 변형을 가하지 않는 것입니다.

소잡는데는 소잡는 칼...

닭잡는데는 닭잡는 칼...

말이 길어졌지만요....

사실 뭘 쓰든간에 큰 차이는 없지만요....

만약 차이가 없으면 가능한한 있는 그대로 쓰는 것이 좋지않을까요?^^

김흥수(protokhs)님이 2005-09-06 16:25:44에 작성한 댓글입니다.
이 댓글은 2005-09-06 17:02:00에 마지막으로 수정되었습니다.

아!

하나 더

제가 미래에 생길지모를 이득으로 말한 날짜의 부분 인덱싱...

이거!

지금도 ODCI(오라클 데이타 카트리지 인터페이스)를 사용하여 사용자 정의 인덱스 타입을 만들면 얼마든지 가능합니다.

물론 VARCHAR2이면서 논리적으로 날짜로 간주되는 데이타 타입에 대해서도 ODCI를 쓰면 가능하지만 ...

문제는 VARCHAR2를 사용하면 타입안정성이 없다는 단점이 생깁니다.

프로그래머 분들이시니 익히 아시겠지만... 프로그래밍 언어에서의 타입 안정성은 정말 중요한 이슈입니다.

김흥수(protokhs)님이 2005-09-06 16:46:51에 작성한 댓글입니다.

엄청 긴 장문의 글을 썼다가 논쟁의 소지가 있을 듯하여 다 지워버렸습니다.

그냥 가볍게 몇가지만 짚고 넘어갈께요.

김흥수님 말씀대로 between을 쉽게 쓰기 위해서 char나 vachar2로 잡자는 겁니다.

조건이 8월 29일 부터 9월 5일까지의 데이타를 조회하는 거라면 아무 생각없이 날짜컬럼 between '20050829' and '20050905' 이렇게 쓰면 되잖아요. 만약 날짜 컬럼에 date로 되어 있다면 저렇게 단순히 입력하면 29일 데이타는 안나오겠죠?

데이타 성격상 월단위로 보는 경우가 많으면 월단위로 테이블을 따로 만들던가, 컬럼을 따로 만들던가 하지 date냐 char/varchar2냐로 고민하진 않습니다.

dba혹은 data modeler가 해야 할 일은 무엇일까요?

아니면 application개발자가 해야 할일은?

각각의 사람들은 혼자서 일하는게 아니라 생각합니다.

서로 협력해서 잘 될 수 있게 하는게 각자의 역할이라 생각합니다.

제약조건을 잘 적용되어 들어가는지, 디폴트 값은 잘 들어가는지,

관계 설정은 잘 되어서 데이타는 맞게 들어가는지 확인 하는 일도 물론 중요하지만, 그 이전에 왜 그런 이상한 데이타가 들어갈 수 밖에 없는지 미리 확인하고, 협의해서 안들어가게 혹은 더 편하게 사용할 수 있게 하는게 각각의 사람들이 해야할 일이라 생각됩니다.

왜 아니겠습니까?

당연히 날짜는 date타입으로 하는게 좋으니까 date타입으로 만들어 놨겠지요.

근데 사용하다 보니까 불편한 점도 있더라는 거죠.

솔직히 불편한게 편한거 보다 더 많으니까 그러는거죠.

function based index를 사용하면 어느정도 해결은 가능하나 그렇게 해결하는거 보다는 설계를 그렇게 하는게 더 낫겠다는 생각이 들어서 그렇게 얘기한거지요.

어차피 정답은 없다고 생각하고 (솔직히 잘 몰라서 그럴수도 있음)

편하게 평소에 가지고 있던 생각을 적은건데 너무 공격적으로 나오시니 좀 씁쓸합니다.(저도 그랬나요?)

예전에 char와 varchar2 어느것을 쓰는게 좋은지에 대해서 논쟁 붙었을때의 모습을 보는듯 해서.....(물론 그때는 얻은 소득은 있었죠?)

이현석(dreamlhs)님이 2005-09-06 17:49:24에 작성한 댓글입니다.
이 댓글은 2005-09-06 17:49:55에 마지막으로 수정되었습니다.

제가 좀 공격적이었습니까?

그런 의도는 없었습니다.

오해하시지 마시길...

그리구 날짜 컬럼에 29일이 안나온다는 말씀이 무슨 뜻이신지?

between 하면 나옵니다.

한가지더 함수기반 인덱스를 사용하면 어느정도는 해결이 가능하다고 하신 부분...

어떤 문제가 어떻게 해결이 안되는데... 함수기반인덱스를 썼을 때 어떻게 어느정도 해결이 가능하다는 말씀이신지 이해가 안갑니다.

자세히 말씀해주셨으면 합니다.

그리구 마지막으로.

저 싸움 붙자고 하는 거 아니구여(이부분 오해 없으시길)

제가 분명히 밝혔듯이 저는 논리적 설계가 중요하고 날짜 타입이 문자타입보다 불리할 것이 없다는 것을 말씀드린 거구요..

그렇다면 타입안정성이 있는 날짜가 오히려 낫다는 점입니다.

사실 저는 이 논쟁이 있기 전에

이 부분에 관심이 있어서 테스트 해본적이 있습니다.

특히나 제가 그 때 테스트의 중점을 두었던 부분은 문자열이 날짜에 비하여 성능상 그리 떨어지지 않는다(지금 제입장과 반대 입장같죠?)는 것을 입증하기 위한 테스트를 한 적이 있습니다.

그랬던 이유는 저희 개발팀내에서 날짜를 쓰자는 부류와 문자를 쓰자는 부류가 붙었는데... 사실 별 차이 없다는 것을 입증하여 설득하기 위한 자료로 만들었었습니다.(지금도 가지고 있습니다.)

그건 그거고.... 제가 납득이 안가는 부분은 문자열이 유리하다고 생각한다는 점입니다.(가능하면 실례를 보여주시길...)

제 생각에는 안그렇다는 것이죠!

문자열에 비해 날짜가 진정 오버헤드가 있습니까?

전 거꾸로 문자열이 날짜에 비해 그리 큰 오버헤드는 없다고 증명하기 위해 테스트했던 사람으로 거꾸로의 이야기를 들으니 납득이 안가서 입니다.

길어지니 아랫단에 제가 실제 테스트 했던 것을 올려 보겠습니다.

이 테스트를 하게 된 경위는 토마스 카이트 아저씨가 문자열의 between 연산은 날짜의 between 연산에 비해 불리하다는 글을 쓴 것을 보고 우리 팀의 한 사람이 문제를 제기하면서 "에이 그럼 정말 해보자" 해서 했던 테스트입니다.

참고하시길^^

(제가 좀 무례했다면 사과드립니다.^^ 그냥 에이 저놈은 논쟁만 붙으면 환장하는군 하고 생각해 주십시오.^^)

김흥수(protokhs)님이 2005-09-06 18:17:48에 작성한 댓글입니다.
이 댓글은 2005-09-06 18:18:50에 마지막으로 수정되었습니다.

위에서 말씀드린 날짜 테스트 결과입니다.^^

1-2-3. DATE 타입
먼저 테이블을 만들고

SQL> create table t_date_test1
2 ( id date primary key,
3 contents varchar2(100)
4 );

테이블이 생성되었습니다.

경 과: 00:00:00.00
SQL> create table t_date_test2
2 ( id char(10) primary key,
3 contents varchar2(100)
4 );

테이블이 생성되었습니다.

경 과: 00:00:00.00

데이타를 삽입한다

SQL> insert into t_date_test1 select trunc(sysdate,'dd') + rownum,object_name from all_objects;

41046 개의 행이 만들어졌습니다.

경 과: 00:00:06.02

SQL> insert into t_date_test2 select to_char(trunc(sysdate,'dd') + rownum,'yyyymmdd'),object_name fr
om all_objects;

41046 개의 행이 만들어졌습니다.

경 과: 00:00:05.09

commit;

이제 기본 테스트를 위한 자료가 만들어 졌고

두개의 테스트 프로시져를 만든다.
SQL> create or replace procedure p_date_test_1
2 is
3 l_value varchar2(100);
4 ld_key date;
5 ls_key char(10);
6 begin
7 For lii in 1..100 Loop
8 For lij in 1..10000 Loop
9 ld_key := Trunc(Sysdate,'dd') + lij;
10 ls_key := to_char(ld_key,'YYYYMMDD');
11 Select
12 contents
13 Into
14 l_value
15 From t_date_test1
16 Where id = ld_key;
17 End Loop;
18 End Loop;
19 End;
20 /

프로시저가 생성되었습니다.

경 과: 00:00:00.04
SQL> create or replace procedure p_date_test_2
2 is
3 l_value varchar2(100);
4 ld_key date;
5 ls_key char(10);
6 begin
7 For lii in 1..100 Loop
8 For lij in 1..10000 Loop
9 ld_key := Trunc(Sysdate,'dd') + lij;
10 ls_key := to_char(ld_key,'YYYYMMDD');
11 Select
12 contents
13 Into
14 l_value
15 From t_date_test2
16 Where id = ls_key;
17 End Loop;
18 End Loop;
19 End;
20 /

프로시저가 생성되었습니다.

경 과: 00:00:00.00

이제 성능을 테스트 하기 위해 아래와 같은 SQL을 실행한다.

spool a.txt
select current_timestamp from dual;
exec p_date_test_1;
select current_timestamp from dual;
exec p_date_test_2;
select current_timestamp from dual;
spool off
ed a.txt

결과 보기

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
05/08/09 11:10:22.671000 +09:00

SQL> exec p_date_test_1;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
05/08/09 11:11:17.015000 +09:00

SQL> exec p_date_test_2;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
05/08/09 11:12:13.015000 +09:00

SQL> spool off

먼저 날짜의 경우 약 55초 가량 걸렸다. 문자(CHAR형)의 경우 약 56초가 걸렸다.
SQL의 수행 횟수는 각각 백만번씩이니까....이건 거의 차이 없다고 봐야 할 것이다.
그나마 약간의 차이가 있는 것은 데이타 유형에서 온다기 보다 그 길이에서 온다고 봐야 할 것이다.
날짜의 경우 7자리를 차지하고 char는 10자리(사실은 11자리) 니까...
이 정도 차이라면 속도문제만 따지고 보면 어느것을 사용하건 문제가 되지 않을 것이다.
물론 톰아저씨의 책을 보면 날짜와 문자의 경우를 비교하며 범위 스캔에서 문자 범위 스캔은 올바른
질의 계획 수립에 문제가 있을 수 있다는 것을 보여준다.음... 그말은 맞다고 생각한다.
그럼 얼마나 영향을 줄까?
두 테이블을 analyze 한 다음 이틀 씩 범위를 주고 스캔을 해보자 톰아저씨의 말대로 해보자.

SQL> analyze table t_date_test1 compute statistics for table for all indexes for all indexed columns
;

테이블이 분석되었습니다.

SQL> analyze table t_date_test2 compute statistics for table for all indexes for all indexed columns
;

SQL> create or replace procedure p_date_test_1
2 is
3 l_value varchar2(100);
4 ld_key1 date;
5 ld_key2 date;
6 ls_key1 char(10);
7 ls_key2 char(10);
8 begin
9 For lii in 1..100 Loop
10 For lij in 1..10000 Loop
11 ld_key1 := Trunc(Sysdate,'dd') + lij;
12 ld_key2 := ld_key1 + 1;
13 ls_key1 := to_char(ld_key1,'YYYYMMDD');
14 ls_key2 := to_char(ld_key2,'YYYYMMDD');
15 Select
16 max(contents)
17 Into
18 l_value
19 From t_date_test1
20 Where id between ld_key1 and ld_key2;
21 End Loop;
22 End Loop;
23 End;
24 /

프로시저가 생성되었습니다.

SQL> create or replace procedure p_date_test_2
2 is
3 l_value varchar2(100);
4 ld_key1 date;
5 ld_key2 date;
6 ls_key1 char(10);
7 ls_key2 char(10);
8 begin
9 For lii in 1..100 Loop
10 For lij in 1..10000 Loop
11 ld_key1 := Trunc(Sysdate,'dd') + lij;
12 ld_key2 := ld_key1 + 1;
13 ls_key1 := to_char(ld_key1,'YYYYMMDD');
14 ls_key2 := to_char(ld_key2,'YYYYMMDD');
15 Select
16 max(contents)
17 Into
18 l_value
19 From t_date_test2
20 Where id between ls_key1 and ls_key2;
21 End Loop;
22 End Loop;
23 End;
24 /

프로시저가 생성되었습니다.
SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
05/08/09 11:29:58.187000 +09:00

SQL> exec p_date_test_1;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
05/08/09 11:31:33.921000 +09:00

SQL> exec p_date_test_2;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
05/08/09 11:33:17.031000 +09:00

SQL> spool off

결과를 보면 날짜의 경우 1분 35초 문자열의 경우 1분 44초 약간의 차이가 나타난다.
그러나 개인적인 사견으로는 이정도는 무시할 수 있을 정도의 차이이다. 이정도 성능차이 때문에?
음... 아마도 다른 부분에서 성능개선하는 것이 나을듯 하다.

김흥수(protokhs)님이 2005-09-06 18:20:31에 작성한 댓글입니다.

흥수님...you win...!!! ^*^

제가 실수한 부분이 있는데 29일이 안나오는게 아니라 9월 5일이 안나오는군요..

실제 데이타는 20050905 시분초 이렇게 들어가 있으니깐...

글구 date 타입에 between을 쓰면 에러나지 않나요?

형변환 함 해주셔야 할듯한데...

아~~

님의 프로시져 내에서 형변환을 해주셨네요...

그런 형변환을 안해도 된다는 말씀들 저 포함 위에 분들이 하시는 겁니다.

응답속도 등의 차이에서는 저도 뭐라 드릴 말씀이 없습니다.

당근 오라클이 꼴통이 아닌데, 좋으니까 date 타입을 만들었겠죠.

이것도 어디까지 제 편견이었나 봅니다...

이현석(dreamlhs)님이 2005-09-06 18:52:45에 작성한 댓글입니다.
이 댓글은 2005-09-07 08:22:56에 마지막으로 수정되었습니다.

형변환을 한 이유는 그것이 필요해서가 아니라

테스트상 날짜와 문자를 공평하게 하기 위해 일부러 한 것입니다.

잘 보시면 날짜 테스트용 프로시져와 문자 테스트용 프로시져 모두 일부러 형변환을 하고 있습니다.^^

그리고 문자에 숫자를 더하기 위해 형변환을 하고 있는 것입니다.

즉 거꾸로 문자이기 때문에 형변환이 필요했던 것입니다.

김흥수(protokhs)님이 2005-09-06 20:59:48에 작성한 댓글입니다.
이 댓글은 2005-09-06 21:00:46에 마지막으로 수정되었습니다.

단테님, 아폴론님, 이경환님, 나그네님
특히나 열띤 토론을 펼쳐주신 김흥수님, 이현석님 정말 너무너무 감사합니다.

님들의 애정어린 관심의 글이 많은 분들에게 크나큰 도움이 되었길 바라면서요.
제겐 넘너무 소중한 것들을 깨치게 해주신 듯 하네요..^^*

꾸~~벅~~^^*

그리고 마지막으로 김흥수님, 이현석님 멋있으십니다..--v

박승호(linux815)님이 2005-09-06 23:01:47에 작성한 댓글입니다.
이 댓글은 2005-09-06 23:38:27에 마지막으로 수정되었습니다.

참고로, 모 사이트에서 얻은 정보 공유합니다.

님들 모두모두 행복하고 많이 웃을 수 있는 하루 되세요.

--------------------------------------------------------------

기본적으로 대용량데이터베이스 솔류션 등에서는 날짜를 문자열(varchar2)로 쓰는 것이 여러가지로 유리하다고 되어 있습니다.
주로 프로그래밍의 편이성와 date type 사용시 잘못된 conversion으로 인한 성능저하를 우려하는 내용입니다.

예를 들어 작성시 월단위 검색이나 집계시
where ymd_char like '200509%' 또는 group by substr(ymd, 6)등의 사용으로 작성이 용이하며

date 타입 사용시에는 where to_char(ymd_date, 'yyyymm') like '200509%'와 같은 방식을 사용하는 경우가 있어
인덱스 사용을 저해하는 문제가 생길 수 있다는 것입니다.


반면 토마스카이트는 이펙티브 오라클을 비롯한 저서에서 DBMS의 제공되는 기능을 이용하자는 일관된 논지를 주장합니다.

즉, 문자열은 문자열 타입에 datedate 타입에 저장하자는 것입니다.

첫째는 무결성의 관점에서 둘째는 성능의 관점에서.
첫째는 잘 아시겠지만 '20050931'과 같은 자료가 문자열타입에서는 저장이 가능하다는 것입니다.
물론 어플리케이션에서 체크하겠지만 오류의 소지가 있다는 거죠.

둘째 성능의 관점에서는 문자열타입 사용시 옵티마이저(CBO)가 올바른 판단을 하기 힘들다는 것입니다.

where ymd_char between '20041231' and '20050101'과
where ymd_date between to_date('20041231', 'yyyymmdd') and to_date('20050101', 'yyyymmdd') 은 CBO가 다른 비용을 상정할 수 있다고 합니다.

date type에서는 2004년 12월 31일 이후 2005년 1월 1일 이라는 것을 CBO가 알 수 있지만 문자열 타입에서는 안되겠죠.


토마스 카이트는 이펙티브 오라클에서 같은 범위이지만..
문자열 타입 사용시 FULL SCAN을 하는 경우와 date 타입 사용시 INDEX RANGE SCAN을 하는 경우를 예를 들어 보여주고 있습니다.


물론 프로그래밍은 약간 번거로울 수 있습니다.

문자열 타입이면 간단하게 아래처럼 할 것을..

where ymd_char like '200509%' date type이면 아래처럼 복잡하게 해야합니다.

where ymd_date between to_date('200509' || '01', 'yyyymmdd') and add_months(to_date('200509' || '01', 'yyyymmdd'), 1) - 1
또는 last_day함수를 써서 9월 말일을 구해야 겠죠.


하지만 데이터베이스의 목적인 무결한 데이터와 우수한 성능을 위해선 프로그래밍의 약간의 번잡함은 감수해야겠습니다.

--------------------------------------------------------------

박승호(linux815)님이 2005-09-06 23:36:12에 작성한 댓글입니다.

'database' 카테고리의 다른 글

T-SQL과 ANSI SQL JOIN방식 차이  (0) 2014.06.03
오라클과 MSSQL 함수 비교  (0) 2010.06.18
DBMS에 따른 날짜포맷 변환  (0) 2010.06.18
tinyxml 수정사항  (0) 2010.01.15
블로그 이미지

란마12

,