Challenge Everything

'전체 글'에 해당되는 글 678건

[펌] (T-SQL) 서버 측 동적 쿼리에서의 탈출

Development/DataBase 2011. 8. 13. 15:19

(T-SQL) 서버 측 동적 쿼리에서의 탈출

김정선

삼성 SDS 멀티캠퍼스

전임 강사

Microsoft SQL Server MVP

요약.

수년 전부터 MS 플랫폼에서 SQL Server를 연동한 프로젝트 수행 시 개발 방법론의 중요한 한 축으로 저장 프로시저의 적극적인 활용이 일반화되고 있다. 그로 인한 부작용 중에 하나는 이전에 응용 프로그램 아키텍처 상의 프레젠테이션 계층 혹은 비즈니스 서비스 계층에서 문자열 데이터로 동적으로 구성하던 쿼리가 저장 프로시저를 통해서 서버 측에서 구성이 된다는 것이다. “서버 측 동적 쿼리”(이하 동적 쿼리로 표기)라고 표현하는 이러한 동적 T-SQL 또는 일괄처리는 성능상의 문제를 내포함과 동시에, 개발 생산성과 관리 및 유지 보수의 용이성 측면에서 많은 고려 사항을 포함하고 있다. 그럼에도 아직 많은 개발자들이 막연히 문제 해결 중심적인 사고로 동적 쿼리를 선호하는 경향이 있다. 이 글에서는 동적 쿼리를 비 동적 쿼리(이하. 정적 쿼리로 표기)로 변환하거나 대체할 수 있는 기초적인 사례들을 살펴볼 것이다. 불필요한 동적 쿼리 사용으로 인한 성능상의 문제 해결과 관리 및 유지 보수 용이성을 얻는데 미약하나마 이 글이 도움이 되기를 바란다.

- 동적 쿼리

- 동적 쿼리 대 정적 쿼리(비 동적 쿼리)

- 탈출을 위한 시작, 사례별 예제

- 마치면서...

동적 쿼리

일반적으로 언급하는 동적 쿼리란, 코드의 실행 시점에 SQL 문이 동적으로 구성되고 실행되는 쿼리를 말한다. ODBC를 사용하는 C 언어 계열에서, SQL 문을 문자 배열에서 동적으로 구성한 뒤 이를 ODBC API, SQLPrepare 혹은 SQLExecDirect 함수로 전달하거나 ADO를 사용하는 Visual Basic, ASP 등에서 SQL 문을 문자열로 동적으로 구성한 뒤 이를 Connection 오브젝트나 Recordset 오브젝트를 통해서 호출하거나, 또는 다양한 .NET Data Provider를 제공하는 .NET 호환 언어에서 역시 문자열로 동적으로 구성한 SQL 문을 DataAdapter, DataReader 등의 오브젝트를 통해서 호출하는 경우가 이에 해당한다.

참고. SQL Server .NET Data Provider 의 경우엔 동적 쿼리를 호출하더라도 순수한 쿼리 형태로 호출되지 않고, SQL Server의 확장 저장 프로시저, sp_executesql 을 통해서 자동 변환이 된다. 이는 Ad hoc 쿼리와 Precompiled 쿼리(저장 프로시저, 트리거 등)의 중간 형태인, Parameterized 쿼리의 생성 방법 중의 하나로써 .NET 기반 데이터베이스 응용프로그램 개발자들이 기본적으로 숙지해야 할 시스템 프로시저 중의 하나입니다.

동적 쿼리를 나타내는 용어도 다양하다, 원시적으로는 Embedded SQL for C(ESQL/C) 에서 사용된 용어를 시작으로 플랫폼이나 개발 언어에 따라 Dynamic Query, Hard Coded Query, Ad hoc Query 등으로 불려지고 있다.

서버 측 동적 T-SQL 혹은 동적 일괄 처리(Batch)

이전의 C, C++, Visual Basic, ASP 등에서 동적 쿼리의 사용은 일반적이었다. 물론 여전히 많은 응용 프로그램과 프로젝트에서 동적 쿼리 형식이 사용되고 있다. 그러나 현재 기본적으로 권장하는 SQL Server 데이터베이스 응용 프로그램의 개발 방법론에서는 서버 측의 T-SQL 쿼리 오브젝트(저장 프로시저, 사용자-정의 함수, 트리거, 등)를 사용하는 것이다. 이는 수년 전 IIS 기반의 ASP를 사용한 웹 응용 프로그램의 개발이 일반화되면서 ASP 개발자들이 이를 수용, 프로젝트에 적용하기 시작했다 - 사실, 저장 프로시저의 적극적인 사용을 권장한 것은 꽤 오래된 이야기이지만 국내에서 활발하게 적용되기 시작한 것은 얼마 되지 않는다.

이러한 방법론을 통해 얻을 수 있는 이득이 많은 반면, 피하기 어려운 문제점 또한 가지고 있었다. 그 중에 하나가 바로 서버 측에서의 동적 쿼리의 필요성이었다. 런타임 시 쿼리를 문자열 상수와 변수로 구성하고, 이를 통해 완성된 최종 쿼리를 호출하던 이전의 방식에서는 런타임 시 조건에 따라 다양한 쿼리를 구성할 수 있는 편리함이 제공된 반면 저장 프로시저, 트리거 등에서 런타임 시 쿼리를 동적으로 변경하는 것은 상대적으로 쉬운 문제가 아니었다. 가장 흔히 접하게 되는 몇 가지 예제를 보자:

- SELECT @컬럼명 FROM table

- SELECT * FROM table WHERE @컬럼명 LIKE @표현식

- SELECT * FROM table WHERE column IN (@표현식)

어떻게 해결 할까? SQL Server 2000 버전에서 저장 프로시저, 트리거, 혹은 스크립트 내에서 런타임 시에 쿼리를 동적으로 구성하기 위한 방법으로 다음 두 가지가 주로 사용된다.

- sp_executesql 확장 프로시저를 사용해서 유니코드 문자열을 실행. 동적 쿼리 구문 내에 매개변수를 선언하고 연동하는 것이 가능하며, 이를 통해 얻을 수 있는 추가 이득이 있다.

