반응형

쿼리 유형별 트랜잭션 로그의 증가 수치를 설명하고, 

트랜잭션 로그 축소 쿼리를 설명 드리겠습니다.


▷TEST 장비 : i7 LG gram NoteBook, RAM: 24 G, SSD

▷TEST DB 정보 : 로그파일 10MB 단위로 증가


▷TEST TABLE 구조


▷ 작업 유형별 로그파일 증가량 : 각 테스트 전 로그파일을 기본 24MB로 축소

 건수

 작업 방식

 LOG 파일 증가량

 100만건 insert

 WHILE 을 이용하여 1건씩 100만번 INSERT

 24MB ⇒ 284MB

 집합 복제를 통하여 100만건을 한번에 INSERT

24MB ⇒ 524MB

 100만건 update

 WHILE을 이용하여 1000건씩 1000번 UPDATE

24MB ⇒ 94MB

 한번에 100만건 UPDATE

 24MB ⇒ 454MB

▷ 결론

1. 로그파일 증가를 최소화 하고 싶다면 INSERT, UPDATE, DELETE를 WHILE을 사용하여 나눠서 실시 → 단, 작업 시간은 증가


▷ 로그파일 축소 : 

    - DB가 사용중(update, select가 실행되고 있는 중) 에 로그파일 축소가 가능합니다.

      하지만 가급적 점검시 사용하는 것이 좋겠습니다.

1
2
3
4
5
6
7
8
9
10
11
12
ALTER DATABASE test_db  --test_db : DB명
SET RECOVERY SIMPLE;
GO
 
-- 로그파일을 10MB로 축소
-- DB 속성의 처음크기 이하로는 줄일 수 없음
/*
DB 속성의 처음크기를 너무 크게 잡은 경우, 해당 값을 500MB 정도로
줄인 후 로그 축소를 해야 함
*/
DBCC SHRINKFILE (TEST_DB_log, 10);  --TEST_DB_log : LOG 파일 명
GO
 
ALTER DATABASE test_db   --test_db : DB명
SET RECOVERY FULL;
GO

cs


반응형

'Database & Data > MSSQL' 카테고리의 다른 글

SP_LOCK 출력 결과의 정렬 순서 개선 (Mode DESC)  (0) 2019.01.01
MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05
Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05
MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
반응형

JOIN 별 결과 집합을 도식화한 자료 입니다. 어디서 구했는지 생각나질 않아 링크는 못 걸어 드립니다.




반응형

'Database & Data' 카테고리의 다른 글

상황에 맞는 그래프 선  (0) 2023.01.02
LabVIEW 와 DB연동을 위한 전반적인 내용 입니다.  (0) 2020.04.20
일련 번호 중 빠진 번호 조회  (0) 2017.09.18
세미나 동영상 [최적 데이터 구조 설계]-다운 가능  (0) 2015.09.20
ER Studio Relationship 관계선 종류  (0) 2015.08.14
반응형

아래와 같이 Row 구분자 = "-" , Column 구분자 = "/" 인 문자열을

테이블 형식으로 변환해 보겠습니다.

EX) '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701'


>> Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DECLARE @V_STRING VARCHAR(3000);
SET @V_STRING = '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701';
 
WITH TA AS
(
    SELECT 1 LV
    UNION ALL
    SELECT LV + 1 LV FROM TA WHERE LV + 1 <= 4
) ,
TB AS
(
    SELECT 1 RNUM, dbo.regexp_substr(@V_STRING, '_', 1, 1) TXT
    UNION ALL
    SELECT RNUM + 1 RNUM, dbo.regexp_substr(@V_STRING , '_', 1, RNUM + 1) TXT
    FROM TB
    WHERE RNUM + 1 <= LEN(dbo.fn_StripCharacters(@V_STRING, '^_+'))+1
)
SELECT TC.RNUM
    , MAX(CASE WHEN TC.LV = 1 THEN TC.TXT ELSE NULL END) COL1
    , MAX(CASE WHEN TC.LV = 2 THEN TC.TXT ELSE NULL END) COL2
    , MAX(CASE WHEN TC.LV = 3 THEN TC.TXT ELSE NULL END) COL3
    , MAX(CASE WHEN TC.LV = 4 THEN TC.TXT ELSE NULL END) COL4
