30초 만

제일 자주 쓰는 집계 함수입니다

 

함수종류	내용
COUNT	인수들의 총 수
SUM	인수들의 총 합
AVG	인수들의 평균
GROUPING	그룹함수(cube,rollup관련)
MAX	식의 최고값
MIN	식의 최소값
VARP	해당 값의 모집단에 대한 분산
VAR	분산
STDEV	표준편차
STDEVP	해당 값의 모집단에 대한 표준편차
CHECKSUM	해당 목록에 대한 검사값
CHECKSUM_AGG	Null값을

 

함수종류	내용
ABS	절대값
RAND	0과1사이의 랜덤값
ROUND	반올림(사사오입)
CEILING	해당값보다 큰 최소 정수값
FLOOR	해당값보다 작은 최대 정수값
SIN	사인
COS	코사인
TAN	탄젠트
ASIN	아크사인
ACOS	아크코사인
ATAN	아크탄젠트
ATN2	아크탄젠트
COT	코탄젠트
DEGREES	라디안의 단위
EXP	지수
LOG	자연대수
RADIANS	라디안
POWER	거듭제곱
PI	원주율
LOG10	10을 저점으로한 자연대수
SIGN	부호
SQIARE	제곱
SQRT	평방근

mssql 랜덤  숫자값 구하기   : select convert(int, abs(rand()*100) )   0~99까지

 출쳐 http://jajking.textcube.com/54

--**********************************************************************
--      함수(날짜열)
--**********************************************************************

함수(날짜열)
DATE구분	구분약자	DATE구분	구분약자
year	yy	week	wk
quarter	qq	hour	hh
month	mm	minute	mi
day of year	dy	second	ss
day	dd	millisecond	ms
항목	내용
GETDATE()	시스템에서 제공하는 날짜와 시간 값
산술연산	-- date + NUMBER -- date - NUMBER

MSSQL] 날짜 형식 변환
date 를 convert 시킬때 형식
SELECT convert(char(10),getdate(),126)
-> 2006-06-02
SELECT convert(char(10),getdate(),102)
-> 2006.06.02
SELECT convert(char(10),getdate(),111)
-> 2006/06/02
SELECT convert(char(10),getdate(),112)
-> 20060602
* 문자열 변환
SELECT  convert(char(10),convert(datetime, convert(char(8),cast('20060602' as decimal  (10))) ,120),102)