- EXECUTE 문을 사용해서 문자열을 실행.

참고. 하나 이상의 T-SQL 쿼리를 하나의 일괄처리(Batch) 범위 내에서 동적으로 구성할 수 있기 때문에 동적 T-SQL 혹은 동적 일괄처리라고 부르는 것이 보다 가까운 표현이 될 것이며, 온라인 설명서에도 이와 같이 언급하고 있다.

저장 프로시저 등에서 동적 쿼리 구성은 SQL Server 관련된 온라인 커뮤니티와 뉴스그룹 등에서 가장 활발한 질문/답변 유형 중의 하나이다, 두드러진 변화는 대략 1년 전부터인 것으로 기억이 난다. 그 만큼 많은 코드에서 적용되고 있다는 것을 암시하지만, 문제는 이러한 접근 방법이 또 다른 문제를 야기한다는 것이며, 그 사실을 잘 알지 못한 채 불필요한 상황에서까지 과도하게 사용하고 있다는 것이 더욱 큰 문제에 해당한다. 저장 프로시저 등을 사용해서 얻을 수 있는 성능 상의 이득을 반감시키고, 보안 위험, 관리 상의 어려움 등 여러 가지 문제가 유발된다.

동적 쿼리 대 정적 쿼리(비 동적 쿼리)

응용 프로그램 아키텍처 상에서 적용되는 계층에 관계없이, 동적 쿼리의 과용은 여러 가지 문제점들을 가지고 있다. 기본적으로 실행 시마다 컴파일을 반복하게 됨으로써 PreCompiled/Procedure Cache 재사용을 저해함으로써 발생하는 성능 상의 문제(여기서, SQL Server의 Auto Parameterization/Prepared Cache 재사용성에 대한 특징은 따로 언급을 하지 않을 것이다.)를 포함해서, Ownership Chain 권한 처리 문제와 SQL Injection (문자열 쿼리 내에 또 다른 쿼리를 삽입해서 의도하지 않는 동작을 유도하는 방식)등에 관련된 보안 상의 문제, 공통 모듈에 대한 관리 및 배포에 대한 어려움 등을 예로 들 수 있다. 이는 이미 오래 전부터 많은 전문가들에 의해 잘 알려진 사실 들이다.

참고. 이에 관련된 좋은 문서가 하나 있다, Microsoft SQL Server MVP 이기도 한 Erland sommarskog 가 쓴 글, “The curse and blessings of dynamic SQL” 을 읽어보기 바란다. 그리고 이 글은 한글 버전도 제공된다. 또한 그의 다른 좋은 글 들도 도움이 될 것이다.

동적 쿼리가 가지고 있는 여러 가지 문제점은, 저장 프로시저를 사용하도록 권장하는 이유들과 자연스럽게 연결된다. 이제는 일상적인 논쟁거리로, SQL Server 개발자들의 술자리 메뉴 중의 하나가 되었지만, “동적 쿼리에서의 탈출”이라는 사뭇 거창한 주제를 강조하기 위해서라도 다시 한 번 저장 프로시저를 사용하는 이유를 간단하게 정리해 보자:

- 실행 계획 Caching 을 통한, 성능 이득

- Network Traffic 최소화

- 출력 Parameter, Return 값 사용

- Ownership Chain 을 통한 권한 처리, SQL Injection 차단 등의 보안 기능

- 업무 논리의 캡슐화, 모듈화

- SQLXML 3.0 이후 릴리스에서 XML WebService 노출 기능

- 이 외에도 적용 방법과 구성에 따라 추가 이득을 얻을 수 있다.

저장 프로시저가 정답은 아니다. 또한 동적 쿼리를 무조건 사용하지 말라는 것도 아니다. 이들은 모두 우리가 얻을 수 있는 해답의 한 가지일 뿐이며, 무엇이 가장 정답에 가까운 해답인지에 따라서 그 이득의 유무와 정도가 달라는 것이다. 어느 것이 정답에 가까운지를 판단하기에 앞서 어떤 해답이 있는지를 알고 있는 것이 기본일 것이다 ? 여러분은 주관식과 객관식 중 어느 것을 좋아하는가?

동적 쿼리가 가지고 있는 문제점을 저장 프로시저 안에 재현하는 것은, 결국 위에서 언급한 저장 프로시저 사용에 대한 이득을 저해하는 요소가 되는 것이다. 현재 저장 프로시저의 적지 않은 코드가 동적 쿼리 형태로 작성되고 있으며, 그들 중의 상당 부분은 정적 쿼리로 재 작성할 수가 있다. 즉, 동적 쿼리를 불필요하게 남발하고 있는 코드가 많다는 것이다. 이는 객관식 보기가 충분하지 않다는 것이기도 하다. 다음에서 이러한 사례들을 살펴볼 것이며, 여러분이 가지고 있는 객관식 보기 유형에 추가하길 바란다. 이를 통해 SQL Server 저장 프로시저가 제공하는 본래의 이득에 충실한 코드를 개발하는데 도움이 될 것이다.

탈출을 위한 시작, 사례별 예제

서버 측 동적 T-SQL을 정적 쿼리로 변환하는 방법에 대한 다양한 사례가 SQL Server 전문 웹사이트, 뉴스그룹 등을 통해서 알려져 왔으면 지금도 다양한 사례가 논의되고 개발되고 있다. 여기서 기초적인 몇 가지 사례를 살펴보자. 여기에 기록한 사례들은 주로, Robert Marda 가 쓴 글, “How Dynamic SQL Can Be Static SQL”, 그리고 Erland sommarskog 의 다른 글 및 뉴스그룹 등의 자료 들을 정리한 것이다.

예제에 대한 자세한 설명은 제외했다. 이면의 부족함도 있지만, 대신 동적 쿼리 형식과 이를 대체한 정적 쿼리를 비교해서 기록했으므로, 코드를 보는 것으로도 충분히 설명을 대체할 수 있을 것이라 본다. 그리고, 예제는 모두 SQL Server의 Northwind 데이터베이스를 사용한다.