FROM (
    SELECT TB.RNUM, TA.LV, dbo.regexp_substr(TB.TXT, '/', 1, TA.LV) TXT
    FROM TB,TA
) TC
GROUP BY TC.RNUM
ORDER BY TC.RNUM ASC
cs


>> 결과 집합


>> 필요 함수

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
/*
    원본 문자열에서 특정 문자열을 제거
*/
CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String                NVARCHAR(MAX), 
    @MatchExpression    VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'
 
    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
 
    RETURN @String
 
END
GO
 
 
/*
    원본 문자열에서 특정 문자열 부분을 추출
*/
CREATE  FUNCTION [dbo].[REGEXP_SUBSTR] 
(
    @i_str varchar(1000), 
    @i_delimeter varchar(10), 
    @i_position int,
    @i_index int
) RETURNS varchar(2000)
AS BEGIN
 
    DECLARE @o_str varchar(2000); 
    SET @o_str = '';
 
    WITH t AS (
        SELECT 1 as lvl, CONVERT(VARCHAR(2000), @i_str) AS code
        UNION ALL
        SELECT lvl + 1 as lvl
            , CONVERT(VARCHAR(2000), SUBSTRING(code, CHARINDEX(@i_delimeter, code, 1) + 1, 99) ) AS code
        FROM t
        WHERE CHARINDEX(@i_delimeter, code, 1) > 0
    )
 
    SELECT @o_str = CONVERT(VARCHAR(2000), LEFT(code, CHARINDEX(@i_delimeter, code + @i_delimeter, 1) - 1))
    FROM t
    WHERE lvl = @i_index        
 
    RETURN @o_str
 
END
GO
 
cs


반응형

'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL- 복제 관련 조회 쿼리  (0) 2018.10.28
MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05
MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
반응형

>> ORACLE 의 connect by level  구문을 MSSQL 로 변환

Oracle Query

 MSSQL Query  (MSSQL 2005 이상)

쿼리 결과
1
2
3
SELECT rownum RNUM
FROM DUAL
CONNECT BY LEVEL <= 5;
cs
1
2
3
4
5
6
7
WITH TA AS
(
    SELECT 1 LV
    UNION ALL
    SELECT LV + 1 LV FROM TA WHERE LV + 1 <= 5
)
SELECT * FROM TA
cs


>> 100 개 이상 출력시 오류 발생


>> option(maxrecursion 0) 구문을 추가하여 최대 재귀 횟수 제한 해제

1
2
3
4
5
6
7
8
WITH TA AS
(
    SELECT 1 lv
    UNION ALL
    SELECT lv + 1 FROM TA WHERE lv + 1 <= 200
)
SELECT * FROM TA option(maxrecursion 0)
;
cs

>> 100 개 이상 표시 출력 됨

 


반응형

'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL 트랜잭션 로그 축소  (0) 2018.08.12
MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05
MSSQL-연속 출석일 수 조회 쿼리  (0) 2018.07.24
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
MSSQL 두개의 SP 간 임시테이블 공유하기  (0) 2016.09.24
반응형

LOGIN_LOG 테이블에서 연속 출석일수를 조회하는 쿼리 입니다. 

≫ 테이블 구조

  - 특정 기간 동안의 유저 로그인 기록 입니다. 일자별로 여러개 row가 있을 수 있는데, 

    우리가 원하는 것은 연속 출석일 수 를 구하는 것이기 때문에 유저별,일자별 그룹핑 하였습니다.

  - 결과 테이블 설명

1. 37번 유저 : 19일, 20일, 21일, 22일 연속해서 로그인 하였기 때문에 연속 출석일 수 = 4일