세기 포함 안함(yy)	세기 포함(yyyy)	표준	입력/출력**
-	0 또는 100 (*)	기본값	mon dd yyyy hh:miAM(또는 PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	영국/프랑스	dd/mm/yy
4	104	독일	dd.mm.yy
5	105	이탈리아	dd-mm-yy
6	106	-	dd mon yy
7	107	-	Mon dd, yy
8	108	-	hh:mm:ss
-	9 또는 109 (*)	기본값 + 밀리초	mon dd yyyy hh:mi:ss:mmmAM(또는 PM)
10	110	USA	mm-dd-yy
11	111	일본	yy/mm/dd
12	112	ISO	yymmdd
-	13 또는 113 (*)	유럽 기본값 + 밀리초	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 또는 120 (*)	ODBC 표준	yyyy-mm-dd hh:mi:ss(24h)
-	21 또는 121 (*)	ODBC 표준(밀리초)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126(***)	ISO8601	yyyy-mm-dd Thh:mm:ss.mmm(스페이스 없음)
-	130*	회교식****	dd mon yyyy hh:mi:ss:mmmAM
-	131*	회교식****	dd/mm/yy hh:mi:ss:mmmAM

--------------------------------------------------------------------------------
날짜 포맷 변환
--------------------------------------------------------------------------------
[ 형식 : 'YYYY.MM.DD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY.MM.DD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 102)
3. DB2 : REPLACE(CHAR(DATE(date_exp),ISO), '-', '.')
 
[ 형식 : 'HH:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'HH:MI:SS')
2. MSSQL : CONVERT(VARCHAR, date_exp, 108)
3. DB2 : CHAR(TIME(date_exp) , JIS )

 

[ 형식 : 'YYYY/MM/DD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY/MM/DD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 111)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '/')

[ 형식 : 'YYYYMMDD' ]
1. Oracle : TO_CHAR(date_exp, 'YYYYMMDD')
2. MSSQL : CONVERT(VARCHAR, date_exp, 112)
3. DB2 : CHAR(DATE(date_exp))

[ 형식 : 'HH24:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'HH24:MI:SS')
2. MSSQL : CONVERT(VARCHAR(8), date_exp, 114)
3. DB2 : CHAR(TIME(date_exp))

[ 형식 : 'YYYY.MM.DD HH24:MI' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY.MM.DD HH24:MI')
2. MSSQL : CONVERT(VARCHAR, date_exp, 102) + ' ' + CONVERT(VARCHAR(5), date_exp, 114)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '.') || CAST( TIME(date_exp) AS CHAR(5))

[ 형식 : 'YYYY/MM/DD HH24:MI:SS' ]
1. Oracle : TO_CHAR(date_exp, 'YYYY/MM/DD HH24:MI:SS')
2. MSSQL : CONVERT(VARCHAR, date_exp, 111) + ' ' + CONVERT(VARCHAR(8), date_exp, 114)
3. DB2 : REPLACE(CHAR(DATE(date_exp), ISO), '-', '/') || CAST( TIME(date_exp))

-- 숫자만 추출 예제

CREATE  FUNCTION ep_GetOnlyNumber(@Str VARCHAR(MAX))
              RETURNS VARCHAR(MAX)
AS
BEGIN
        DECLARE @retStr varchar(MAX)
        SET @retStr = ''

        DECLARE @i int
        SET @i = 1

        While @i <= Len(@Str)
        BEGIN  
                --if isnumeric(substring(@str,@i,1)) = 1 -- isnumeric 사용하면 '-' 도 가져오기 때문에 안됨.
                IF substring(@Str,@i,1) BETWEEN '0' AND '9'
                       SET @retStr = @retStr + substring(@Str,@i,1)
                SET @i = @i + 1
         END

         RETURN @retStr
END

 

--예제 용 10분뒤의 날짜 20100118000000  14자리형으로 변환

dbo.ep_GetOnlyNumber(CONVERT(varchar,  dateadd(mi, -10, getdate()),20))


-->
--********************************************************************
--      YEAR,MONTH,DAY,DATEDIFF함수(날짜열)
--********************************************************************
--year, month, day 해당일의 년월일 값을 돌려준다.
select year(getdate())'year',
   month(getdate())'month',
   day(getdate())'day'
--datediff(part,date1,date2)/두 날짜 사이의 DATE구분 사이 값을 계산함.
--   (part, date1, date2)
select datediff(dd,'1999/02/15','2000/02/15')'day',
   datediff(mm,'1999/02/15','2000/02/15')'month',
   datediff(yy,'1999/02/15','2000/02/15')'year'
--
select datediff(dd,'1999/11/06','2000/02/15')'day',
   datediff(mm,'1999/11/06','2000/02/15')'month',
   datediff(yy,'1999/11/06','2000/02/15')'year'
--회원테이블의 생일과 오늘의 일수 차이를 검색! (Alias는 회원명, 생일, 오늘, 태어난지?)
select mem_name"회원명", mem_bir"생일",
   getdate()"오늘",
   datediff(dd,mem_bir,getdate())"태어난지?"
 from member
--본인의 생년월일과 오늘의 일수 차이를 비교 검색!
select datediff(dd,'1972/08/14',getdate()) "차이는"

--*******************************************************************
--      Datename, Datepart함수(날짜열)
--*******************************************************************
--datename(part,date)/date에서 datepart부분의 ASCII값(구분문자)
--datepart(part,date)/date에서 datepart부분의 정수값(구분숫자)
select convert(char,getdate()), '<==현재날짜'
select datename(yy,getdate()),datepart(yy,getdate()),'<==년도'
select datename(qq,getdate()),datepart(qq,getdate()),'<==분기'
select datename(mm,getdate()),datepart(mm,getdate()),'<==월'
select datename(dy,getdate()),datepart(dy,getdate()),'<==일수'
select datename(dd,getdate()),datepart(dd,getdate()),'<==해당일'
select datename(wk,getdate()),datepart(wk,getdate()),'<==주수'
select datename(dw,getdate()),datepart(dw,getdate()),'<==요일'
select datename(hh,getdate()),datepart(hh,getdate()),'<==시간'
--회원테이블에서 구길동회원의 생일의 DATENAME 과 DATEPART를 검색!
--(Alias 는 회원명, 생일, 기타 上 同)
select mem_name"회원명", mem_bir"생일",
   datename(dw,mem_bir)'기타上同',
   datepart(dw,mem_bir)'기타上同'
 from member
   where mem_name='구길동'
/* 월 (2002년 2월)만 입력받아 해당월의 시작일과 종료일을 검색!
Alias는 해당월, 시작일, 종료일) */
--******************** 1. 2002-02-01의 1달 후 -1....
select '200년02월' 해당월, '200-02-01' 시작일,
   dateadd(mm,1,'2000-02-01')-1 "종료일"
--******************** 2. 2002-03-01의 1일 전....
select '200년02월' 해당월, '200-02-01' "시작일",
   dateadd(dd,-1,'2000-03-01') "종료일"



SQL 기타 함수

select convert(numeric,'123456.9') result1,
   cast('123456.9' as numeric) result2
--
select convert(numeric(10,2),'123456.9') result1,
   cast('123456.9' as numeric(10,2)) result2
--
select convert(float,'123456.9') result1,
   cast('123456.9' as float) result2
--
select convert(decimal,'123456.9') result1,
   cast('123456.9' as decimal) result2


--***********************************************************************
--      함수(conversion) : 날짜로 치환
--***********************************************************************
--날짜로 치환/모양만 날짜형인 문자를 DATE로 치환
select'19990101'result1,'1999-01-01'result2,
   '1999-01-01 00:00:00.000'result3
--
select convert(datetime,'20201025',112)" result1",
   convert(datetime,'20201025')" result2",
   convert(datetime,'2020-10-25 10:15:20.000') " result3",
   cast('2020-10-25 10:15:20.000' as datetime) " result4"
--
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) 치환날짜
   from member
--1900년대 사람들만 있다고 가정했을 경우 19를 더해서 밀레니엄버그를 없앨 수 가 있다.
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 500일을 더한 날짜를 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) + 500 치환날짜
   from member
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 '1974-01-01'부터 '1975-12-31'사이의 날짜를 검색!
--(Alias 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) 치환날짜
   from member
   where convert(datetime,'19'+mem_regno1)between'1974-01-01'and'1975-12-31'
--회원테이블에서 생일을 문자로 치환한 후 LIKE '1975%'의 조건을 사용하여 해당회원을 검색!
--Alias는 회원명, 생일)
select mem_name 회원명, mem_bir 생일
   from member
   where convert(char, mem_bir, 121) like '1975%'



--is null, is not null /null값인지 아닌지 비교
--isnull(c,d)/c가 null값이면 d값으로 치환!
--nullif(c,d)/c와 d를 비교하여 같으면 null을, 다르면 c값을 돌려준다.

--AVG(columm)/조회범위 내에 해당 컬럼들의 평균값
--                     /DISTINCT : 중복된 값은 제외
--                     /ALL : Default로써 모든 값을 포함(all을 쓰지 않아도 Default값으로 적용)
--                     /Column명 : NULL값은 제외
--                     /* : NULL값도 포함(COUNT함수만 사용)
--                     isnull을 사용하여 NUll값은 '0'등으로 나오게 한다.

--ISDATE(c) / 타당한 날짜 포맷인지 확인 : 날짜면 1, 아니면 0
--ISNUMERIC(n) / 타당한 숫자포맷인지 확인 : 숫자면 1, 아니면 0
--CASE WHEN / 연속적인 조건문(자주활용되는 함수)
--                     CASE WHEN ~ THEN ~ ELSE ~ END

ERWIN 에서 외부 데이터 테이블 정보를 가져오고 싶어 할때
ERWIN 파일이 깔려 있는 폴더에다가 DLL을 복사 해서 넣으면된다.

select /* 오늘날짜 시분초 포함*/
              to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')
    from dual

 

- 변환형 함수

    TO_CHAR : 숫자나 날짜를 문자열로 변환

    TO_NUMBER : 문자를 숫자로 변환

    TO_DATE : 문자를 날짜로 변환

 

      - TO_CHAR에서 숫자를 문자로 변환시에 형식에 사용되는 요소

          9 : 일반적인 숫자를 나타냄

          0 : 앞의 빈자리를 0으로 채움

          $ : dollar를 표시함

          L : 지역 통화 단위(ex \)

          . : 소숫점을 표시함

          , : 천단위를 표시함

      - TO_CHAR에서 날짜를 문자로 변환시에 형식에 사용되는 요소

          SCC : 세기를 표시 S는 기원전(BC) 

          YEAR : 연도를 알파벳으로 spelling

          YYYY : 4자리 연도로 표시

          YY : 끝의 2자리 연도로 표시

          MONTH : 월을 알파벳으로 spelling

          MON : 월의 알파벳 약어

          MM : 월을 2자리 숫자로 표시

          DAY : 일에 해당하는 요일

          DY :  일에 해당하는 요일의 약어

          DDD,DD,D : 연도,월,일 중의 날짜를 숫자로 표시

          HH , HH24 : (1-12) , (0-23)중의 시간을 표시

          MI : 분을 표시

          SS : 초를 표시

          AM(A.M.),PM(P.M.) : 오전인지 오후인지를 표시

 

      TO_CHAR(문자값,‘형식’)

        숫자를 문자로 변환 : TO_CHAR(350000,'$999,999')→ $350,000

        숫자를 날짜로 변환 : TO_CHAR(SYSDATE,'YY/MM/DD')→ 95/05/25

      TO_DATE(문자값, ‘형식’) : TO_DATE('10 SEPTEMBER 1992','DD MONTH YYYY')→10-SEP-92

      TO_NUMBER(문자값) : TO_NUMBER('1234')→ 1234

오라클 날짜함수

//퍼온곳
//http://sqler.pe.kr/web_board/view_list.asp?id=599&read=4382&pagec=23&gotopage=23&block=2&part=MyBoard7&tip=
//

안녕하십니까? 유일환(ryu1hwan@empal.com)입니다.
SQL Server의 실행계획과 인덱스에 대해 간단히 적어볼까 합니다.
먼저 인덱스가 무엇인지는 다들 아시리라 믿습니다.
물론, 실행계획이 무엇인지도요.
인덱스가 있는 테이블과 없는 테이블간에 성능 차이, 성능을 확인 하는 정도를 다루고 있습니다.
계속해서 어려운 부분도 다루도록 노력하겠습니다.

1. 먼저 할일은 인덱스 없는 테이블과 인덱스 있는 테이블을 생성하는 일입니다.
    IF OBJECT_ID('TBL_NO_IDX') IS NOT NULL DROP TABLE TBL_NO_IDX
    IF OBJECT_ID('TBL_IDX') IS NOT NULL DROP TABLE TBL_IDX
    go

    CREATE TABLE TBL_NO_IDX
    (   noInt int,
        tmpTxt char(800))
    go

    INSERT  INTO TBL_NO_IDX VALUES (1, 'A')

    -- 만건의 데이터를 TBL_NO_IDX테이블에 입력
    DECLARE @i as int
    DECLARE @maxNo as int
    SET @maxNo = 1

    WHILE @maxNo < 10000
    BEGIN
        INSERT INTO TBL_NO_IDX
        SELECT  noInt + @maxNo, 'A'
        FROM    TBL_NO_IDX
        WHERE   noInt + @maxNo <= 10000

        SELECT  @maxNo = MAX(noInt)
        FROM    TBL_NO_IDX
    END

    go

    SELECT  COUNT(*)
    FROM    TBL_NO_IDX

    go

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

TBL_NO_IDX 테이블에는 만건의 데이터가 만들어져 있습니다.

이 테이블을 CREATE할 때, tmpTxt라는 컬럼에 800을 주었으므로 한 레코드당

크기는 804바이트 정도가 될 것입니다. SQL Server는 한 페이지(*)에 8000바이트 정도의

데이터를 담을 수 있습니다. 그러므로 한 페이지에 10건 정도의 데이터가

들어가게 됩니다.

*페이지(page):SQL Server의 페이지 입출력을 위한 최소 단위로서 데이터를 읽거나 쓸때

페이지 단위로 동작하게 됩니다.

여기서 1000건의 데이터를 넣기 위해 사용한 방법을 보시면 WHILE문에 조건으로 @maxNo를

사용한 것을 볼 수 있습니다. 이 방법을 사용하게 되면, 10000건의 데이터를 금방 집어 넣을

수 있습니다.

WHILE 루프가 처음 돌때, 2건의 데이터가 들어가 있고, 2번째 돌 때는 4건의 데이터가 들어가고,

3번째 돌때는 8건의 데이터가 들어가고, 4번째는 16건, 이런식으로 2의 n승만큼의 데이터를 한번에

넣게 되므로 WHILE문을 만번 반복하지 않고, 데이터를 넣을 수 있게 됩니다. 이런 것이 집합처리(*)의

기본원리라 할 수 있습니다. 들어가 있는 집합들에 최대값을 더해서, 2배의 데이터를 만들어 내는

것입니다.

*집합처리 : 프로그래머들이 SQL문을 다룰때, 한 개의 레코드에 대해서만 생각을 하고 순차적으로

접근하는 경향이 있습니다. 하지만, SQL은 비 절차적이기 때문에, 이는 옳은 방법이 아닙니다.

우리 개발자들은 SQL을 다룰때, 집합적으로 데이터를 다루어야 합니다. 한번에 한 건의 데이터가 아닌

한번에 여러 집합을 다룰 수 있어야 합니다.
     

2.데이터를 모두 담았으므로 해당 테이블과 동일한 TBL_IDX라는 테이블을 만들어 내고, TBL_IDX

테이블에는 인덱스를 생성해 줍니다.

    --인덱스 생성을 위한 테이블 생성

    SELECT  * INTO TBL_IDX
    FROM    TBL_NO_IDX

    go

    --인덱스 생성

    CREATE UNIQUE INDEX tbl_idx_idx1 ON TBL_IDX(noInt)

    go

    --테이블의 크기 확인

    SELECT  id, used, rowcnt, name
    FROM    sysindexes
    WHERE   id IN (OBJECT_ID('TBL_NO_IDX'), OBJECT_ID('TBL_IDX'))

    go

    --1200정도의 페이지를 가지고 있다.
    id          used        rowcnt               name
    1977058079  1251        10000                TBL_NO_IDX
    1993058136  1139        10000                TBL_IDX
    1993058136  25          10000                tbl_idx_idx1



위의 SQL문을 보면 SELECT INTO라는 것이 있는데, 이것은 FROM절에 기술한 테이블과 동일한

테이블을 쉽게 만들 수 있게 해줍니다. SELECT INTO는 여러가지로 응용이 가능하므로 나중에

따로 설명하도록 하겠습니다.

TBL_IDX에는 인덱스를 설정하고, TBL_NO_IDX에는 인덱스를 설정하지 않습니다.

CREATE UNIQUE INDEX로서 UNIQUE한 인덱스를 TBL_IDX에 설정합니다.

인덱스의 이름은 tbl_idx_idx1입니다.

준비된 테이블들의 크기를 보기 위해 sysindexes테이블을 SELECT합니다.

여기서 used라는 컬럼은 해당 object가 얼만큼의 페이지를 가지고 있는지를 보여줍니다.

1페이지는 8000바이트, 즉 8K이므로(정확히 8000바이트는 아닙니다.) 각 테이블이 8 * 1200KB정도의

크기를 가지고 있습니다. 그리고, tbl_idx_idx1에 대해서는 25페이지가 따로 설정되어 있는 것을 알

수 있습니다. 인덱스는 int컬럼인 noInt만 가지고 설정을 했으므로 실제 테이블보다 크기가 적을 수

밖에 없겠죠.



3. 실행계획을 보기 위해 설정을 합니다.

이 설정을 하기 전에, 그래픽으로 보는 실행계획은 OFF를 시키셔야 합니다.

    --실행계획과, IO를 보기위한 설정을 ON

    SET STATISTICS IO ON        --페이지의 입출력 수를 알 수 있다.

    SET STATISTICS PROFILE ON   --실행계획에 대한 결과를 알 수 있다.

    go

여기서 PROFILE ON은 실행계획을 보여주지만, 해당 실행계획이 실제 실행된 결과도 보여주게 됩니다.

나중에 보시면 아시겠지만, ROWS와 Executes를 보여줌으로써, 해당 과정에서 몇 Row가 발생되고,

해당 과정이 몇번 실행 됐는지를 알 수 있습니다.



4. 다음의 쿼리를 통해 인덱스를 사용한 경우와 사용하지 않은 경우의 상태를 비교 합니다.

    SELECT  *
    FROM    TBL_NO_IDX    --인덱스가 없는 테이블
    WHERE   noInt = 3  



    SELECT  *
    FROM    TBL_IDX     --인덱스가 있는 테이블
    WHERE   noInt = 3

    go 

--인덱스가 없는 테이블의 실행계획 및 IO

Rows    Executes StmtText
1   1   SELECT * FROM [TBL_NO_IDX] WHERE [noInt]=@1
1   1     |--Table Scan(OBJECT:([PLANDB].[dbo].[TBL_NO_IDX]), WHERE:([TBL_NO_IDX].[noInt]=Conv

'TBL_NO_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 1250, 물리적 읽기 수 0, 미리 읽기 수 0.

--인덱스가 있는 테이블의 실행 계획 및 IO

Rows    Executes StmtText
1   1   SELECT * FROM [TBL_IDX] WHERE [noInt]=@1
1   1     |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PLANDB].[dbo].[TBL_IDX]))
1   1          |--Index Seek(OBJECT:([PLANDB].[dbo].[TBL_IDX].[tbl_idx_idx1]), SEEK:([TBL_IDX].[

'TBL_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0.

실행 한 결과를 보면, 결과 밑에 실행계획이 보여지고, 메세지 창을 확인하게 되면 페이지의 IO를
보여줍니다.
TBL_NO_IDX의 실행계획은 Table Scan이라는 연산을 하고 있습니다.

(실행계획을 보는 순서에 대해서는 나중에 따로 다루도록 하겠습니다.)

TBL_NO_IDX에는 WHERE절에 기술된 noInt = 3이라는 값을 찾을수 있는 인덱스가 없으므로 테이블 전체를

스캔한다는 것이 됩니다. 그 결과, 논리적 읽기 수는 실제 테이블의 크기인 1251페이지에 가깝다는

것을 알 수 있습니다. 여기서, 논리적 읽기 수는 실제 데이터를 위해 페이지를 읽은 수가 됩니다.

물리적 읽기 수는 메모리에 데이터 페이지가 없으므로 실제 물리적으로 저장된(하드같은곳.)곳에서

페이지를 읽어온 횟수가 됩니다.(여기서는 해당 페이지들이 모두 메모리에 올라와 있으므로 물리적

읽기 수가 없습니다.) 실제로, 똑같은 쿼리가 처음에는 느리고, 두번째 부터 빠른 이유는 이런

이유입니다. 처음 실행된 쿼리의 데이터는 물리적 읽기로 데이터를 찾아와야 하고, 두번째 같은 쿼리는

메모리에서 데이터를 찾아오기 때문이죠.

결과적으로 TBL_NO_IDX테이블에서 한건을 찾아오기 위해 1250페이지를 뒤진 것을 확인 할 수 있습니다.

반면에 TBL_IDX테이블에 대한 것을 살펴 보면, 실행계획은 TBL_NO_IDX과 틀린 것도 알 수 있고,

논리적 읽기 수 역시 3밖에 안되는 것을 알 수 있습니다.

실행계획을 먼저 보면, Index Seek를 하고 있습니다. 이것은 인덱슬르 이용해서 조건에 해당하는

값을 찾아가고 있다는 행동입니다. Index Seek와 Index Scan이 있는 이 두가지가 틀리다는 것을

알아야 합니다. Index Scan은 인덱스의 Leaf Page(인덱스 키값과 해당 키값의 데이터의 주소가

저장된 페이지)를 모두 뒤지는 연산이고, Index Seek는 인덱스 트리(B Tree)를 검색해서 원하는

데이터를 바로 찾아내는 방법입니다.

B Tree 역시 나중에 다루도록 하겠습니다.

여기서는 Index Seek를 했고, 그 후 Bookmark Lookup이라는 연산을 수행했습니다.

Bookmark Lookup은 실제 데이터를 찾아가는 과정이라고 보시면 됩니다. 인덱스안에는 noInt컬럼에 대한

값밖에 없습니다. 그러므로 tmpText까지 포함한 실제 데이터를 보여주기 위해서 실제 데이터를

찾아가는 과정이죠.

이렇게 TBL_IDX에서 데이터를 찾아내는 과정이 진행되었습니다.

결과적으로 TBL_IDX에서는 3개의 페이지만을 읽었다는 것이 중요합니다.

해당 SQL의 성능은 곧 페이지의 IO와 많이 연관이 있기 때문입니다.

페이지 IO를 줄이기 위해 노력하는 것이 해당 SQL문에 대한 튜닝이 될 수 있습니다.



5. 인덱스를 사용하지 않는 경우에 대해서도 알아보도록 합시다.

    SELECT  *
    FROM    TBL_NO_IDX
    ORDER BY noInt  ASC



    SELECT  *
    FROM    TBL_IDX
    ORDER BY noInt  ASC



    'TBL_NO_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 1250, 물리적 읽기 수 0, 미리 읽기 수 0.
    'TBL_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 1113, 물리적 읽기 수 0, 미리 읽기 수 0.

Rows    Executes StmtText
10000   1   SELECT * FROM [TBL_IDX] ORDER BY [noInt]
10000   1     |--Sort(ORDER BY:([TBL_IDX].[noInt] ASC))
10000   1          |--Table Scan(OBJECT:([PLANDB].[dbo].[TBL_IDX]))

go

위 두개의 SQL문은 정렬을 하고 있고, 모든 데이터를 보여주는 작업을 하고 있습니다.

TBL_NO_IDX는 특별히 설명할 것이 없고, TBL_IDX 테이블에 대한 SQL에 대해 말씀드리고 싶은 것은

인덱스가 있는데도, 인덱스를 사용하지 않았다는 것입니다. 이것은 SQL Server의 실행계획을 만들어

내는 옵티마이져란 녀석이 판단을 하기에, 인덱스를 사용하지 않는 것이 더 효율적이라

판단을 했기 때문입니다. 사실, 해당 SQL에서는 인덱스를 사용하게 할 수 있는 여건이 아무것도 없는

것이죠. 그러므로 TBL_IDX도 테이블을 스캔한 후에, Sort라는 작업을 하는 것을 볼 수 있습니다

우리는 테이블에 데이터를 집어넣을 때, 1부터 시작해서 데이터를 넣었습니다. 그러므로 실제 저장된데이터는 1부터
10000까지 시퀀스하게 저장되어 있습니다. 하지만, 관계형 DB는 순서가 없으므로

이런 순서는 언제가는 깨지게 됩니다. 그러므로, ORDER BY를 이용해서 꼭, 순서를 명시하는

SQL문이 필요한 것이죠.



6. 인덱스를 사용해서 Sort를 제거하기



    --인덱스를 사용해서 Sort를 제거

    SELECT  *
    FROM    TBL_IDX (index = tbl_idx_idx1)



--어마어마한 논리적 읽기 수
'TBL_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 10024, 물리적 읽기 수 0, 미리 읽기 수 0.
Rows    Executes StmtText
10000   1   SELECT *  FROM TBL_IDX (index = tbl_idx_idx1)
10000   1     |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PLANDB].[dbo].[TBL_IDX]))
10000   1          |--Index Scan(OBJECT:([PLANDB].[dbo].[TBL_IDX].[tbl_idx_idx1]))



힌트를 주어서 Order By를 제거 할 수 있습니다. 데이터는 무순서로 저장이 되지만,

인덱스는 인덱스로 지정한 컬럼에 대해 순서적으로 저장되어 있으므로 인덱스를 경유해 검색을

하게 된다면 Order By와 동일한 결과를 얻을 수도 있습니다.

하지만 위의 SQL의 IO를 보시면, 10024라는 어마어마한 물리적 읽기가 수행된 것을 알 수 있습니다.

그것은 위의 SQL이 모든 데이터를 대상으로 하므로 실제 데이터를 찾아가는 과정이 필요하기 때문입니다.

위의 실행계획은 이런식으로 실행이 됩니다.

첫번째 인덱스 컬럼에 위치->주소를 참조 실제 데이터를 가져온다.->다음 인덱스로 이동

두번째 인덱스 컬럼에 위치->주소를 참조 실제 데이터를 가져온다.->다음 인덱스로 이동

...

..

만건이 이렇게 수행되어지므로 만번의 Bookmark Lookup이 일어나게 되고, 이것에 대한

부하가 심한 것입니다. 만약에 클러스터드 인덱스를 사용한다면, 이런 Bookmark Lookup은 일어나지 않죠.



7. 커버된 인덱스의 사용

    --커버된 인덱스를 사용해서 페이지IO를 줄일 수 있다.

    SELECT  noInt
    FROM    TBL_IDX (index = tbl_idx_idx1)



--커버된 인덱스는 확연한 성능개선

Rows    Executes StmtText
10000   1   SELECT noInt  FROM TBL_IDX (index = tbl_idx_idx1)
10000   1     |--Index Scan(OBJECT:([PLANDB].[dbo].[TBL_IDX].[tbl_idx_idx1]))

'TBL_IDX' 테이블. 스캔 수 1, 논리적 읽기 수 24, 물리적 읽기 수 0, 미리 읽기 수 0.



결과를 보면 인덱스 스캔만 있을 뿐, Bookmark Lookup이 없는 것을 알 수 있습니다.

이것은 SELECT절에 noInt만 표시되어 있기 때문입니다. noInt컬럼에 대한 정보는

인덱스에 이미 있으므로 실제 테이블을 뒤질 필요가 없는 것입니다.

이렇게 인덱스만으로 쿼리문이 해결되는 것을 인덱스로 커버된 쿼리라고 합닙다.

커버된 쿼리의 내용 역시 후에 자세히 따로 다루도록 하겠습니다.

하지만 이런 커버된 쿼리는 SQL에 있는 모든 컬럼들이 인덱스 안에 있어야 한다는 제약조건이

있다는 것을 유의해서 사용해야 합니다.



간단하게 인덱스와 실행계획에 대해 살펴보았습니다.

인덱스를 사용하는 쿼리는 Index Seek가 나타나게 되고, Index Seek가 나온다 해도 Bookmark Lookup이 많다면

곧, 성능에 부하가 생긴다는 것이죠. Bookmark Lookup은 인덱스로 찾고자 하는 데이터가 많을수록 많이

나타나게 됩니다.

그러므로 인덱스를 이용해서 데이터를 찾는 것은 많은 데이터가 아닌, 적은 데이터여야 합니다.

물론, 이 적다는 의미가 어느정도인지는 시스템에 따라, 상황에 따라 달라지게 됩니다.

아무리 적어도 1000건까지는 인덱스를 사용해도 괜찮다고 제 개인적으로 생각합니다.

(물론, 인덱스의 깊이에 따라 달라지겠지만요. 그외 클러스터드 인덱스도 고려요소가 됩니다.)



여기서 여러분들이 최소한 건져야 할 것은,

Index Scan이냐, Seek와, Bookmark Lookup이 무엇인지, 그리고 실행계획을 볼려면 어떤

옵션을 주어야 하는지 정도라 생각되어 집니다.

부족한 글 읽어주셔서 감사합니다.

데브피아에서 펀 글인뎅.. 좋은 내용입니다.

이곳 Tip&Tech의 글 중에 게시판의 속도를 높이자.. 라는 글에서 사용한 방법이
바로이 파생 테이블이기도 합니다.

/*
-----------------------------------------------------------------------
    소개
-----------------------------------------------------------------------

+ 파생 테이블이란?
우선, 파생 테이블에 대해서 간단히 소개를 하겠습니다.
파생 테이블(Derived Table, 인라인 뷰라고도 하죠)
T-SQL에서 FROM절에는 일반적으로 Table_Source 형식의 데이터 집합이 올
수 있죠.

다음은 테이블 소스 형식의 예입니다.
- Table
- View
- Rowset 함수
- OPENXML 함수
- Derived Table(파생 테이블)

위에서 보는 것처럼, 테이블 소스가 될 수 있는 형식은 다양합니다.
그러나, SQL을 전문적으로 배우기 전에는 FROM절 하면 항상 테이블 또는 뷰 정도만을
생각하게 됩니다.

이번엔 파생 테이블에 대한 기본적인 사용법과 함께, 어떤 경우에 파생 테이블을
적용할 수 있는지, 또한 다른 방법들과 어떤 차이가 있는지 다양한 상황을 통해
살펴 보도록 하겠습니다.

+ 집합 개념의 시작
이번 강좌를 진행하면서, 제 개인적인 목적은 Advanced T-SQL에 대한 내용을
전반적으로 살펴보면서 집합적 개념을 인식할 수 있도록 유도하는 것입니다.
이전 강좌를 통해서도 집합 개념에 대한 중요성을 계속 언급을 드리고 있습니다.
파생 테이블 역시, 제대로 사용하기 위해서 그리고 그 이점을 최대한 활용하기 위해선
집합적 개념의 이해와 접급 시도를 필요로 합니다.

특히 두 개 이상의 테이블을 조인하거나, 다른 집합에서 파생된 데이터를 가공하거나
다른 집합을 기준으로 새로운 집합을 산출하는 처리를 하는 경우 파생 테이블의 사용
여부는 상당한 차이를 보여줍니다.

여러분들은, 집합 개념의 이해가 어떤 내용인지를 이 강좌의 마지막 예제를 통해서
느끼실 수가 있으실 겁니다.


+ 서브 쿼리, 임시 테이블의 대체
파생 테이블을 적용할 수 있는 또 다른 사례가 바로 서브 쿼리나 임시 테이블을
사용하는 쿼리입니다.
SQL의 정답은 없다라는 말을 곧 잘 합니다. 하나의 결과를 산출하기 위해서 시도할
수 있는 방법은 여러가지가 있으며, 하나의 방법이 좋은 결과를 보였다고 해서 다른
SQL에서도 동일하다는 보장은 없다는 뜻 입니다.
서브 쿼리, 임시 테이블(또는 2000의 Table 데이터형) 그리고 파생 테이블 이러한
방법 중 가장 좋은 결과(성능 및 비용 측면)를 가지는 방법을 선택하는 시도가
필요합니다.


-----------------------------------------------------------------------
    준비 사항
-----------------------------------------------------------------------
자, 그럼 강좌를 보기 전에 기본적인 준비가 있어야 겠죠.

쿼리 분석기에서 윈도우를 하나 오픈하시죠(Ctrl-N),
그리고 엔터프라이즈 관리자에서 Northwind 데이터베이스 다이어그램을 하나
작성하시면 좋겠습니다.

제공되는 예제는 기본적으로 Northwind DB를 사용할 것입니다.


-----------------------------------------------------------------------
    파생 테이블의 미리보기 예제
-----------------------------------------------------------------------
우선, 간단한 예제를 먼저 보도록 하겠습니다.

*/
USE Northwind
GO

SELECT p.productid, p.quantity
FROM (SELECT productid, sum(quantity) quantity
FROM dbo.[order details] od
GROUP BY productid) as p



/*
-----------------------------------------------------------------------
    파생 테이블 작성 규칙
-----------------------------------------------------------------------
1. ANSI-92 SQL 표준.
2. ()안에 SELECT문을 기술.
3. 반드시 테이블 별칭(Alias)을 기술합니다.


    -- 테이블 별칭을 생략한 경우
    SELECT p.productid, p.quantity
    FROM (SELECT productid, sum(quantity) quantity
            FROM dbo.[order details] od
            GROUP BY productid)

    서버: 메시지 170, 수준 15, 상태 1, 줄 4
    줄 4: ')' 근처의 구문이 잘못되었습니다.

파생 테이블의 ANSI-92 표준이기 때문에 다른 DBMS에서도 동일하게 적용하실 수 있는
기능입니다. SQL Server는 6.5버전부터 지원이 됩니다.

작성 방법은 어렵지 않을 것입니다.
쉽게 생각하셔서, SELECT문과 별칭을 가진 테이블 소스라고 생각하시면 되죠.

기본적인 사항을 배우셨으니, 이제 실제 다양한 사례를 통해서 파생 테이블을 어떤
경우에 적용할 수 있는지 보도록 하겠습니다.

그냥 읽어보지만 마시고, 반드시 실행과 테스트를 해 보시기 바랍니다.
*/


/*
-----------------------------------------------------------------------
    파생 테이블 적용 사례
-----------------------------------------------------------------------
1. 파생 테이블과 조인하기

예제. Products 테이블에서 제품별 단가를 산출하되,
같은 종류 별(categoryid) 평균단가(avgprice) 를 함께 출력한다.


참조테이블.
    Products

출력양식.
    productid unitprice avgprice
    ----------- --------------------- ---------------------
    1 18.0000 42.3708
    2 19.0000 42.3708
    3 10.0000 23.0625
    ...

*/
USE northwind

-- 각 제품의 단가 출력
SELECT p.productid, p.unitprice
    FROM dbo.products as p

-- 같은 종류별로 평균 단가 출력
SELECT avg(unitprice) as avgprice
    FROM dbo.products
    GROUP BY categoryid


-- 1) 서브 쿼리를 이용한 최종 결과
SELECT p.productid, p.unitprice,
    (SELECT avg(unitprice)
        FROM dbo.products a
        WHERE a.categoryid = p.categoryid
        GROUP BY a.categoryid) as avgprice
   