- 예제 1. 동적 WHERE 조건자 컬럼

동적 쿼리 형식

SELECT * FROM table WHERE @컬럼명 = @표현식

정적 쿼리

DECLARE @column varchar(10), @value varchar(20)

SET @Column = 'Last'

SET @value = 'Full%'

SELECT *

FROM dbo.employees

WHERE (CASE @column WHEN 'Last' THEN LastName

WHEN 'First' THEN FirstName

WHEN 'Title' THEN Title

ELSE @value

END) LIKE @value

- 예제 2. 동적 SELECT 절

동적 쿼리 형식

SELECT

{ @컬럼1, @컬럼2, @컬럼3

| @컬럼4, @컬럼1, @컬럼5

| , ... }

FROM table

정적 쿼리

DECLARE @column varchar(10)

SET @Column = 'title'

SELECT EmployeeID,

CASE @column

WHEN 'Name' THEN LastName

WHEN 'Title' THEN Title

ELSE LastName

END AS Column1,

CASE @column

WHEN 'Name' THEN FirstName

WHEN 'Title' THEN LastName

ELSE CAST(BirthDate as varchar(20))

END AS Column2,

CASE @column

WHEN 'Title' THEN CAST(HireDate as varchar(20))

ELSE ''

END AS Column3

FROM dbo.employees

WHERE EmployeeID < 4

- 예제 3. 동적 Order By 절 (컬럼명 만 유동적인 경우)

동적 쿼리 형식

SELECT * FROM table

ORDER BY @컬럼명

정적 쿼리

DECLARE @OrderBy varchar(10)

SET @OrderBy = 'LastName'

SELECT *

FROM dbo.employees

WHERE EmployeeID < 4

ORDER BY

(CASE @OrderBy

WHEN 'LastName' THEN LastName

WHEN 'Title' THEN Title

END) ASC

- 예제 4. 동적 정렬 기준 ( 정렬 기준 & 컬럼명 모두 유동적인 경우 )

동적 쿼리 형식

SELECT * FROM table

ORDER BY @컬럼명 @정렬기준

정적 쿼리

DECLARE @OrderBy varchar(10), @Sequence varchar(4)

SET @OrderBy = 'LastName'

SET @Sequence = 'DESC'

SELECT *

FROM dbo.employees

WHERE EmployeeID < 4

ORDER BY

(CASE @Sequence

WHEN 'ASC' THEN CASE @OrderBy

WHEN 'LastName' THEN LastName

WHEN 'Title' THEN Title END

END) ASC,

(CASE @Sequence

WHEN 'DESC' THEN CASE @OrderBy

WHEN 'LastName' THEN LastName

WHEN 'Title' THEN Title END

END) DESC

- 예제 5. 동적 GROUP BY, HAVING

동적 쿼리 형식

SELECT @컬럼명

FROM table

GROUP BY @컬럼명

HAVING @HAVING-조건식

정적 쿼리

DECLARE @column varchar(10), @ActivateCount bit, @Count int

SET @Column = 'name' -- Group By 기준 컬럼

SET @ActivateCount = 1-- Having 절에 적용될 컬럼

SET @Count = 4 -- Having 절에 비교될 값

SELECT

CASE @column

WHEN 'Name' THEN 'Number of Employees'

WHEN 'Title' THEN 'Number of Titles'

END AS Type,

CASE @column

WHEN 'Name' THEN COUNT(employeeID)

WHEN 'Title' THEN COUNT(Title)

END AS QTY,

CASE @column

WHEN 'Name' THEN Country

WHEN 'Title' THEN Title

END AS GroupBy

FROM dbo.employees

GROUP BY (CASE @column

WHEN 'Name' THEN Country

WHEN 'Title' THEN Title

END)

HAVING (CASE @column + LTRIM(STR(@ActivateCount))

WHEN 'Name1' THEN COUNT(employeeID)

WHEN 'Title1' THEN COUNT(Title)

ELSE @Count + 1

END) > @Count

- 예제 6. 동적 조인 컬럼 ( 조인 컬럼을 동적으로 가져가는 경우)

동적 쿼리 형식

SELECT *

FROM table1

INNER JOIN table2 ON @테이블.컬럼명 = table2.Column

INNER JOIN table3 ON ...

정적 쿼리

-- 테스트를 위한 임시 테이블

CREATE TABLE #RegionSupervisors (City varchar(20), employeeID int)

INSERT INTO #RegionSupervisors (City, EmployeeID)

SELECT 'Bern', 1 UNION SELECT 'Geneve', 1

UNION SELECT 'Koln', 1 UNION SELECT ' Albuquerque ', 5

UNION SELECT 'Seattle', 5 UNION SELECT ' Redmond ', 5

UNION SELECT 'Kirkland', 5 UNION SELECT ' London ', 4

UNION SELECT 'Cowes', 4 UNION SELECT 'Colchester', 4

-- @supervisors에 따라서, 조인 컬럼을 변경하는 경우

DECLARE @supervisors varchar(10)

SET @Supervisors = 'employee'

SELECT CustomerID, OrderDate, ShipCity, e.LastName AS [Assigned To]

, e.City AS [Employee Home Office]

, CASE @Supervisors

WHEN 'employee' THEN 'employee supervisor: ' + se.LastName

WHEN 'order' THEN 'order supervisor: ' + se.LastName END AS [Region Supervisor]

FROM dbo.Orders o

INNER JOIN dbo.Employees e ON o.EmployeeID = e.EmployeeID

INNER JOIN dbo.#RegionSupervisors s ON (CASE @Supervisors

WHEN 'employee' THEN e.City

WHEN 'order' THEN o.ShipCity END) = s.City

INNER JOIN dbo.Employees se ON se.EmployeeID = s.EmployeeID

- 예제 7. 동적 비교 연산자 ( 대/소 비교를 동적으로 가져가는 경우 )

동적 쿼리 형식

SELECT * FROM table

WHERE column @비교연산자 @표현식

정적 쿼리

DECLARE @Sign char(1), @date datetime