2. 38번 유저 : 6일, 7일 연속 출석일 수 = 2, 26일, 27일, 28일 연속 출석일 수 = 3일 

   로 계산 되었습니다.

 기반 테이블

 연속 출석일 수 계산 결과

 

 


≫ Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DECLARE @v_start_date datetime, @v_end_date datetime;
SET @v_start_date = '2018-02-01 00:00:00';
SET @v_end_date = '2018-02-28 23:59:59';
 
with tmp_user_login_list as (
    select ta.usn, ta.login_date
    from (
        select usn, convert(char(10),login_date,23) login_date
        from TBL_USER_CONNECTION_LOG where LOGIN_DATE between @v_start_date and @v_end_date
    ) ta
    group by ta.usn, ta.login_date
    --order by ta.usn, ta.login_date asc
),
tmp_user_min_login_date as (
    select usn, min(convert(char(10),login_date,23)) start_login_date
    from TBL_USER_CONNECTION_LOG where LOGIN_DATE between @v_start_date and @v_end_date
    group by usn
)
select usn, MIN(login_date) std_dt, MAX(login_date) end_dt , COUNT(*) cnt
from ( 
        select  ta.usn, login_date
        ,   ROW_NUMBER() over (order by  ta.usn, login_date) idx        -- 자료의 일자별 순서
        ,   DATEDIFF( dd, login_date, tb.start_login_date )  no            -- 유저의 해당 기간 최초 접속 일
        from  tmp_user_login_list ta
        inner join tmp_user_min_login_date tb on ta.USN = tb.USN
)  r
group by usn, idx + no
having count(*) > 1        -- 연속 로그인 일수가 1일 이상인 자료
order by usn, idx + no  desc
cs





반응형

'Database & Data > MSSQL' 카테고리의 다른 글

MSSQL 문자열을 파싱하여 테이블 구조로 변환  (0) 2018.08.05
Oracle 의 CONNECT BY LEVEL 을 MSSQL 로 변환  (0) 2018.08.05
MSSQL - xml 문자열로 DB 저장하기  (1) 2016.09.25
MSSQL 두개의 SP 간 임시테이블 공유하기  (0) 2016.09.24
MSSQL dm_exec_query_stats DMV 테스트  (0) 2016.07.05
반응형

오라클

아래와 같이 구분자로 구분된 여러 항목이 들어 있는 문자열을 파싱하여 표 형태로 출력해 주는 쿼리 입니다.

EX) '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701'

Row 구분자 :  "_"

Column 구분자 : "/"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
  SELECT TD.RNUM
  , MAX(CASE WHEN TD.LV = 1 THEN TD.TXT ELSE NULL END) COL1
  , MAX(CASE WHEN TD.LV = 2 THEN TD.TXT ELSE NULL END) COL2
  , MAX(CASE WHEN TD.LV = 3 THEN TD.TXT ELSE NULL END) COL3
  , MAX(CASE WHEN TD.LV = 4 THEN TD.TXT ELSE NULL END) COL4
  FROM (
  SELECT TB.RNUM, TC.LV ,regexp_substr(TB.TXT, '[^/]+', 1, TC.LV) TXT
  FROM (
  SELECT rownum RNUM,regexp_substr(TA.TXT, '[^_]+', 1, LEVEL) TXT
  FROM (SELECT '1/1/W/101_1/2/H/201_1/3/H/301_2/3/H/501_3/4/H/601_4/5/H/701' TXT FROM dual) TA
  CONNECT BY LEVEL <= length(regexp_replace(TA.TXT, '[^_]+',''))+1
  ) TB, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 4) TC ) TD
  GROUP BY TD.RNUM
  ORDER BY TD.RNUM ASC;
cs

▶ 출력 결과


기타 문의 사항은 덧글로 부탁 드립니다.


반응형

'Database & Data > ORACLE' 카테고리의 다른 글

하나의 테이블에서 Merge Into 실행  (0) 2020.03.21
Oracle-xml 문자열을 테이블에 저장하기 입니다.  (2) 2016.09.25

+ Recent posts

티스토리툴바