FROM dbo.products as p


-- 2) 파생 테이블을 이용한 최종 결롸
SELECT p.productid, p.unitprice, a.avgprice
FROM dbo.products p JOIN
(SELECT categoryid, avg(unitprice) as avgprice
    FROM dbo.products
    GROUP BY categoryid) AS a    ON a.categoryid = p.categoryid
ORDER BY p.productid



/*
2. (GROUP BY 절과 함께 UPDATE하기)

예제. 매출액( sum(unitprice) ) 5위 까지의 제품의 단가를 20% 상향 조정
하기 위한 update 작성.

임시 테이블을 작성할까?


참조테이블.
    [Order Details]

출력양식.
    없음.

*/

-- 매출액 5위까지 산출
SELECT TOP 5 productid, sum(unitprice) as unitprice
FROM dbo.[order details]
GROUP BY productid
ORDER BY unitprice DESC


-- 매출액 1위인 38번 제품의 현재 단가: 263.5 (확인용)
-- (여러분들의 금액을 적어두시면 됩니다)
SELECT productid, unitprice
FROM dbo.products
WHERE productid = 38


-- 파생 테이블을 이용한 경우, 316.2로 변경(38번 제품)
UPDATE p SET p.unitprice = p.unitprice * 1.2
FROM dbo.products as p JOIN
    (SELECT TOP 5 productid, sum(unitprice) as unitprice
            FROM dbo.[order details]
            GROUP BY productid
            ORDER BY unitprice DESC) as a
    ON a.productid = p.productid