SET @Sign = '>'

SET @date = '19630703'

-- 1-1.

SELECT *

FROM dbo.employees

WHERE

(CASE @Sign

WHEN '>' THEN BirthDate

WHEN '<' THEN @date END) > (CASE @Sign WHEN '>' THEN @date

WHEN '<' THEN BirthDate END)

- 예제 8. 동적 조건자 ( 조건자 자체를 동적으로 구성하는 경우 )

동적 쿼리 형식

SELECT * FROM table

WHERE @조건자1 AND @조건자2 , ...

정적 쿼리

DECLARE @LastName varchar(30), @FirstName varchar(30)

SET @LastName = 'd'

SET @FirstName = 'a'

SELECT *

FROM dbo.employees

WHERE EmployeeID < 4

WHERE

(CASE WHEN @LastName <> '' THEN LastName

WHEN @LastName = '' THEN 'Eliminate' END) Like

(CASE WHEN @LastName <> '' THEN @LastName + '%'

WHEN @LastName = '' THEN 'Eliminate' END)

AND

(CASE WHEN @FirstName <> '' THEN FirstName

WHEN @FirstName = '' THEN 'Eliminate' END) Like

(CASE WHEN @FirstName <> '' THEN @FirstName + '%'

WHEN @FirstName = '' THEN 'Eliminate' END)

- 예제 9. 동적 계단식 조건자 ( 다양한 조건식을 계단식으로 구성하는 경우 )

동적 쿼리 형식

SELECT * FROM table

WHERE @조건자1 [ AND @조건자2 [ AND @조건자3 [, ... ] ] ]

정적 쿼리

DECLARE @LastName varchar(30), @FirstName varchar(30), @Country varchar(5)

, @City varchar(20), @Title varchar(30)

SET @Country = 'usa '

SET @ City = 'k'

SET @LastName = ''

SET @FirstName = ''

SET @Title = 's'

-- 최종 결과 확인용 쿼리.

SELECT *

FROM dbo.employees

WHERE Title LIKE @Title + '%'

AND Country LIKE @Country

AND City LIKE '%' + @City + '%'

-- 1. Tilte 존재 시 -> Country 존재 시 -> City 존재 시

SELECT *

FROM dbo.employees

WHERE

CASE WHEN @Title <> '' THEN Title

WHEN @Country <> '' THEN Country

WHEN @City <> '' THEN City

WHEN @LastName <> '' THEN LastName

WHEN @FirstName <> '' THEN FirstName END LIKE

CASE WHEN @Title <> '' THEN @Title + '%'

WHEN @Country <> '' THEN @Country

WHEN @City <> '' THEN '%' + @City + '%'

WHEN @LastName <> '' THEN @LastName + '%'

WHEN @FirstName <> '' THEN @FirstName + '%' END

AND

CASE WHEN @Title <> '' THEN

CASE WHEN @Country = '' THEN 'Eliminate' ELSE Country END

WHEN @Country <> '' THEN

CASE WHEN @ City = '' THEN 'Eliminate' ELSE City END

WHEN @City <> '' THEN

CASE WHEN @LastName = '' THEN 'Eliminate' ELSE LastName END

WHEN @LastName <> '' THEN

CASE WHEN @FirstName = '' THEN 'Eliminate' ELSE FirstName END

WHEN @LastName = '' THEN 'Eliminate'

WHEN @FirstName = '' THEN 'Eliminate' END LIKE

CASE WHEN @Title <> '' THEN

CASE WHEN @Country = '' THEN 'Eliminate' ELSE @Country END

WHEN @Country <> '' THEN

CASE WHEN @ City = '' THEN 'Eliminate' ELSE @City + '%' END

WHEN @City <> '' THEN

CASE WHEN @LastName = '' THEN 'Eliminate'

ELSE '%' + @LastName + '%' END

WHEN @LastName <> '' THEN

CASE WHEN @FirstName = '' THEN 'Eliminate'

ELSE '%' + @FirstName + '%' END

WHEN @LastName = '' THEN 'Eliminate'

WHEN @FirstName = '' THEN 'Eliminate' END

AND

CASE WHEN @Title <> '' THEN City

WHEN @Country <> '' THEN LastName

WHEN @City <> '' THEN FirstName

WHEN @LastName = '' THEN 'Eliminate'

WHEN @LastName <> '' THEN 'Eliminate'

WHEN @FirstName <> '' THEN 'Eliminate' END LIKE

CASE WHEN @Title <> '' THEN '%' + @City + '%'

WHEN @Country <> '' THEN @LastName + '%'

WHEN @City <> '' THEN '%' + @FirstName + '%'

WHEN @LastName = '' THEN 'Eliminate'

WHEN @LastName <> '' THEN 'Eliminate'

WHEN @FirstName <> '' THEN 'Eliminate' END

- 예제 10. 동적 UPDATE

동적 쿼리 형식

UPDATE table

SET @컬럼명 = @표현식

정적 쿼리

DECLARE @colname varchar(128)

SET @colname = 'orderdate'

UPDATE dbo.orders

SET orderdate = CASE @colname WHEN 'orderdate' THEN getdate()

ELSE orderdate END,

requireddate = CASE @colname WHEN 'requireddate' THEN getdate()

ELSE requireddate END

WHERE orderid = 10248

'Development > DataBase' 카테고리의 다른 글

[펌] [저장프로시저]생성 (0)	2011.08.13
[펌] [저장프로시저]개요 (0)	2011.08.13
[본문스크랩] ANSI 표준 SQL 조인 (0)	2011.08.13
[본문스크랩] 데이터베이스 유지 관리 계획 마법사 (0)	2011.08.13
MSSQL int, bigint, smallint 및 tinyint, float 및 real, decimal 및 numeric (0)	2011.03.10

안정적인 DNS서비스 DNSEver

Posted by 키르히아이스

[본문스크랩] ANSI 표준 SQL 조인

Development/DataBase 2011. 8. 13. 15:19

SQL

ANSI 표준 SQL 조인

Jonathan Gennick

Oracle9i에서 새롭게 지원되는 SQL 조인으로 이미 잘 알려진 조인을 더욱 간편하게 사용할 수 있고 완벽한 포괄 조인(outer-join) 지원이 가능하게 되었습니다.

SQL/92 표준 조인 구문 지원은 Oracle9i에 새롭게 추가된 많은 기능 중 하나입니다. 이 지원을 통해 휴대용 애플리케이션 작성이 이전보다 훨씬 더 간편해지고 완벽한 포괄 조인이 가능하게 되었습니다.

Oracle9i 이전의 조인 구문

지금까지 Oracle 데이타베이스는 SQL/86에서 정의된 조인 구문을 지원했습니다. Listing 1은 구 표준 조인 구문의 예를 보여 줍니다.

Oracle9i가 발표되기 전 Oracle 데이타베이스는 독자적인 구문을 사용하여 포괄 조인을 지원했습니다. Listing 2 는 COURSE와 ENROLLMENT의 포괄 조인 예를 보여 줍니다.

WHERE 절에서 ENROLLMENT 테이블 열 이름 다음에 "(+)"를 사용하여 이 테이블을 조인에서 옵션 테이블로 표시한다는 점을 유의하십시오.

기존 조인 구문에는 문제가 존재했습니다. 복잡한 질의에서는 WHERE 절을 구문 분석하여, 질의 결과의 제한 사항과 조인 조건을 분리하는 것이 어려운 경우가 많았습니다. 그리고 프로그래머가 조인 조건을 지정하는 것을 간과하여 데카르트 곱이 나오는 경우가 가끔 있었습니다.

ANSI 표준 조인 구문

ANSI 표준 SQL 조인 구문은 몇 가지 새로운 키워드와 절을 Oracle9i에게 제공하여, SELECT 문의 FROM 절에서 조인을 완벽하게 지정할 수 있습니다. COURSE와 ENROLLMENT 테이블을 조인하는 문제를 살펴 보겠습니다. 기존 방식을 사용한다면, 다음과 같이 질의의 FROM 절에서 두 테이블을 표시하게 됩니다.

FROM courses c, enrollment e

그러나 새로운 ANSI 구문을 사용하면 쉼표(,) 없이 원하는 조인 타입을 명확하게 지정할 수 있습니다. COURSE와 ENROLLMENT 테이블 사이에 배타 조인을 수행하려면 다음과 같이 작성합니다.

FROM courses c 
   INNER JOIN enrollment e

Jonathan Gennick의 웹 사이트 http://gennick.com/에서는 더 다양한 Oracle 팁을 온라인 제공합니다.

배타 조인을 지정했으므로 그 조인의 조건을 지정해야 합니다. 다른 WHERE 절 제한 사항과 조인 조건을 뒤섞이게 하는 대신 FROM 절에 포함된 ON 절에서 조인 조건을 지정할 수 있습니다.

SELECT c.course_name, c.period,
  e.student_name
FROM course c INNER JOIN enrollment e
     ON c.course_name = e.course_name
        AND c.period = e.period;

기존 조인 구문에 익숙한 사람이라면 새로운 조인 구문에 익숙해지는 데 시간이 걸릴 수 있습니다. 그러나 새 구문은 몇 가지 장점을 제공하기 때문에 학습할 만한 가치가 있습니다.

모든 조인 정보는 한 장소에서 지정됩니다. 복잡한 WHERE 구문 이곳 저곳을 옮겨 다니며 조인 조건을 질의 결과의 제한 사항과 분리하기 위해 애쓸 필요가 없습니다.

조인 조건 지정을 "잊어버리는" 일은 없습니다. 예를 들어 배타 조인을 지정하면 Oracle9i 는 ON 절이나 기타 다른 절을 사용하여 조인 조건을 명확하게 지정하도록 요구합니다. 데카르트 곱이 필요하다면 이를 명확하게 표시해야 합니다.

전체 포괄 조인을 수행할 수 있습니다. 이는 기존 Oracle 데이타베이스 조인 구문에서는 불가능했던 것입니다.

"(+)" 사용을 빠뜨려 포괄 조인을 배타 조인으로 바꿔버리는 실수를 염려하지 않아도 됩니다.

조인 조건 지정하기

지금까지는 ON 절을 사용하여 조인 조건을 지정했었습니다.

SELECT c.course_name, c.period,
  e.student_name
FROM course c INNER JOIN enrollment e
     ON c.course_name = e.course_name
        AND c.period = e.period;

다양한 ANSI 표준 SQL 자료를 webstore.ansi.org에서 구입할 수 있습니다.

ON 절에서는 조인 조건으로 부울 식을 지정할 수 있습니다. 그러다 대부분의 조인은 동등 조인입니다. 동등 조인에서는 두 테이블의 관련 열을 비교하여 값이 같은지 알아 볼 수 있습니다. 따라서 두 테이블에서 각각 조인을 정의한 열이 같은 이름을 갖고 있다면, 구문이 더 간단해지고 질의가 더 명확하게 됩니다. ON 절을 사용하여 부울 조인 조건을 지정하는 대신, USING 절에서 조인 열을 지정할 수 있습니다. 다음과 같이 작성하는 대신

   ON c.course_name = e.course_name
     AND c.period = e.period;

간단하게 이렇게 작성합니다.

	  USING (course_name, period);

이 예에서 USING 절은 두 테이블의 행이 각 COURSE_NAME과 PERIOD 열에서 동일한 값을 가질 때 조인되도록 지정합니다. Listing 3은 USING 절을 사용하여 동등 조인을 수행하는 경우를 보여 줍니다. 여기에서는 SELECT 문장이 간단하고 이해하기 쉬운 편입니다. 그러나 USING 절을 사용하면 질의의 의미에 미묘한 영향을 미치게 됩니다. ON 절을 사용하여 조인을 작성하면 두 테이블의 모든 열을 사용할 수 있습니다. 따라서 두 테이블로부터 COURSE_NAME 열을 선택할 수 있습니다.

SELECT C.COURSE_NAME, E.COURSE_NAME