/*
3. 재미있는 예제.

(통계치에 대한 분포 데이터 출력)

예제. 고객별 주문횟수에 대한 분포 데이터 출력
*/
SELECT customerid, count(*) as ordercnt
    FROM dbo.orders
    GROUP BY customerid
    ORDER BY ordercnt


-- 파생 테이블을 이용한 경우, 재미있죠?
SELECT cnt, Replicate('=', count(*)) as freq
FROM (SELECT customerid, count(*) as cnt
        FROM dbo.orders
        GROUP BY customerid) as c
GROUP BY cnt



/*
문제 유형.
    1:M관계의 조인후 1 관계 산출하는 SQL

파생 테이블을 적용해서 성능 향상을 유도할 수 있는 일반적인 사례가 바로
GROUP BY 절이 사용되는 경우입니다.

GROUP BY 절은 M쪽 관계의 집합을 1쪽 관계로 산출하는 집합 연산입니다.
특히 조인과 함께 적용되는 경우 JOIN과 GROUUP BY 이 두가지 집합 연산의 처리
순서는 성능에 있어서 중요한 요소가 됩니다.

제 말의 의미를 아시겠는지?
아래 예제를 살펴보시고, 실제 성능 평가는 마지막에서 언급합니다.

요구하는 출력양식을 위한 SQL을 직접 작성해 보십시오.
그리고 파생 테이블을 사용한 경우와 비교를 해 보시기 바랍니다.


참조테이블.
    Orders, [Order Details], Products

출력사항.
    1996-12-31 이전에 주문된 제품별 단가의 합계 출력

출력양식.
    -------------------------------
    제품명 단가합계
    -------------------------------
    Alice Mutton 249.6000
    Aniseed Syrup 8.0000   
    ...

SQL 구현 방법
    1. 일반 조인을 이용한 경우
    2. 파생 테이블을 이용한 경우

*/
-- 1. 조인을 이용한 경우
-- 대부분의 개발자들이 이렇게 처리를 하십시다.
SELECT productname, sum(od.unitprice) sumprice
FROM dbo.[order details] od join dbo.orders o
    ON o.orderid = od.orderid join dbo.products p
    ON od.productid = p.productid