Oracle9i SQL Reference 는 조인에 대한 정보 및 사례가 수록되어 있습니다. 이 설명서를 온라인으로 이용하려면 otn.oracle.com/docs/를 방문하십시오.

USING 절이 사용된 문에서 이렇게 열 별칭을 사용하면 "invalid column name" 오류가 발생합니다. USING 절을 지정할 때 데이타베이스 엔진은 두 개의 COURSE_NAME 열을 병합하고, 결과에서는 한 열만 인식하게 됩니다. 이 열은 조인되는 테이블 어느 쪽과도 연관되지 않으므로, 별칭을 사용할 수 없습니다. 동등 조인의 정의에 따르면 질의 결과 각 행에서는 하나의 COURSE_NAME 값만 존재해야 하므로 이는 타당한 규칙입니다.

USING 절은 동등 조인을 간편하고 쉽게 표현할 수 있는 구문이며, ON 절보다는 더 이해하기 쉬운 질의 결과를 얻을 수 있다고 생각합니다. 그리고 언급하기가 다소 꺼려지고 그렇게 권장하고 싶지 않지만 또 다른 지름길이 있습니다. 바로 NATURAL 절입니다. NATURAL 절을 사용하여 NATURAL 조인을 작성할 수 있습니다. 이 조인은 특별한 타입의 동등 조인으로서, 조인 열이 두 테이블에서 같은 이름을 갖는 모든 열로 구성되어 있습니다. Listing 4에 그 예가 있습니다. 그러나 NATURAL 조인은 권장하고 싶지 않으며, 이는 ANSI 측 실수였다고 생각합니다. NATURAL 조인에서는 한 쪽 테이블에 다른 쪽 테이블과 우연히 이름이 일치하는 열을 추가하면 본의 아니게 NATURAL 조인을 변경하는 셈이 됩니다.

ANSI 스타일의 포괄 조인

새로운 ANSI 구문은 세 가지 타입의 포괄 조인을 인정합니다. 왼쪽 포괄 조인(left outer joins), 오른쪽 포괄 조인(right outer joins) 그리고 전체 포괄 조인입니다. 왼쪽 포괄 조인과 오른쪽 포괄 조인은 사실 동일합니다. 한 테이블의 모든 행이 다른 테이블에서 일치하는 행과 함께 포함됩니다. 왼쪽 포괄 조인과 오른쪽 포괄 조인의 유일한 차이점은 테이블이 나열되는 순서입니다. 다음 세 질의에서 첫번째는 기존 구문을 사용했으며 의미는 동일합니다.

SELECT c.course_name, c.period,
  e.student_name
FROM course c, enrollment e
     WHERE c.course_name = e.course_name(+)
         AND c.period = e.period(+);

SELECT c.course_name, c.period,
  e.student_name
FROM course c LEFT OUTER JOIN enrollment e
     ON c.course_name = e.course_name
        AND c.period = e.period;

SELECT c.course_name, c.period,
  e.student_name
FROM enrollment e RIGHT OUTER JOIN course c
     ON c.course_name = e.course_name
        AND c.period = e.period;

전체 포괄 조인은 새로운 기능으로서, 양쪽 테이블에서 모든 행을 반환합니다. 행들은 조인 열에서 서로 일치시키며, 대상 테이블에 일치시킬 값이 없는 행의 빈 열에는 널 값이 사용됩니다. Listing 5는 Oracle9i 이전 방식으로 전체 포괄 조인을 시뮬레이션한 것 과 Oracle9i 전체 포괄 조인을 보여 줍니다.

Listing 5출력 결과에서 Spanish I 과 U.S. History에서 COURSE 행은 ENROLLMENT 테이블에서 일치하는 행이 없습니다. 따라서 STUDENT_NAME 열에서 이 행은 널 값을 갖습니다. Sky Lynn의 German I 코스 행은 COURSE 테이블에서 일치하는 행이 없습니다. 그러나 COURSE_NAME과 PERIOD 열은 널 값이 아님에 유의하십시오. 이 열은 조인 열이며, USING 절에서 지정된 바 있습니다. 따라서 COURSE 테이블에서 해당하는 행이 없는 경우 Oracle9i 는 ENROLLMENT 테이블에서 해당 열의 값을 가져오게 됩니다. 동등 조인에서는 이런 방식이 타당합니다. 열의 값을 널로 두고 싶다면 ON 절을 사용하여 조인 조건을 지정하면 됩니다. Listing 6을 참고할 수 있습니다.

Listing 6에서는 ON 절이 사용되었기 때문에, Sky Lynn의 COURSE_NAME과 PERIOD 열은 널 값을 갖습니다.

다중 조인

다중 조인 조건을 지정하면 질의에서 두 개 이상의 테이블을 조인할 수 있습니다. 기본적으로 Oracle9i 는 왼쪽에서 오른쪽으로 조인을 처리합니다. 그러나 소괄호를 사용하여 그 순서를 제어할 수 있습니다. 다음 두 질의는 동일합니다.

SELECT course_name, period, student_name,
  s.grade_level

FROM course c INNER JOIN enrollment e
     USING (course_name, period)
     INNER JOIN student s USING (student_name);

SELECT course_name, period, student_name, 
  s.grade_level
FROM (course c INNER JOIN enrollment e
     USING (course_name, period))
     INNER JOIN student s USING (student_name);

첫번째 질의는 COURSE에서 ENROLLMENT로 조인을 수행한 다음 그 결과를 STUDENT에 조인합니다. 두 번째는 소괄호를 사용하여 조인 순서를 동일하게 지정한 것입니다.

결론

저는 새로운 조인 구문이 마음에 듭니다. 그리고 여러분도 모든 조인 질의에서 이 방식을 사용해보도록 권장합니다. 이 표준 방식을 사용하여 코드를 더 이동성 있게 그리고 읽기 쉽게 만들 수 있습니다. 또한 전체 포괄 조인이라는 새로운 기능을 활용할 수 있습니다. 궁극적으로는 실수를 범할 확률이 더 적어지게 됩니다.

Jonathan Gennick (jonathan@gennick.com) 은 풍부한 경험을 갖춘 Oracle DBA 이자 Oracle Certified Professional 로서, 새로운 Oracle 기술 탐구를 즐기는 전문가입니다. 그는 현재 저술 활동을 하고 있으며 최근 발표된 SQL*Loader: The Definitive Guide (O'Reilly & Associates, 2001)의 공동 저자이기도 합니다.

출처 : http://www.oracle.com/global/kr/magazine/webcolumns/2001/o61sql.html

'Development > DataBase' 카테고리의 다른 글

[펌] [저장프로시저]생성 (0)	2011.08.13
[펌] [저장프로시저]개요 (0)	2011.08.13
[펌] (T-SQL) 서버 측 동적 쿼리에서의 탈출 (0)	2011.08.13
[본문스크랩] 데이터베이스 유지 관리 계획 마법사 (0)	2011.08.13
MSSQL int, bigint, smallint 및 tinyint, float 및 real, decimal 및 numeric (0)	2011.03.10

안정적인 DNS서비스 DNSEver

Posted by 키르히아이스

[본문스크랩] 데이터베이스 유지 관리 계획 마법사

Development/DataBase 2011. 8. 13. 15:19

다음과 같은 관리 작업을 매일 새벽 2시에 수행되도록 하고 싶습니다.

o 매일 새벽 2시
- 페이지당 빈 공간 비율을 10%로 하여 인덱스 페이지 다시 구성
- 데이터베이스가 50MB를 넘으면 축소후 데이터 공간의 10%에 해당하는 빈 공간만 유지하게

o 매일 새벽 3시
- DBCC CHECKDB를 이용하여 데이터베이스 무결성 점검

o 매일 새벽 4시
- 데이터베이스를 백업하되 해당일에 백업받은 파일을 별도의 폴더를 만들어 보관

o 매 3시간 간격
- 트랜잭션 로그 백업

o 기타
- 위 모든 작업의 결과를 보고서로 기록

위와 같은 관리를 위한 작업이 필요한 경우 각각의 작업을 만들어 스케쥴을 걸어주면 됩니다. 하지만 SQL 서버의 [데이터베이스 유지 관리 계획 마법사]를 이용하여 아주 쉽게 위와 같은 작업을 설정 할 수 있습니다. 이번 강좌에서는 이 방법에 대하여 살펴보도록 하겠습니다.

1. 데이터베이스 유지 관리 계획 마법사

[데이터베이스 유지 관리 계획 마법사]는 다양한 관리 작업을 손쉽게 설정 할 수 있는 방법을 제공하는 유익한 마법사입니다. 이 마법사를 이용하면 여러가지 작업을 특정 시간에 수행되도록 설정 할 수 있는데, 인덱스 재생성 및 데이터베이스 무결성 검사, 데이터베이스 백업 및 로그 백업 등과 같은 작업을 이 마법사를 통하여 예정된 시간에 수행되도록 자동화 할 수 있습니다.

[그림 1]

위 [그림 1]에서 처럼 [데이터베이스 유지 관리 계획] 마법사를 이용해서 등록된 유지 계획은 [관리] 부분에 위치하고 있습니다. 위 [그림 1]에서는 아직 관리 계획이 하나도 만들어진 상태가 아님을 알 수 있습니다.

2. 데이터베이스 유지 관리 계획 만들기

위 [그림 1]의 빨간색 박스 안의 [데이터베이스 유지 관리 계획] 위에서 마우스 오른쪽 버튼을 눌러 표시도는 단축 메뉴에서 "새 유지 관리 계획(P)"을 선택하거나, [도구] - [마법사] 에서 [관리] 부분의 "데이터베이스 유지관리 계획 마법사"를 수행하시면 됩니다. 그럼 다음 [그림 2]와 같이 초기 화면이 표시됩니다.

[그림 2]

1) 데이터베이스 선택

위 [그림 2] 에서 [다음] 버튼을 누르면 아래 [그림 3]과 같이 데이터베이스를 선택하는 화면이 나타납니다. 네가지 선택방법이 주어지는데, 특정 데이터베이스만을 선택하고자 하는 경우는 "데이터베이스 지정" 을 선택 하신 후 원하는 대상 데이터베이스를 선택하면 됩니다. 이 강좌에서는 Pubs 데이터베이스를 선택하여 진행하도록 하겠습니다.

[그림 3]

2) 데이터 최적화 정보 업데이트

위 [그림 3] 에서 [다음] 버튼을 누르면 아래 [그림 4]와같이 데이터 최적화 정보 업데이트 설정 화면이 나타납니다. 기본값은 아무것도 선택되지 않은 상태입니다. [그림 4]의 경우는 페이지당 빈공간을 10%로 하여 데이터와 인덱스 페이지를 재구성하도록 설정했으며 데이터베이스 파일 공간제거도 설정한 상태입니다. 그리고 이러한 작업이 매일 새벽 2시에 수행되도록 일정을 설정한 상태입니다.

[그림 4]

3) 데이터베이스 무결성 검사

위 [그림 4] 에서 [다음] 버튼을 누르면 아래 [그림 5]와 같이 데이터베이스 무결성 검사 설정 화면이 나타납니다. 기본값은 아무것도 선택되지 않은 상태입니다. [그림 5]의 경우는 인덱스를 제외한 데이터베이스 무결성 검사를 매일 새벽 3시에 수행하도록 설정한 상태입니다.

[그림 5]

4) 데이터베이스 백업 계획

위 [그림 5] 에서 [다음] 버튼을 누르면 아래 [그림 6]과같이 데이터베이스 백업 지정 화면이 나타납니다. [그림 6]의 경우는 매일 새벽 4시에 데이터베이스 백업을 디스크에 받도록 지정한 것입니다. 이때 디스크에 저장되는 방식은 [다음] 버튼을 눌러 진행하면 설정하게 됩니다.

[그림 6]

5) 백업 디스크 디렉터리 지정