WHERE orderdate < '19961231'
GROUP BY productname



-- 2. 파생 테이블을 이용한 경우, 비교를 해 보시죠.
SELECT productname, sumprice
FROM dbo.products p
    JOIN (SELECT productid, sum(od.unitprice) sumprice
        FROM dbo.[order details] od join dbo.orders o
         ON od.orderid = o.orderid
        WHERE orderdate < '19961231'
        GROUP BY productid) od
ON od.productid = p.productid
ORDER BY productname


/*
성능 비교   

아래 세션 옵션(STATISTICS IO)을 ON으로 설정하신 뒤에 위의
두 명령을 쿼리 분석기에서 다시 실행하고 나면 아래와 같은 결과를 추가적으로
보여줍니다.
만일, 쿼리 분석기에서 실행 계획을 보고 평가하실 수 있는 분이라면 실행
계획도 함께 확인하시면 훨씬 도움이 되실겁니다.(Ctrl-K)

STATISTICS IO 설명
이 옵션을 ON으로 설정한 뒤, SQL을 실행하시면 쿼리 분석기의 결과 창에
실행 SQL에 의해 발생된 각 테이블의 페이지(데이터 페이지&인덱스 페잊) IO 횟수,
테이블 스캔 횟수 및 기타 정보를 보실 수가 있습니다.

SQL 성능 측정을 위한 기본적인 방법입니다.

자, 그럼 비교를 해 보시죠.
*/
SET STATISTICS IO ON