위 [그림 6] 에서 백업을 선택하지 않았다면 다음 [그림 7]은 표시되지 않게 됩니다. [그림 7]에서는 E:\Data 폴더에 백업을 받되 데이터베이스마다 하위 디렉토리를 만들게 하였습니다. 즉 E:\Data\Pubs 폴더가 생성되게 됩니다. 여러개의 데이터베이스를 백업받는 경우에 관리를 용이하게 합니다. 그리고 백업받은 파일은 4주가 지나면 지워지게 했습니다. 또한 백업 파일의 확장자는 BAK으로 지정하였습니다.

[그림 7]

6) 트랜잭션 로그 백업 계획

위 [그림 7] 에서 [다음] 버튼을 누르면 아래 [그림 8]과 같이 트랜잭션 로그 백업 계획 지정 화면이 나타납니다. [그림 8]의 경우는 매일 3시간 간격으로 로그를 백업 받도록 설정한 상태입니다.

[그림 8]

7) 트랜잭션 로그 백업 디스크 디렉터리 지정

위 [그림 8] 에서 트랜잭션 백업을 선택하지 않았다면 다음 [그림 9]는표시되지 않게 됩니다. 트랜잭션 백업 파일의 확장자는 TRN으로 지정하였습니다.

[그림 9]

8) 생셩할 보고서

위 [그림 9] 에서 [다음] 버튼을 누르면 아래 [그림 10]과 같이 생성할 보고서 지정 화면이 나타납니다. [그림 10]의 경우는 E:\Data\Log 폴더 안에 보고서를 만들도록 했습니다. 그리고 4주가 지난 보고서는 자동으로 삭제되도록 하였습니다.

[그림 10]

9) 유지 관리 계획

위 [그림 10] 에서 [다음] 버튼을 누르면 아래 [그림 11]과 같이 유지 관리 계획 기록 지정 화면이 나타납니다.

[그림 11]

10) 완료

위 [그림 11] 에서 [다음] 버튼을 누르면 아래 [그림 12]와 같이 최종 확인 화면이 표시됩니다. 이 화면에서는 "계획 이름"에 적합한 이름을 지정해 주시면 됩니다. 이 이름이 위 [그림 1]의 우측 화면에 표시되게 됩니다. 설정이 잘못된 경우는 [뒤로] 버튼을 눌러 특정 부분의 설정을 다시 지정 할 수 있습니다. [마침] 버튼을 누르면 유지 계획 설정이 완료됩니다.

[그림 12]

다음 [그림 13]은 완료된 후의 모습니다.

[그림 13]

해당 유지 관리 계획은 언제 든지 수정이 가능합니다.

그리고 다음 [그림 14]는 위의 과정에 의해 자동으로 생성된 작업(Job)이 SQL 서버 에이젼트에 등록된 것을 보여 줍니다.

[그림 14]

3. 정리

상당히 많은 화면을 거치게 됩니다. 위 화면 중에서 필요로 하는 부분의 설정만을 통하여 원하는 관리 작업을 자동화 할 수 있습니다. 실습을 해보시는 겨우는 위 [그림 14] 처럼 등록된 작업의 세부 내용을 살펴 보시기 바랍니다.

이번 강좌에서는 자동화를 위한 또한가지의 방법으로 [데이터베이스 유지 관리 계획]에 대하여 살펴 보았습니다. 실제로 이 방법은 실무에서 많이 사용되는 방법중의 하나입니다. 위 내용들을 세밀하게 검토해서 실제 업무에 활용하여 보시기 바랍니다. 특히 확인 할 부분은 백업에서 설정된 내용대로 작업이 이루어진 후에 해당 폴더에 생성된 파일들이 어떤 이름으로 생셩되는지 하는 것입니다.

스폰서 링크 : http://www.mbcitschool.com

'Development > DataBase' 카테고리의 다른 글

[펌] [저장프로시저]생성 (0)	2011.08.13
[펌] [저장프로시저]개요 (0)	2011.08.13
[펌] (T-SQL) 서버 측 동적 쿼리에서의 탈출 (0)	2011.08.13
[본문스크랩] ANSI 표준 SQL 조인 (0)	2011.08.13
MSSQL int, bigint, smallint 및 tinyint, float 및 real, decimal 및 numeric (0)	2011.03.10

안정적인 DNS서비스 DNSEver

Posted by 키르히아이스

shutdown 함수

Development/네트워크 프로그래밍 2011. 8. 13. 15:18

Socket 클래스의 멤버 함수

버퍼를 닫게 할 용도로 사용

그냥 close 하면 입력 , 출력 버퍼가 모두 닫히니까 그것을 조절 하게 하기위해 사용하는 함수

주로 입력버퍼을 닫기위해 많이 사용

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemnetsocketssocketclassshutdowntopic.asp

'Development > 네트워크 프로그래밍' 카테고리의 다른 글

setsockopt의 SO_LINGER 옵션 (0)	2011.08.13
Microsoft Windows 2000 TCP/IP 구현 정보 (0)	2011.08.13
IP String -> DWORD 로 변환 (0)	2011.08.13
IOCP 예제 코드 (0)	2011.08.13
Graceful shutdown (0)	2011.08.13

안정적인 DNS서비스 DNSEver

Posted by 키르히아이스

이전 1 ··· 120 121 122 123 124 125 126 ··· 170 다음

Challenge Everything

'전체 글'에 해당되는 글 678건

[펌] (T-SQL) 서버 측 동적 쿼리에서의 탈출

'Development > DataBase' 카테고리의 다른 글

[본문스크랩] ANSI 표준 SQL 조인

'Development > DataBase' 카테고리의 다른 글

[본문스크랩] 데이터베이스 유지 관리 계획 마법사

'Development > DataBase' 카테고리의 다른 글

shutdown 함수

'Development > 네트워크 프로그래밍' 카테고리의 다른 글

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바


	자기 앞길에 어떠한 운명이 기다리고 있는가, 그것을 묻지 말고 앞으로 나아가라. 그리고 대담하게 자기의 운명에 부딪쳐라. 운명을 겁내는 사람은 운명에 지게 되고, 운명과 맞서 싸우면 운명이 길을 비킨다. - 비스마르크 by 키르히아이스