-- 1. 조인을 이용한 SQL을 실행한 경우의, 디스크 처리 통계 정보

-- 'Products' 테이블. 스캔 수 401, 논리적 읽기 수 802, 물리적 읽기 수 0, 미리 읽
기 수 0.
-- 'Order Details' 테이블. 스캔 수 151, 논리적 읽기 수 303, 물리적 읽기 수 0, 미
리 읽기 수 0.
-- 'Orders' 테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0.


-- 2. 파생 테이블을 이용한 SQL을 실행 한 경우의, 디스크 처리 통계 정보

-- 'Products' 테이블. 스캔 수 74, 논리적 읽기 수 148, 물리적 읽기 수 0, 미리 읽기
수 0.
-- 'Order Details' 테이블. 스캔 수 151, 논리적 읽기 수 303, 물리적 읽기 수 0, 미
리 읽기 수 0.
-- 'Orders' 테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0.


/*
2가지 IO 비교에서
'Products' 테이블에 대한 논리적 읽기 수를 비교해 보십시오.

1. 스캔 수:401, 논리적 읽기 수:802
2. 스캔 수:74, 논리적 읽기 수:148

상당한 차이의 Page IO를 보실 수가 있습니다.
예제에서 사용된 테이블의 레코드 수는 각각 Products(77), Orders(830),
Order Details(2155) 건 밖에 되지 않습니다.

대량의 데이터를 가진 테이블의 경우를 상상해 보십시오.


-----------------------------------------------------------------------
    Horizontal Partitioning(=Partitioned View)
-----------------------------------------------------------------------
SQL Server 2000에 새로 추가된 Enterprise 기능 중에 한 가지가 바로
Distributed Partitioned View(분산 분할 뷰) 기능입니다.
데이터 베이스 층에서 데이터 로드 밸런싱을 통한 확장성을 구현할 수 있는
기능입니다.

이 분할 뷰를 파생 테이블을 형식으로도 구성을 하실 수가 있습니다.
분산 분할 뷰에 대해서는 기회가 닿는 대로 소개를 하도록 하겠습니다.


-----------------------------------------------------------------------
    Inline Table-value 함수도 동일한 기능을 수행
-----------------------------------------------------------------------
SQL Server 2000에서 또 다른 추가 기능이 바로 UDF(사용자 정의 함수) 기능이죠.
UDF는 세 가지 형식으로 작성하실 수가 있습니다.

1. Scalar 함수
2. Table-value 함수 중
    2-1) Inline
    2-2) Multi Statement

입니다.
이 중 Inline Table-value 형식의 함수는
일반적인 뷰, 파생테이블과 동일하게 처리 방법으로 사용이 됩니다.


-----------------------------------------------------------------------
    마무리.
-----------------------------------------------------------------------
파생 테이블에 대한 사례는 많이 있습니다.
너무 부담을 드릴 수는 없는 관계로 몇 가지 사례만을 소개 했습니다.
집합 개념의 대한 정립. 파생 테이블 활용에서부터 시작하십시오.
변환된 모습을 느끼실 수 있으실 겁니다.


이번 강좌도 여러분들에게 많은 도움이 되셨으면 하는 바램을 가져 봅니다.
벌써 새벽 4시군요, 오늘은 토요일, 즐거운 주말 되십시오.

1. 트랜잭션 로그 Truncate
현재 트랜잭션 로그 백업을 받지 않는다면 다음의 명령을 수행해 log를 truncate합니다.
backup log [DB_NAME] with TRUNCATE_ONLY
go

2. LDF 파일 축소
DB의 LDF파일에 대한 이름을 확인한 후 DBCC SHRINKFILE 명령으로 원하는 SIZE만큼
줄입니다.
-- LDF 파일에 대한 logical name 확인
sp_helpdb [DB_NAME]
예를 들면 pubs가 디비명인 경우 pubs_log가 논리적 이름이 됩니다.

-- 아래 100은 MB단위로 target size입니다. (즉, 파일 size를 100MB로 줄임)
DBCC SHRINKFILE (pubs_log, 100)
GO

LDF 파일을 축소하는 작업은 운영중에도 가능하지만, 사용자가 적은 시간에 동작시키는
것이 현명할 듯 하네요~ 데이터베이스 복구 메델이 "단순"이 아니라면 정기적으로 트랜
잭션 로그를 백업 받아 주셔야 LDF파일이 무한정 커지는 것을 방지할 수 있습니다.

BACKUP LOG DB_NAME  WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE ( DB_NAME , TRUNCATEONLY)