Online SQL and PL/SQL Formatter

SQL/MSSQL 2006. 9. 13. 12:57

http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl

데브피아 sql 게시판에서 퍼옴..

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

Text, Ntext, Image에 대한 간략한 자습서 (0)	2007.02.15
MSSQL Information Schema Views (0)	2007.01.11
[펌]서버측 동적 쿼리 에서의 탈출 (0)	2006.09.13
SQL2000 Table Hints 관련 문서 (0)	2006.07.06
SQL서버 에서 테이블, 프로시저 오너 변경하기. (0)	2006.04.14

Posted by tornado

|

[펌]서버측 동적 쿼리 에서의 탈출

SQL/MSSQL 2006. 9. 13. 12:30

(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
go

마치면서 … 단순한 사례들만을 늘어 놓았다. 최적 사례에 대한 소개가 생략된 것이다. 다음에 또 기회가 된다면, 그 때는 몇 가지 최적 사례에 대하여 논의해 볼 것이다.

예제는 예제일 뿐이다. 이 글을 읽는 분 들이 예제를 여과 없이 수용하고 개발에 적용한다면 분명 필자가 욕을 먹을 것이다. 위 예제를 예제로서 보지 말고 성능 상의 문제로까지 연결해서 분석해야 한다. 예제 중의 일부는 여전히 성능 문제를 해결하지 못하고 있으며, 여러분의 노력에 따라 더 나은 성능을 제공하는 쿼리로 변경하거나 재 개발할 수 있을 것이다. 이는 필자가 직접적으로 그 내용을 포함하지 않은 이유이기도 하다. 무엇이 문제인지를 모르고 해답만을 논할 수는 없지 않은가? 그리고 사실 이것이 더 재미있다! 그러니 오해가 없기를 바란다. 다시 한 번 당부하는 것은, 반드시 스스로 테스트하고 또 성능 문제로까지 확장해서 분석해 보아야 한다. 아마 짧지 않은 시간이 필요할 것이다!

객관식에 몇 가지 보기를 추가했을 뿐이며, 아직 많은 보기가 남아 있다. 또한, 이미 언급한대로 좋은 보기만을 제시한 것도 아니다. 다양한 사례를 통해 경험을 축적하고 이를 지식 데이터베이스화해야 한다. 최종적으로 여러분 스스로 보기를 만들어내고 가장 정답에 가까운 해답을 찾아낼 수 있는 능력을 길러야 한다.

동적 쿼리의 사용이 무조건 나쁘다는 것이 아니다. 비즈니스 모델의 특수성, 데이터베이스 모델의 변화, 단축된 개발 주기, 개발 생산성에 대한 이슈 등의 동적 쿼리를 필요로 하는 상황이 많이 발생한다. 문제는 무분별하게 과용되고 있다는 것이다. 동적 쿼리가 요구될 때, 기본적인 검토를 할 필요가 있다. 동적 쿼리를 사용하는 것이, 성능 대비 개발 생산성, 관리 용이성 등의 상대적 이득이 충분하다고 판단되어야 할 것이다. 지금 이 글을 읽고 난 뒤에, 여러분이 현재 개발하거나 운영 중인 데이터베이스의 얼마나 많은 동적 쿼리가 사용 중인지를 살펴 보자. 그리고 얼마나 무분별하게 과용되고 있는지도 평가해 보아야 할 것이다.

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

Text, Ntext, Image에 대한 간략한 자습서 (0)	2007.02.15
MSSQL Information Schema Views (0)	2007.01.11
Online SQL and PL/SQL Formatter (0)	2006.09.13
SQL2000 Table Hints 관련 문서 (0)	2006.07.06
SQL서버 에서 테이블, 프로시저 오너 변경하기. (0)	2006.04.14

Posted by tornado

|

[Mysql] GRANT로 사용자 추가하기

SQL/MySQL 2006. 8. 28. 11:30

#mysql -u root -p

mysql>GRANT ALL PRIVILEGESE ON 데이타베이스명.테이블명 TO '아이디'@'호스트'

->IDENTIFIED BY '패스워드' ;

※ DB 생성

mysql> create database test_db;

※ 사용자 생성

1. 외부에서 접근할수 있게 하는 경우

mysql> GRANT ALL PRIVILEGES ON test_db.* TO test_id@'%' <======== %는 모든 호스트를 의미함

-> IDENTIFIED BY 'test_passwd';

2. 내부에서 접속할수 있게 하는 경우

mysql> GRANT ALL PRIVILEGES ON test_db.* TO test_id@localhost <======== *는 모든 테이블을 의미함
-> IDENTIFIED BY 'test_passwd'; <======== localhost는 내부

3. 확인

mysql> select user from user;

mysql 재시작

[root@/root]mysqladmin -uroot -ptest_passwd reload

'SQL > MySQL' 카테고리의 다른 글

[MySQL] Got error 28 from storage engine (0)	2008.03.03
[MySQL] mysqld_multi 사용법, 데몬 두 개 운영하기 (0)	2006.11.03
mysql 관리 툴 EMS (0)	2006.09.30
replication 사용하기.. version - 4.0.17 (0)	2004.07.06
[펌] 데이터베이스 관리 (0)	2004.06.30

Posted by tornado

|

SQL2000 Table Hints 관련 문서

SQL/MSSQL 2006. 7. 6. 10:06

출처 : www.sql-server-performance.com

작성자 : Randy Dyess

내용 : SQL2000 Table Hints

음음.. 여러가지.. SQL서버 쿼리의 힌트 들에 대한 정보 입니다.

참고 하시길 바랍니다.

SQL Server 2000 Table Hints

by Randy Dyess

Visit his website at: www.TransactSQL.com

As you advance in your skills as a Transact-SQL developer, or SQL Server database administrator, there will come a time when you need to override SQL Server's locking scheme and force a particular range of locks on a table. Transact-SQL provides you with a set of table-level locking hints that you can use with SELECT, INSERT, UPDATE, and DELETE statements to tell SQL Server how you want it to lock the table by overriding any other system-wide or transactional isolation levels.

This article will describe the use of the table-level locking hints and general table hints that are available to Transact-SQL developers and SQL Server database administrators, and attempt to provide scenarios on when you should use a particular hint. You should note that SQL Server's query optimizer will automatically determine what it thinks is the best type of lock to use on an object in a query, and you should only override this choice only when necessary.

After saying that, it is sometimes necessary to correct the optimizer and use a hint to force the optimizer to use an index other than the one it picks, or to simply control the behavior of the locks. An example of choosing an alternative index would be when the query optimizer chooses an index that is constantly out of date and you do not have the cycles to bring the index up to date before you run the query. An example of controlling the locking behavior would be to lock a table to optimize a BULK INSERT.

Table Hints May Not Always Be Used By the Query Optimizer
You should note that even though you specify a table-level hint in your code, the query optimizer may ignore the hint. Table-level hints will be ignored if the table is not chosen by the query optimizer and used in the subsequent query plan.

In addition, the query optimizer will often choose an indexed view over a table. In case your table-level hint will be ignored, you can override the query optimizer's preference for indexed views by using the OPTION (EXPAND VIEWS) query hint.

Another reason the query analyzer may ignore your hint is due to the fact that the table may contain computed columns and the computed columns are computed by expressions and functions referencing columns in other tables, and the table hints are not specified for those tables. Table hints are not propagated on tables with computed columns, so the hint will not be used on tables referenced by computed columns, table-level hints are propagated on base tables and views referenced by another view though.

SQL Server also does not allow more than one table hint from either the Granularity hint group (PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, TABLOCKX), or the Isolation Level hint group (HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE) to be used in the FROM clause for each table. This basically means that you cannot specify ROWLOCK and TABLOCK for a table in the same FROM clause. SQL Server will also not allow the NOLOCK, READUNCOMMITTED, or READPAST hints to be used against tables that are the targets of a DELETE, INSERT or UPDATE statement.

Table Hint Syntax
Now that we have mentioned most of the hints, let's look at the syntax for table-level hints used with the FROM clause.

SYNTAX[ FROM { < table_source > } [ ,...n ] ] < table_source > ::= table_name [ [ AS ] table_alias ] [ WITH ( < table_hint > [ ,...n ] ) ] < table_hint > ::= { INDEX ( index_val [ ,...n ] ) | FASTFIRSTROW | HOLDLOCK | NOLOCK | PAGLOCK | READCOMMITTED | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK } Microsoft encourages the use of the WITH keyword, even though it is optional, as they state that a future release of SQL Server may require this keyword. Microsoft also encourages the use of the optional commas between different hints, as spaces are only allowed for backward compatibility reasons.

You can see that there are fifteen hints defined in this syntax listing. Thirteen of these hints (HOLDLOCK, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, XLOCK) are considered table-level locking hints while (INDEX and FASTFIRSTROW) are considered table hints. This doesn't mean much to most developers, but I thought I would tell you how Microsoft groups them.

Table Hint Descriptions
Now that you know the names of the hints, how they are grouped, and the syntax of each, lets go over what each hint does.

INDEX is used to specify the name or object ID of an index or indexes that will be used by SQL Server when processing the statement. SQL Server will chose an index by default if one is not specified by the use of the INDEX keyword, but sometimes you will need to force SQL Server to use a particular index.

Only one index hint can be used per table, but you can specify more than one index in this hint. If a clustered index exists on the specified table, using INDEX(0) will force a clustered index scan and INDEX(1) will force a clustered index scan or seek. If no clustered index exists on the table, then INDEX(0) will force a table scan while INDEX(1) will be interpreted as an error.

If you chose multiple indexes to be used for the hint, any duplicates will be ignored. Be careful in the order you place indexes when you use multiple indexes with the index hint. SQL Server try to apply as many conditions as possible on each index, so if you place the broader indexes first, you may have all your conditions covered and SQL Server will not have to create AND statements for all the specified indexes.

You can use a maximum of 250 non-clustered indexes in an index hint. Be aware that if an index hint with multiple specified indexes is used on a fact table in a star join, then SQL Server will ignore will return a warning message and ignore the hint.

FASTFIRSTROW will optimize the query to retrieve the first row of the result set.

HOLDLOCK (equivalent to SERIALIZABLE) applies only to the table specified and only for the duration of the transaction, and will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required. HOLDLOCK cannot be used in a SELECT statement with the FOR BROWSE option specified.

NOLOCK (equivalent to READUNCOMMITTED) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back.

PAGLOCK will force the use of a page lock instead of a table lock.

READCOMMITTED specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, which will result in nonrepeatable reads and may cause phantom data. READCOMMITTED is the default table hint in SQL Server.

READPAST specifies that locked rows be skipped during the read. READPAST only applies to transactions operating at the default READ COMMITTED isolation level, and will only read past row-level locks. READPAST can only be used in SELECT statements. Normal blocking can be worked around by having transactions read past rows being locked by other transactions.

READUNCOMMITTED (equivalent to NOLOCK) permits dirty reads. Dirty reads will not issue shared locks and will ignore exclusive locks placed by other processes. It is possible to receive error messages if the read takes place on an uncommitted transaction or a set of pages being rolled back.

REPEATABLEREAD specifies that locks be placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction.

ROWLOCK forces the use of row-level locks instead of page or table level locks.

SERIALIZABLE (equivalent to HOLDLOCK) applies only to the table specified and only for the duration of the transaction, and it will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required.

TABLOCK specifies that a table lock to be used instead of a page or row level lock. This lock will be held until the end of the statement.

TABLOCKX specifies that an exclusive lock be held on the table until the end of the statement or transaction, and will prevent others from reading or updating the table.

UPDLOCK specifies that update locks will be used instead of shared locks, and will hold the locks until the end of the statement or transaction.

XLOCK specifies that an exclusive lock be used and held until the end of the end of the transaction on all data being processed by the statement. The granularity of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints.

Table Hint Usage
Now that you know a little about each of the table-level hints, you may be wondering when you may need to use them. Where I work, we have a very large database that is used by approximately 10,000 customer service reps in a call center environment. While the reps are using the database, we have to load approximately 400,000 new rows of data into the database every three days. This load process can take up to 16 hours, so we are often forced to run the load during operational hours.

To optimize our BULK INSERT load process, we have added the TABLOCK hint to lock tables and speed inserts, and the READUNCOMMITTED hint to allow dirty reads of the data. All transactions generated by the reps are placed into smaller transactional databases so dirty reads are not a problem.

Our very large database and large number of monthly inserts caused our table statistics and indexes to be out of date. We just didn't have enough operating cycles to keep the all of the statistics and indexes completely updated after every data load.

This problem sometimes caused the query optimizer to create incorrect query plans because it did not always have the most up-to-date information, resulting in poor performance. This forced me to use index hints to force a query to use an index or indexes that I knew were keep updated to solve the slow response times.

Another use of the index hint is to force the use of an index when the query optimizer insists on using a table scan. For example, the SQL Server 7.0 query optimizer seems to have a preference for table scans even though very few rows of the table will be returned by the query.

I don't tend to use the many other hints at work, but in the past I have found use for the READPAST hint in work queues to allow a row of data to be returned to a client without waiting for locks to be releases by other processes. This is nice to use if you do not want end users to obtain the same row of data.

Others have told me that they often use the FASTFIRSTROW hint when they want to return the first row to the user quickly, to give them something to do, while the rest of the query catches up. I haven't found too many uses for TABLOCKX, UPDLOCK, XLOCK, or SERIALIZE (HOLDLOCK) in the type of databases I'm accustomed to working with, but I have been told that they are great hints for financial and reporting situations when you need the data to be consistent throughout a transaction.

Different hints are needed for different types of databases or transactions, and you will eventually determine which ones are proper for your situation.

Summary
As you can see, table-level hints are available for use by Transact-SQL developers or SQL Server database administrators, but should only be used to fine-tune your code, not as a standard technique for writing queries. You should perform a strict review of the query plans procedure by the query optimizer before you decide that a table-level hint may be want you need to solve your problem. In addition, you should perform this strict review after the hint is in place.

While table-level hints are not for all levels of users, experienced administrators and developers can use them to solve a limited set of problems, as well as fine-tune a limited set of queries in which the query optimizer has failed in its job to optimize correctly.

Knowledge Based Articles
Q235880 INF: Optimizer Hint NOLOCK or Isolation Level READ UNCOMMITTED Generates Error 605
Q297466 BUG: READPAST Locking Hint Returns an Incorrect Number of Rows
Q308760 FIX: SQL Server Optimizer Ignores Index Hint for UPDATE If One or More Non-Clustered Indexes Exist
Q308886 PRB: NOLOCK Optimizer Hint May Cause Transient Corruption Errors in the SQL Server Error Log
Q310935 FIX: Use of a Dynamic API Server Cursor with a NOLOCK Hint Causes Error 1047
Q320434 FIX: Bulk Insert with TABLOCK Hint May Result in Errors 8929 and 8965 When You Run CHECKDB
Q247365 BUG: Dynamic Cursor With NOLOCK Hint, DELETE Activity, Causes Assertions in Error Log

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

Text, Ntext, Image에 대한 간략한 자습서 (0)	2007.02.15
MSSQL Information Schema Views (0)	2007.01.11
Online SQL and PL/SQL Formatter (0)	2006.09.13
[펌]서버측 동적 쿼리 에서의 탈출 (0)	2006.09.13
SQL서버 에서 테이블, 프로시저 오너 변경하기. (0)	2006.04.14

Posted by tornado

|

SQL서버 에서 테이블, 프로시저 오너 변경하기.

SQL/MSSQL 2006. 4. 14. 15:55

sp_changeobjectowner '예전소유자.프로시저명','dbo'

도움말에 나와있음 -.-;

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

Text, Ntext, Image에 대한 간략한 자습서 (0)	2007.02.15
MSSQL Information Schema Views (0)	2007.01.11
Online SQL and PL/SQL Formatter (0)	2006.09.13
[펌]서버측 동적 쿼리 에서의 탈출 (0)	2006.09.13
SQL2000 Table Hints 관련 문서 (0)	2006.07.06

Posted by tornado

|

[펌] 유용한 MS-SQL 사이트..

SQL 2005. 12. 20. 10:12



• 온라인을 통한 기술지원		• SQLER
http://support.microsoft.com/oas 제품별 기술지원 정보 및 온라인을 통한 기술지원 제공.		http://www.sqler.pe.kr/ SQL Server 팁, 강좌제공 및 활발한 게시판운영
• 뉴스그룹		• MSSQL
http://support.microsoft.com/newsgroups 고객 상호간 또는 Microsoft 기술지원 엔지니어 및 MVP에 의해 지원되는 게시판 형식의 뉴스그룹을 통한 기술지원 제공.		http://www.mssql.org/ SQL Server 유용한 강좌를 제공하는 개인 홈페이지
• TechNet 온라인		• SQL Server 2005 커뮤니티
www.microsoft.com/korea/technet IT Pro를 위한 백과사전		http://www.sqlyukon.co.kr/ SQL Server 2005 최신 정보제공
• MSDN 온라인		• Jangrae's SQL World
www.microsoft.com/korea/msdn 샘플코드, 라이브러리, 기술문서, 제품 다운로드 등 개발자들의 필수 참고 사이트		http://www.sqlworld.pe.kr/ MS SQL을 공부하시는 분들에게 유익한 정보를 제공
• Microsoft 행사 및 세미나 정보		• OLAP Forum
www.microsoft.com/korea/events Microsoft 행사 및 세미나 일정 공지		http://www.olapforum.com/ 국내 최고의 OLAP 사이트
• Microsoft 다운로드 센터		• DB 가이드넷
www.microsoft.com/korea/download 최근의 주요 업데이트 프로그램, 서비스 팩 및 기타 유용한 파일 등의 다운로드		http://www.dbguide.net/ 한국데이터베이스진흥센터에서 운영하는 DB 구축·운영 종합정보 사이트
• Microsoft e-Seminar		• MCP월드
http://www.microsoft.com/korea/seminar Microsoft가 주관하는 모든 세미나의 동영상과 발표자료 제공		http://www.mcpworld.com/ 마이크로소프트 MCP인증 관련 커뮤니티
• Microsoft 교육 및 인증		• 고수닷넷
www.microsoft.com/korea/traincert Microsoft 자격증 및 Microsoft 공인 교육에 대한 정보 제공		http://www.gosu.net/ 국내 최초 아티클 전무 개발자 커뮤니티
• MDSN HOW-TO 문서
http://www.msdn.microsoft.com/howto 실제 개발과 관련된 절차식 프로그램 가이드 라인 제시
• Microsoft Patterns & Practices
http://www.microsoft.com/practices 애플리케이션의 디자인 배포, 아키텍처, 제작 등에 관련된 Microsoft의 제안

'SQL' 카테고리의 다른 글

[sybase] Update 시 조인을 걸어서 사용하기. (1)	2008.08.07
[쌩기초] sybase 페이지 분할 방법. (0)	2008.07.27
[펌] 「MySQL 5.0」등장, 기업 사용자도 OK! (0)	2005.10.27
[펌] 동적 SQL의 축복과 저주 (0)	2005.10.13
[펌] [Oracle]오라클 어드민 팁 (0)	2005.10.11

Posted by tornado

|

[펌] 「MySQL 5.0」등장, 기업 사용자도 OK!

SQL 2005. 10. 27. 10:21

뉴스 :「MySQL 5.0」등장, 기업 사용자도 OK!

'SQL' 카테고리의 다른 글

[쌩기초] sybase 페이지 분할 방법. (0)	2008.07.27
[펌] 유용한 MS-SQL 사이트.. (0)	2005.12.20
[펌] 동적 SQL의 축복과 저주 (0)	2005.10.13
[펌] [Oracle]오라클 어드민 팁 (0)	2005.10.11
[mssql] 계층적 구조 표현하기. (0)	2005.10.07

Posted by tornado

|

[펌] 동적 SQL의 축복과 저주

SQL 2005. 10. 13. 11:48

dynamic_SQL.html

동적 SQL의 축복과 저주

원문 : SQL Server MVP Erland Sommarskog의 SQL Text
번역 : quest, ASP MVP (microsoft.public.kr.asp.qna)

Microsoft SQL 서버에 대한 여러 뉴스그룹에서 왜 다음 쿼리가 불가능한지를 문의하는 사람들을 종종 보게 된다.:

 SELECT * FROM @tablename SELECT @colname FROM tbl SELECT * FROM tbl WHERE x IN (@list)

많은 경우에 간략한 예와 함께 동적 SQL을 사용하세요 라는 답변이 달리곤 하지만, 답변하는 사람들조차 동적 SQL 문의 사용시에 주의해야 할 사항에 대한 언급을 잊는 경우가 많다.

이 기사에서는 MS SQL Server의 저장 프로시저에서 동적 SQL의 사용에 대해 살펴보고, 조심스럽게 다루어야할 여러 강력한 특징들에 대해 얘기하고자 한다. 그런 특징들에 대해 설명하기 전에, 우선 왜 저장프로시저를 사용해야 하는지에 대해 논의할 것이다. 그 다음에 저장 프로시저를 사용함으로써 얻는 장점과 동적 SQL 효과간에 충돌에 대해 얘기해볼 것이다. SQL Injection이라고 알려진 일반적인 보안 문제에 대해 언급하고, 몇가지 좋은 코딩 습관에 대해 알아보고자 한다. 마지막으로, 동적 SQL이 좋은 해결책으로 활용되는 경우와 그렇지 못한 경우에 대해 살펴볼 것이며, 후자의 경우에는 대신 사용가능한 방법을 제안하고자 한다.

목 차:

   왜 저장 프로시저를 사용하는가?
   EXEC()와 sp_executesql
      EXEC()
      sp_executesql
      어느 것을 사용해야 할까?
      커서(Cursors)와 동적 SQL
   동적 SQL과 저장 프로시저
   SQL Injection - 심각한 보안 문제
   좋은 코딩 습관과 동적 SQL
   동적 SQL을 사용(하지 말아야)하는 일반적인 경우
      select * from @tablename
      select * from sales + @yymm
      update tbl set @colname = @value where keycol = @keyval
      select * from @dbname + '..tbl'
      select * from tbl where col in (@list)
      select * from tbl where @condition
      동적 검색 조건 (Dynamic Search Conditions)
      select * from table order by @col
      select top @n from table order by @col
      create table @tbl
      링크드 서버 (Linked servers)
      오픈쿼리 (OPENQUERY)
      열의 너비를 동적으로 변화시키고자 할 때 (Dynamic Column Widths)
   감사의 글

왜 저장 프로시저를 사용하는가?

동적 SQL이 실제로 무엇인가를 살펴보기 전에, 왜 저장 프로시저를 사용해야 하는지를 먼저 알아볼 필요가 있다. 저장 프로시저를 사용하지 않고 클라이언트(※ 역주: 여기서의 클라이언트는 SQL 서버에 대한 클라이언트를 의미합니다. 웹프로그램 개발의 경우 웹서버가 여기에 해당하며, 클라이언트 코드는 ASP와 같은 Server Side Script를 의미합니다.) 혹은 (COM+와 같은) 중간층(middle layer)에서 직접 SQL 문으로 이루어진 명령문을 내보내는 복잡한 응용프로그램을 작성할 수도 있다. 세련되어 보이기 때문에 저장 프로시저를 사용하는 것은 아니며, 이에는 분명 장점이 존재한다.

1. 권한 체계 (The Permission System)

저장 프로시저는 사용자들에게 데이타에 대한 제한적인 접근을 허용케하는 전통적인 수단이다. 쿼리 분석기와 같은 도구를 이용할 경우 원하는 어떠한 작업도 수행가능하므로, 사용자들은 SELECT, INSERT, UPDATE 혹은 DELETE 같은 문장을 직접 실행할 수 있는 권한을 가져서는 안된다. 예를 들어, 권한을 가진 사용자가 직원 데이타베이스에 접근하게 될 경우, 쉽게 봉급을 인상시킬 수 있다. 저장 프로시저를 사용하면, 해당 프로시저의 실행은 프로시저 소유자의 권한을 이용하게 되므로, 사용자들은 테이블에 대한 직접적인 권한을 필요로 하지 않게 된다.

요즘은 이 상황에 대해 몇가지 선택 가능한 사항이 더 존재한다. 사용자 계정에 직접 권한을 부여하기 보다는, 비밀번호가 필요한 고정 서버 역할(application role)에 접근권한을 부여할 수 있으며, 그런 비밀번호를 응용프로그램 안에 숨겨둘 수 있다. SQL 서버에서 고정 서버 역할은 7.0 버전부터 지원되었으며, COM+와 같은 중간층을 사용하면 사용자가 SQL 서버에 직접 접근할 필요가 없다는 점에서 더 안전하다.

하지만 고정 서버 역할이나 COM+ 등을 사용하지 않는다면, SQL 서버 보안 측면에서 저장 프로시저는 여전히 중요한 의미를 가진다.

2. 실행계획 캐쉬 (Caching Query Plans)

저장 프로시저를 사용하는 다른 중요한 이유는 성능을 향상시키기 위해서이다. 저장 프로시저가 최초로 실행되면, SQL 서버는 해당 프로시저에 대한 실행계획을 생성시키며, 이 실행계획이 캐쉬에 저장된다. 해당 저장프로시저가 재실행 요청을 받으면, SQL 서버는 저장된 실행계획을 재사용한다. 실행계획이 만료되거나 혹은 SQL 서버가 새로운 실행계획을 생성시켜야 할 이유가 생길 때까지 해당 실행계획은 캐쉬에 유지된다. (이 과정은 프로시저가 실행되는 동안에 일어나는데, 여기에 대한 논의는 이 기사의 범위를 범어난다.)

SQL 서버는 저장 프로시저 외부에서 실행되는 SQL 문장들에 대한 실행계획도 캐쉬한다. 자동 매개 변수화 과정(auto-parameterization) 또한 수행되어, 만약 다음과 같은 문을 실행시킬 경우, :

 SELECT * FROM pubs..authors WHERE state = 'CA' go SELECT * FROM pubs..authors WHERE state = 'WI'

SQL 서버가 쿼리를 다음과 같이 캐쉬하므로, 2번째 Select 문장은 첫번째 문장의 실행계획을 재사용한다.

 SELECT * FROM pubs..authors WHERE state = @1

SQL 문장이 좀더 복잡해 질 경우에는, SQL 서버가 자동 매개 변수화에 실패할 수도 있다. 심지어 유사한 쿼리문에서 공백 문자(white space)의 차이로 인해 SQL 서버가 캐쉬에서 해당 문장을 찾는데 실패하는 경우를 본 적도 있다. 결론은 SQL 서버가 순수 SQL 문장을 캐쉬하는데에도 좋은 성능을 발휘하지만, 저장 프로시저를 사용하는 경우 쿼리 실행계획의 재사용 확률이 더 높다는 것이다.

작고 짧은 실행시간을 가지며 자주 실행되는 SQL문 혹은 저장 프로시저의 경우에 캐쉬는 더욱 중요한 의미를 가진다. 실행계획을 만드는데 500ms 가 소요되면 상당한 과부하가 될 수도 있기 때문이다. 반면에, 20분동안 실행되는 프로시저의 실행계획을 세우는데 3초가 소요되는 것은 대단한 문제가 아니다. 만약, 아직도 SQL 6.5를 사용하고 있다면, 해당 버전의 SQL 서버는 순수한 SQL 문에 대한 캐쉬를 하지 않으며, 저장 프로시저에 대한 실행계획만을 캐쉬에 저장한다는 사실을 알아야 한다.

3. 네트워크 소통량 최소화 (Minimizing Network Traffic)

이것 역시 성능문제이다. 50줄 이상의 복잡한 Select 문장이 있으며, 각 실행시마다 Where 절 조건문이 조금씩 변한다고 가정해보자. 이 문장을 저장 프로시저에 넣을 경우, 네트워크를 통해 전달되는 데이타 소통량이 상당히 감소하게 되며, 해당 프로시저가 자주 실행될 수록 성능향상 효과가 증대된다.

서로 연관성을 가진 여러개의 SELECT / INSERT / UPDATE 문장으로 구성된 업무규칙이 존재할 경우 이러한 효과는 더욱 커진다. 저장 프로시저를 사용하면, 서버내부에서 이동하는 모든 데이타를 임시 테이블 혹은 변수에 저장할 수 있게 된다. 만약 순수 SQL 문장을 이용한다면, 서버와 클라이언트 혹은 중간층 사이에 데이타를 이동시켜야 한다. (사실, 이것이 전적으로 맞는 말은 아니다. SQL 문만을 사용해서도 임시 테이블을 생성시키고 거기에 데이타를 저장할 수 있다. 하지만 Connection pooling과 disconnected record sets에 대한 주의를 기울여야 한다.)

4. 출력매개변수 사용 (Using Output Parameters)

단일 행을 반환하는 SQL 문장을 실행시키고 싶을 경우에, SQL 문만 사용하면 결과 집합(record set)을 레코드셋으로 반환받아야 한다. 하지만, 저장 프로시저를 사용할 경우에는 성능이 월등한 출력매개변수의 사용이 가능하다. 단일 쿼리에 대한 실행시간의 차이는 무시할 수 있겠지만, 만약 다음과 같은 작업을 SQL 서버에 수천번 해야 한다면, 결과 집합으로 값을 받는 것에 비해 @key를 출력매개변수로 반환받는 경우의 이점은 엄청나게 커지게 된다.:

 INSERT tbl (...) VALUES (...) SET @key = @@identity

5. 업무규칙 모듈화 (Encapsulating Logic)

이것은 보안 혹은 성능향상과 관련된 것은 아니지만, 코드를 모듈화하는 방법의 하나이다. 저장 프로시저를 사용하면, SQL 문을 만들어내기 위해 클라이언트 코드와 씨름할 필요가 없다. 하지만, 이러한 이유때문에 저장 프로시저를 사용해야 한다고 말할 수는 없다. (SQL 코드가 클라이언트측 주요 언어의 문법에 묻혀 버리기는 하겠지만) 여전히 여러 매개변수들로부터 SQL 문을 조합하는 것이 가능하다.

좀 특별한 경우를 예로 들어 보겠다: 만약 쿼리 분석기 외의 다른 응용프로그램이 없다면, 관리자들을 위한 저장 프로시저를 작성하게 된다는 말이며, 이런 경우에는 저장 프로시저가 업무규칙 모듈화를 위한 유일한 수단이 된다.

6. 의존성 파악 (Keeping Track of what Is Used)

수백개의 테이블이 존재하는 복잡한 시스템에서, 간혹 어디에서 어떤 테이블 혹은 칼럼이 참조되었는지 알고 싶을 때가 있다. 이를테면, 만약 칼럼을 변경할 경우에 어떤 일이 일어날지 알고 싶은 경우가 있을 수 있다. 만약 모든 코드가 저장 프로시저에 보관되어 있다면, 참조된 개체를 찾기 위해 저장 프로시저의 코드만 살펴보면 된다. 또는 간단히 변경하고자 하는 칼럼 혹은 테이블을 누락시킨 데이타베이스를 생성시켜 어떤 일이 일어나는지 관찰할 수도 있다. 시스템 테이블 sysdepends와 시스템 저장 프로시저 sp_depends를 이런 목적에 사용할 수도 있지만, sysdepends내에 보관된 정보를 온전히 정확하게 유지시키기는 어렵다.

응용프로그램에서도 순수 SQL 문을 사용가능하도록 허용 한다면, 문제는 더욱 심각해진다. 훨씬 많은 양의 코드를 살펴봐야 하며, status와 같은 일반적인 이름을 가진 칼럼들은 놓치기도 쉽다. 그리고 sysdepends는 완전히 무의미하게 된다.

EXEC()와 sp_executesql

MS SQL 서버에서 동적 SQL을 실행시키는 2가지 방법은 EXEC()와 sp_executesql이다.

EXEC()

EXEC()는 다음 예제와 같이 그 사용법이 아주 간단하다.:

 SELECT @table = 'sales' + @year + @month EXEC('SELECT * FROM ' + @table)

비록 위의 예가 상당히 단순해 보이지만, 여기에는 놓쳐서는 안될 중요한 점이 존재한다. 첫번째 중요한 점은 비록 해당 문장이 저장 프로시저 내에 존재하더라도 현재 사용자의 권한으로 실행된다는 것이다. 두번째로 중요한 것은 EXEC()가 저장 프로시저의 실행을 위한 EXEC와 매우 유사하다는 점이다. 하지만 이 예제에서는 저장 프로시저를 호출하는 대신에, 단일 SQL 문을 일괄실행시켰다. 마치 저장 프로시저를 호출할 때처럼, 해당 일괄실행문은 호출하는 저장 프로시저와는 다른 실행범위(scope)를 가지게 된다. 여기에는 몇가지 중요한 의미가 내포되어 있다.:

SQL 일괄실행문 안에서는 호출하는 저장 프로시저의 지역변수 혹은 매개변수에 접근할 수 없다.
USE 문의 사용이 호출하는 저장 프로시저에 영향을 미치지 않는다.
SQL 일괄실행문에서 생성된 임시 테이블은 일괄실행문이 종료되면 삭제(drop)되기 때문에, 마치 저장프로시저가 종료된 경우와 마찬가지로, 호출하는 저장 프로시저에서 접근할 수 없다. 그러나, 일괄실행문 내부에서는 호출하는 저장 프로시저에서 생성된 테이블에 접근가능하다.
SQL 일괄실행문 내에서 SET 문장을 사용하면, SET 문장의 영향력은 일괄실행문 내부에서만 유지된다.
SQL 일괄실행문의 실행계획은 호출하는 저장 프로시저의 실행계획의 일부가 아니다. 해당 쿼리문의 캐쉬여부는 클라이언트 프로그램에서 SQL 문장만을 사용하는 경우와 동일하다.
SQL 일괄실행문이 (트리거내의 Rollback처럼) 일괄실행을 종료시키는 결과를 낳았을 때는, 동적 SQL의 일괄처리가 종료될 뿐만 아니라, 호출하는 저장 프로시저 (그리고 해당 프로시저를 호출한 다른 저장 프로시저도) 역시 종료된다.

정상적으로 저장 프로시저를 호출할 때와는 달리, EXEC()문에서는 매개변수 혹은 반환값을 사용할 수 없다. @@error 값은 일괄실행문의 마지막 문장의 실행결과에 관계가 있다. 그러므로, EXEC()내부에서 에러가 발생하더라도 뒤따르는 명령문이 성공적으로 수행되었다면, @@error는 0의 값을 가지게 된다.

EXEC()는 SQL 6.0에서 도입되었다.

EXEC(@sql)와 EXEC @sp를 혼돈하지 말아라. 후자는 이름이 @sp인 저장 프로시저를 실행시킨다.

sp_executesql

sp_executesql는 SQL 7에서 도입되었으며, 동적 SQL 문자열 내부로 입력과 출력을 위한 매개변수를 전달할 수 있다는 장점이 있다. 출력 매개변수를 사용하는 간단한 예는 다음과 같다. (※ 역주 : sysname은 nvarchar(128)과 같은 기능의 시스템 제공 사용자 정의 데이타 형식으로 데이타베이스 개체 이름을 참조할 때 사용됩니다.).:

 DECLARE @sql nvarchar(4000), @col sysname, @min varchar(20) SELECT @col = N'au_fname' SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col + N')) FROM authors' EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT SELECT @min

이 장점으로 인해, 동적 SQL 문을 사용할 때 EXEC()에 비하여 sp_executesql을 사용하면 지역변수로 값을 받아내기가 훨씬 쉬워졌다. (EXEC()에서도 INSERT EXEC()를 이용하여 동일한 작업을 수행할 수 있지만, 수월하지는 않다.)

sp_executesql의 첫번째 인자는 SQL 문(Unicode 문자열)으로, SQL 문법상 변수가 허용되는 곳에는 매개변수를 사용할 수 있다. (그러므로, 여전히 칼럼이름 혹은 테이블이름에 변수를 사용할 수는 없다). 매개변수의 데이타 형은 ntext이어야 하므로, nvarchar 형식의 변수를 사용하여야 한다. SQL 문이 상수로 전달되려면, Unicode 형식임을 나타내기 위해 N 접두어를 인용부호 앞에 붙여야 한다. SQL 문에는 @로 시작하는 매개변수가 포함될 수 있는데, 여기에 사용되는 매개변수들은 동적 SQL문의 외부에서 사용된 변수와는 전혀 별개의 변수이다. sp_executesql에서 사용되는 SQL 문에는 다른 곳에서 사용되는 SQL 문과 마찬가지의 문법이 적용되므로 여전히 칼럼명 혹은 테이블명에는 변수를 사용할 수는 없으므로, 동적으로 결정되는 경우에는 조합시킬 문자열 내에 포함시켜야 한다.

sp_executesql의 두번째 인자는 저장 프로시저 선언부와 마찬가지의 문법으로 매개변수 및 기본값이 정의된 매개변수 정의 목록이다. (온라인 도움말에는 매개변수에 대한 설명이 누락되어 있다.) 매개변수 정의 목록 역시 ntext 데이타 형이다. SQL 문장에서 사용된 모든 변수는 매개변수 선언목록에 명시되어야 한다.

sp_executesql의 나머지 인자는 매개변수 선언목록에서 선언된 것들로, 이들은 선언된 순서로 사용되거나 혹은 변수 이름과 같이 사용가능하다.

sp_executesql는 여러가지 이유로 인해 EXEC()보다 선호된다. sp_executesql 를 사용하면, 사용자가 직접 매개변수를 제공할 수 있기 때문에, SQL 서버가 매개변수 자동화를 수행하도록 의존할 필요가 없다는 것도 그 이유중의 하나이다. 그러므로, sp_executesql를 사용하면 캐쉬 사용의 가능성이 더 높아진다. (그래도 여전히 공백 문자의 사용은 주의를 기울여야 한다.) SQL injection 및 좋은 코딩 습관에 대한 얘기를 하면서 sp_executesql의 다른 장점에 대해 다시 언급할 것이다.

EXEC()에 대해 얘기되었던 특징들은 sp_executesql에도 마찬가지로 적용된다.:

SQL 코드는 고유한 실행범위(scope)를 가지므로, 호출한 저장 프로시저에서 변수에 접근할 수 없다.
현재 사용자의 권한이 적용된다.
USE 문장의 사용이 호출한 저장 프로시저에 영향을 미치지 않는다.
호출한 저장 프로시저에서 SQL 일괄실행문에서 생성된 임시 테이블을 사용할 수 없다.
SQL 일괄실행문내에서 사용된 SET 문장은 일괄실행문 내에서 영향력을 가지지만, 호출한 저장 프로시저에는 영향을 미치지 않는다.
sp_executesql에 의해 실행된 일괄실행문이 종료되면 호출한 저장 프로시저도 종료된다.
@@error는 동적 SQL 코드 내부의 최종 실행문의 상태를 반영한다.

온라인 도움말(Books Online)에 의하면, sp_executesql은 성공했을 경우 0, 실패했을 경우 1의 반환값을 가지지만, 최소한 SQL 2000에서 반환값은 @@error의 값과 같다.

sp_executesql에 대한 더욱 자세한 정보는 온라인 도움말을 참조하기 바란다. KB Article 262499은 출력매개변수의 특징에 대해 기술하고 있다.

어느 것을 사용해야 할까? (Which to Use)

동적 SQL을 규칙적으로 사용하는 경우에는, sp_executesql이 최선의 선택이다. sp_executesql의 사용시에는, 실행계획이 재사용될 가능성이 높고, 매개변수를 사용할 수 있기 때문이다. 아직 SQL 6.5를 사용하고 있는 경우를 제외하면, EXEC()를 사용해야 하는 경우는 동적 SQL 문이 nvarchar(4000)의 범위를 넘어서는 경우 뿐이다. 다음과 같이 사용가능하다. :

 EXEC(@sql1 + @sql2)

T-SQL에서 저장 프로시저를 호출할 때 매개변수로 연산식을 사용할 수 없는 것처럼, sp_executesql를 호출할 때도 하나의 변수만 매개변수로 사용할 수 있다. 만약, 반드시 분리된 쿼리문을 써야 한다면, sp_executesql를 EXEC()내에 포함시켜 사용할 수 있다.:

 DECLARE @sql1 nvarchar(4000), @sql2 nvarchar(4000), @state char(2) SELECT @state = 'CA' SELECT @sql1 = N'SELECT COUNT(*)' SELECT @sql2 = N'FROM authors WHERE state = @state' EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''', N''@state char(2)'', @state = ''' + @state + '''')

이런 경우에 인용부호(')를 여러번 겹쳐 사용하는 것이 혼란스럽다면 EXEC()만 사용할 수도 있다. (뒷부분에 제시될 사용자 정의함수(UDF; User Defined Function)인 quotestring()을 사용하면 이런 문제점을 해소할 수 있다.)

커서(Cursors)와 동적 SQL

커서는 자주 사용될 뿐만 아니라, 동적 SQL에서의 커서 사용에 대한 질문도 자주 접하게 되므로, 완성도 측면에서 예를 들도록 하겠다. DECLARE CURSOR EXEC()와 같이 사용할 수는 없지만, Declare Cursor문 전체를 동적 SQL에 포함시켜 사용하는 것은 가능하다.:

 SELECT @sql = 'DECLARE my_cur CURSOR FOR SELECT col1, col2, col3 FROM ' + @table EXEC sp_executesql @sql

위 쿼리의 실행에는 로컬 커서(local cursor)를 사용할 수 없다는 것에 주목하라.(로컬 커서는 EXEC(@sql) 문이 종료되는 시점에서 접근불가능하게 되어 버리기 때문이다.) Anthony Faull이 다음 예제에서와 같이 사용할 경우, 로컬 커서를 동적 SQL과 함께 사용할 수 있다고 지적해 주었다.

 DECLARE @my_cur CURSOR EXEC sp_executesql N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN @my_cur', N'@my_cur cursor OUTPUT', @my_cur OUTPUT FETCH NEXT FROM @my_cur

명명된 커서(Named Cursors)처럼 커서 변수에 접근가능하며, 예제에서 보이는 바와 같이 매개변수로 전달가능하다.

동적 SQL과 저장 프로시저

저장 프로시저를 사용하는 이유와 동적 SQL을 사용할 때 어떤 일이 일어나는지에 대해 살펴보자. 다음과 같은 프로시저를 사용하는 것으로 시작한다.:

 CREATE PROCEDURE general_select @tblname nvarchar(127), @key key_type AS -- key_type is char(3) EXEC('SELECT col1, col2, col3 FROM ' + @tblname + ' WHERE keycol = ''' + @key + '''')

앞으로 살펴 보겠지만, 이것은 전혀 의미없는 프로시저이다. 왜냐하면, 저장프로시저 사용시에 얻을 수 있는 거의 모든 장점을 살리지 못하기 되기 때문이다. 클라이언트 코드에서도 Select 문을 조합해서 SQL 서버에 바로 질의하는 것이 가능하다.

1. 권한 (Permissions)

사용자가 테이블에 직접 접근할 수 있는 권한이 없다면, 동적 SQL을 사용할 수 없다는 것은 너무나도 당연하다. 사용자가 Select 권한을 가지고 있는 환경도 있을 수 있다. 하지만, 권한이 문제되지 않는다는 것을 확신하지 못할 경우에는, 영구 테이블(permanent tables)에 대한 Insert, Update 및 Delete 문을 동적 SQL문에 사용해서는 안된다. 임시테이블을 사용하는 경우에는 아무런 권한 문제가 발생하지 않는다.

사용자들이 데이타베이스에 대한 직접적인 연결을 하지 않고, 고정 서버 역할(application roles)을 활용하거나 COM+와 같은 중간층을 사용하는 경우에는 이 문제에 대해 심각하게 생각할 필요는 없다. 하지만, SQL injection 절에서 여전히 고려해야할 다른 보안 문제에 대해 살펴볼 것이다.

Sysadmin 고정서버역할을 가진 사용자가 사용할 코드를 작성할 경우에는, 당연히 권한문제에 대해 걱정할 필요가 없다.

2. 실행계획 캐쉬 (Caching Query Plans)

살펴본 바와 같이, SQL 서버는 순수 SQL 문과 저장 프로시저 모두에 대해 실행계획을 캐쉬하지만, 저장 프로시저에 대한 실행계획을 재사용할 때 다소 더 정확하다. SQL 6.5 에서는 매실행시마다 재컴파일 되었기 때문에, 동적 SQL이 더 느리다고 확실히 말할수 있었으나, 그 이후 버전에서는 상황이 그렇게 명확하지는 않다.

앞에서 나왔던 general_select 프로시저를 보자. @tblname을 다르게 줄 경우, 실행계획은 캐쉬되고, @tblname에 대한 매개변수 자동화 과정이 일어난다. 이것은 클라이언트 코드에서 SQL 문을 생성시킨 경우에도 마찬가지이다.

이것이 의미하는 바는, 동적 SQL을 현명하게 사용한다면, 성능향상 효과를 얻을 수 있다는 것이다. 예를 들어, 저장 프로시저 내부에 복잡한 쿼리가 있고, 선호되는 실행계획이 실행 당시의 테이블에 있는 데이타에 의존한다고 가정하자. 해당 쿼리를 동적 SQL로 작성하고, SQL 서버가 충분히 똑똑해서 캐쉬된 정보를 전혀 쓰지 않기를 바랄 수 있다 (임시테이블이 사용되었다면, SQL서버가 똑똑하게 처리하지 못할 것이다.). 한편, 그 복잡한 쿼리를 각각의 목적에 맞는 저장 프로시저로 나누어서 같은 결과를 얻을 수도 있겠지만, 모든 로직이 한 군데에 위치한다면 코드는 훨씬 읽기 쉬울 것이다. 위의 내용은 사용자가 동적 SQL을 실행시킬 수 있도록 권한구성이 되어있을 경우를 가정하였다.(?)

3. 네트워크 소통량 최소화 (Minimizing Network Traffic)

앞의 두 절에서 저장프로시저내의 동적 SQL은 클라이언트에서의 평범한 SQL문에 비해 장점이 없다고 하였는데, 네트워크 소통량 문제에 대해서는 그렇지 않다. 저장 프로시저 내에 동적 SQL을 사용하면, 네트워크 비용이 들지 않는다. 예제 프로시져인 general_select의 사용시에는, 이러한 장점이 거의 없으며, 순수 SQL 코드의 크기와 저장 프로시져를 호출하기 위한 실행문의 크기가 거의 비슷하다.

그러나, 복잡한 조건에 따라 6개의 테이블을 조인시키는 복잡한 쿼리를 생각해 보자. 사용자가 필요로 하는 자료의 기간정보에 따라 필요한 테이블은 sales0101이 될 수도, sales0102 가 될 수도 있다. 사실 이런 테이블 디자인은 좋지 못한데, 이에 대해서는 다시 살펴보기로 하고, 아무튼 여러분이 이런 상황에 처해있다고 가정해 보자. 이런 문제를 해결하기 위해 동적 SQL과 저장프로시저내를 활용한다면, 전체 쿼리를 매번 질의하지 않고 기간정보만 매개변수로 전달하면 된다. 만약 쿼리가 시간당 한번 질의된다면, 이득은 무시할만 하겠지만 네트워크 사정이 그렇게 좋지 못한 환경에서 15초마다 한번씩 쿼리를 보내야 한다면 차이점을 느낄 수 있을 것이다.

4. 출력매개변수 사용 (Using Output Parameters)

출력매개변수를 얻을 목적만으로 저장프로시저를 사용하는 경우 동적 SQL의 사용과 별다른 관련성이 없다. 다른 말로, 클라이언트에서 직접 sp_executesql문을 사용할 수 있으므로, 저장 프로시저없이도 출력매개변수의 사용이 가능하다.

5. 업무규칙 모듈화 (Encapsulating Logic)

저장프로시저에 대한 이전 주제에서 다루어진 것들 외에 특별히 더할 내용은 없다. 그럼에도 불구하고 저장 프로시저를 사용하기로 결정한다면 SQL에 관련된 모든 숨겨야할 내용을 저장프로시저내에 포함시킬 수 있다는 점은 지적하고 싶다. 이러한 의미에서 general_select의 예에서 테이블 이름을 매개변수로 직접 전달하는 것은 좋지 못한 생각이다. (sysadmin 고정 서버 역할의 구성원을 위한 응용프로그램의 경우는 예외이다.)

6. 의존성 파악 (Keeping Track of what Is Used)

동적 SQL은 이 목적에 위배된다. 동적 SQL은 sysdepends를 사용하지 않으므로 참조되는 개체파악이 힘들어지며, 데이타베이스 내에 참조되는 개체가 존재하지 않는 경우에도 알아차리기 어렵다. 테이블이름 혹은 칼럼 이름을 매개변수로 사용하지 않을 경우에는, 어떤 테이블이 사용되었는지를 파악하려면 최소한 SQL 코드를 뒤져보는 작업을 해야 할 것이다. 그러므로, 동적 SQL을 사용할 때에는 테이블 이름과 칼럼 이름을 프로시켜 코드에 제한해서 사용하라.

SQL Injection - 심각한 보안 문제

SQL injection 은 공격자로 하여금 개발자가 의도하지 않은 SQL 문을 실행시킬 수 있게 하는 기술이다. 사용자가 입력한 값이 직접 SQL 코드로 전달될 때 (저장 프로시저에서 동적 SQL을 사용하거나 혹은 클라이언트 쪽에서 SQL문을 생성시키거나), SQL injection의 위험이 존재한다. 이 공격방법은 MS SQL 서버뿐만 아니라, 모든 관계형 데이타베이스 관리시스템(RDBMS)에 적용된다.

다음과 같은 저장 프로시저에 대해 생각해 보자.:

 CREATE PROCEDURE search_orders @custname varchar(60) = NULL, @prodname varchar(60) = NULL AS DECLARE @sql nvarchar(4000) SELECT @sql = 'SELECT * FROM orders WHERE 1 = 1 ' IF @custname IS NOT NULL SELECT @sql = @sql + ' AND custname LIKE ''' + @custname + '''' IF @prodname IS NOT NULL SELECT @sql = @sql + ' AND prodname LIKE ''' + @prodname + '''' EXEC(@sql)

매개변수 @custname와 @prodname 에 대한 입력은 사용자 입력필드로부터 직접 전달되는 값이다. 여기서, 심술궂은 사용자가 @custname에 전달된 입력필드에 다음과 같은 값을 전달한다고 가정해보자.

 ' DROP TABLE orders --

결과로 만들어지는 SQL문은 다음과 같다.:

 SELECT * FROM orders WHERE 1 = 1 AND custname LIKE '' DROP TABLE orders --'

붉은 색으로 표시된 문장이 보이는가? 이런 공격유형의 성공여부는 상황에 따라 다르다. SQL 서버에 직접 접속한 평범한 사용자가 테이블을 삭제(drop)할 수 있는 권한을 가진다고 보기는 어렵지만, 만약 그 사용자가 웹에서 접속한 사용자이고, 웹서버가 SQL서버에 관리자 권한으로 연결되어 있다면, 해당 공격은 성공하게 될 것이다. 이러한 정밀한 공격에 필요한 권한을 가지고 있지 않더라도, 공격자는 여전히 원하는 명령을 내릴 수 있는 수단을 갖게 된다.

공격자는 먼저 입력필드에 작은 따옴표(')를 넣었을 때 어떤 일이 일어나는지 살펴본다. 만약 문법 오류 (syntax error)가 발생한다면, 공격자는 취약점이 존재한다는 것을 알게 된다. 그런 다음, 공격자는 쿼리를 종료시킬 수 있는 다른 수단을 찾아내고, 결국 자기 자신이 작성한 SQL 명령을 더할 수 있게 된다. 마지막으로 공격자는 SQL 문장의 나머지를 무시하고 문법 오류를 피하기 위하여 주석 문자를 사용한다. 공격자가 세미콜론(;)과 같은 문자를 사용할 수도 있다. SQL 7 이후 버전에서는 세미콜론이 T-SQL 문장을 분리하기 위한 선택사항으로 사용된다. 세미콜론을 사용하여 오류가 발생한다면, 공격자는 general_select에서와 같은 문제점이 존재한다는 것을 알아차리게 된다. 만약 사용자가 입력한 값이 직접 매개변수 @tablename에 전달된다면, 다음과 같은 문장을 전달하는 것도 가능하다:

 some_table WHERE keycol = 'ABC' DELETE orders

사용자가 직접 값을 넣을 수 있는 입력필드만 공격에 이용되는 것이 아니라는 것을 기억해야 한다. 저장 프로시저에 직접 전달되는 값이 URL에 포함되어 있다면, 공격자가 이것을 이용할 수도 있다.

이런 공격에는 기술도 필요하겠지만, 운도 많이 작용할 거라고 생각할 수도 있을 것이다. 하지만, 인터넷에는 시간이 널널한 수많은 공격자가 존재한다는 걸 기억해야 한다. SQL injection은 심각한 보안문제이며, 이에 대항하기 위한 방법을 알아야 하다. 이를 위한 2가지 방법이 존재한다.

사용자에게 SQL 서버에서 필요한 권한 이상을 부여하지 마라. 응용프로그램이 중간층(middle layer)을 이용하여 SQL 서버에 접속한다면, 테이블에 대한 Select 권한만을 가지는 평범한 사용자 계정으로 접속케 해라. 경험이 적거나, 적당히 얼버무리는 개발자들이 SQL injection이 가능케하는 헛점을 만들 수 있기 때문이다.
간단히 적용가능한 코딩 습관이 있는데, 다음 절 "동적 SQL을 위한 코딩 습관"에서 이에 대해 살펴볼 것이다.

SQL injection 문제는 저장 프로시저에 제한된 문제만은 아니라는 것을 강조해야 겠다. 문자열 변수는 종종 제한없이 사용가능하므로, 클라이언트 코드에서 SQL 명령문을 생성시켜 전달할 때 더 큰 취약점이 존재할 수도 있다. 저장 프로시저를 사용하는 경우에도, 호출하기 위해 EXEC문을 텍스트로 전달해야 한다는 것을 기억해야 한다. 여기에 SQL injection 공격이 가능한 취약점이 존재한다.

좋은 코딩 습관과 동적 SQL

동적 SQL을 사용하는 것이 어렵지 않게 보이겠지만, 작성한 코드에 대한 통제를 잃는 경우를 피하기 위한 규칙들이 존재한다. 주의를 기울이지 않는다면, 작성해 놓은 코드가 지저분해지거나 읽기 어렵게 되고, 문제해결을 위한 시도나 유지보수가 어렵게 된다. 무시무시한 프로시져 general_select을 다시 살펴보자:

 CREATE PROCEDURE general_select @tblname nvarchar(127), @key key_type AS -- key_type is char(3) EXEC('SELECT col1, col2, col3 FROM ' + @tblname + ' WHERE keycol = ''' + @key + '''')

여기서 사용된 중복된 인용부호를 보고 이게 도대체 무슨 뜻이지?라며 자문할 수도 있을 것이다. SQL은 문자열 제한자를 문자열에 포함시키기 위해 해당 리터럴을 겹쳐써야 하는 언어중 하나이다. 따라서, 위의 네개의 작은 따옴표('''')는 하나의 작은 따옴표(')를 표현하기 위한 문자열 리터럴이다. 위에서는 간단한 예를 들었지만, 상황은 더 나빠질 수도 있다.

쉽게 저지르게 되는 다음과 같은 에러가 있다.:

 EXEC('SELECT col1, col2, col3 FROM' + @tblname + ' WHERE keycol = ''' + @key + '''')

FROM 다음에 공백이 생략된 것이 보이는가? 해당 프로시저를 컴파일 할때는 에러메시지가 보이지 않지만, 실행시키려로 하면 열 이름 'col1'이(가) 잘못되었습니다., 열 이름 'col2'이(가) 잘못되었습니다., 열 이름 'col3'이(가) 잘못되었습니다., 열 이름 'keycol'이(가) 잘못되었습니다.라는 에러메세지를 접하게 된다. 그러면, 입력된 테이블 이름이 정확하므로 칼럼이름이 잘못된 것으로 오해하게 되어, 혼란이 가중된다. 아래는 매개변수가 foo와 abc일때 실제로 생성된 코드이다.:

 SELECT col1, col2, col3 FROMfoo WHERE keycol = 'abc'

FROMfoo는 col3 칼럼에 대한 별칭(alias)으로 해석되므로, 문법적인 오류가 아니다.

앞서 테이블 이름이나 칼럼 이름을 매개변수로 사용해서는 안된다는 의견을 제시했었다. 그러나 여기는 좋은 코딩 습관에 관한 절이므로, 한번 더 강조하겠다. 저장 프로시저를 작성하면, 해당 프로시저는 SQL 개체를 참조하는 독점적인 공간이 된다. (save stored procedures that is! ?) 그럼에도 불구하고, 아래에 동적 SQL에 대한 좋은 코딩 습관의 장점을 보여주도록 general_select을 개선해 보았다.:

 CREATE PROCEDURE general_select @tblname nvarchar(127), @key key_type, @debug bit = 0 AS DECLARE @sql nvarchar(4000) SET @sql = 'SELECT col1, col2, col3 FROM ' + quotename(@tblname) + ' WHERE keycol = @key' IF @debug = 1 PRINT @sql EXEC sp_executesql @sql, N'@key key_type', @key = @key

보시는 바와 같이, 몇가지를 수정하였다:

@tblname이 SQL injection에 쓰이는 걸 방지하기 위해 quotename()을 사용하였다. quotename()에 대한 자세한 내용은 아래를 참조하라.
매개변수 @debug를 첨가해서, 예기치 못한 에러가 나타나는 경우에는 @key를 사용하여 SQL 코드가 어떻게 작성되었는지 쉽게 나타낼 수 있다.
문자열내에 @key 값을 포함시키지 않고, sp_executesql를 사용하고 @key를 매개변수로 전달하였다. 이런 방식은 SQL injection에 대한 대비도 된다.

quotename()은 SQL 7에서 처음 도입된 내장함수(built-in function)이다. 해당 함수는 구분 식별자가 되도록 추가된 구분 기호와 함께 유니코드 문자열을 반환한다. 이 함수의 제공목적이 원래 개체 이름을 인용하기 위한 것이므로, 기본 구분자는 각괄호(squeare brackets; [])이지만, 작은 따옴표(') 혹은 큰 따옴표(")로 지정할 수도 있다. 그러므로, EXEC()를 사용해야 할 때는 SQL injection을 막기 위해 quotename()을 사용할 수 있다. SQL injection절에서 예로 들었던 search_orders 프로시저에서 몇줄을 다음과 같이 수정할 수 있다.:

 IF @custname IS NOT NULL SELECT @sql = @sql + ' AND custname LIKE ' + quotename(@custname, '''')

quotename() 함수에 눈여겨 볼만한 점이 하나 더 있다.: quotename()의 입력인자는 nvarchar(129)이므로, 긴 문자열을 대상으로는 사용하지 못한다. SQL 2000에서는 다음과 같은 사용자 정의 함수를 사용할 수 있다.:

 CREATE FUNCTION quotestring(@str nvarchar(1998)) RETURNS nvarchar(4000) AS BEGIN DECLARE @ret nvarchar(4000), @sq char(1) SELECT @sq = '''' SELECT @ret = replace(@str, @sq, @sq + @sq) RETURN(@sq + @ret + @sq) END

사용법은 아래와 같다.:

 IF @custname IS NOT NULL SELECT @sql = @sql + ' AND custname LIKE ' + dbo.quotestring(@custname)

SQL 7에서는, 사용자정의 함수가 제공되지 않으므로, quotestring을 저장 프로시져로 만들어야 한다. SQL 6.5에서는 replace() 함수가 제공되지 않으므로, 별 다른 대안이 없다. (SQL 서버 MVP인 Steve Kass가 quotename() 혹은 사용자 정의함수에 대해 제안해주었음을 밝힌다.)

중복된 인용부호로 인해 야기되는 지저분함을 피하기 위한 다른 대안은, T-SQL이 큰 따옴표(")를 지원한다는 사실을 이용하는 것이다. QUOTED_IDENTIFIER를 OFF로 설정하면, 문자열 구분자로 큰 따옴표("))를 쓸 수 있다. 이 설정에 대한 가본값은 컨텐스트에 좌우되는데, 선호되는 설정값은 ON으로, 인덱스된 뷰(Indexed Views)와 계산된 열(computed column)에 대한 인덱스를 사용하기 위해서는 이 값이 반드시 ON으로 설정되어야 한다. 그러므로, 이 방법이 가장 좋은 해결책은 아니지만, 경고메시지를 보는게 싫을 경우에는, 다음과 같이 사용 가능하다.:

 CREATE PROCEDURE general_select @tblname nvarchar(127), @key key_type, @debug bit = 0 AS DECLARE @sql nvarchar(4000) SET @sql = 'SET QUOTED_IDENTIFIER OFF SELECT col1, col2, col3 FROM ' + @tblname + ' WHERE keycol = "' + @key + '"' IF @debug = 1 PRINT @sql EXEC(@sql)

두가지 다른 인용 부호가 쓰였으므로, 해당 코드의 가독성이 높아진다. SQL 문장을 위해 작은 따옴표가 쓰였고, 포함된 문자열 리터럴로 큰 따옴표가 사용되었다.

SQL injection에 대해 보호되지 못하므로, 이 방식은 sp_executesql와 quotename()을 쓰는것 보다는 좋지 못한 방법이다. 그러나 sysadmin 을 위한 작업인 관계로 SQL injection이 문제되지 않을 경우에는 사용가능하며, SQL 6.5 환경에서는 아마도 최선의 방법일 것이다.

이 절에서 제시된 가이드라인을 따르더라도, SQL 코드에 동적 SQL을 사용함으로써 야기되는 복잡성은 상당하다. 따라서, 사용하기 전에 반드시 시용해야 하는지 재고해보기 바란다는 말로 끝을 맺겠다.

동적 SQL을 사용(하지 말아야)하는 일반적인 경우 (Common Cases when to (Not) Use Dynamic SQL)

SQL 서버에 관한 여러 뉴스그룹에서, 거의 매일 간단한 예와 함께 동적 SQL을 사용하라는 답변을 받는 사람들이 종종 있다. 하지만, 답변하는 사람들 조차도 권한 및 캐싱에 관련된 숨겨진 의미를 말해주는 것을 잊곤 한다. 많은 경우에 이런 질문들에 대해 동적 SQL이 유일한 해법이기는 하지만, 실제로는 완전히 다른 – 그러고 훨씬 더 좋은 – 해결책이 존재하는 경우도 있다.

이 절에서는 동적 SQL을 사용할 수 있는 몇가지 경우와, 동적 SQL이 적절한 해결책인 경우에 대해 살펴볼 것이다. 그리고, 다른 한편으로 동적 SQL이 좋지 못한 선택인 경우에 대해서도 살펴볼 것이다.

select * from @tablename

일반적인 질문은 왜 다음 쿼리가 작동하지 않는지에 관한 것이다:

 CREATE PROCEDURE my_proc @tablename sysname AS SELECT * FROM @tablename

이러한 경우에 동적 SQL을 이용하여 해결할 수 있다는 것을 이미 알고 있지만, 이런 식의 저장 프로시저의 사용은 의미없는 일이라는 것도 또한 알고 있다. 만약 SQL 프로그래밍을 이렇게 한다면, 저장 프로시저를 사용하기 위해 골치아파할 필요가 전혀 없다.

사람들이 이러한 작업을 하고 싶어하는데는 몇가지 이유가 있어 보인다. C++, VB등 다른 프로그래밍 언어에서의 경험이 있으나 SQL 프로그래밍이 처음인 사람들이 보통 이런 식으로 작업을 많이한다. (※ 역주 : ASP개발자들도 마찬가지죠 ^^;) 테이블 이름을 매개변수로 사용하는 것은 재사용 가능한 범용 코드(Generic Code)를 만들기 위해서, 그리고 유지보수 편의성을 높힌다는 측면에서 환영할만한 방식이다.

그러나 데이타베이스 개체에 대해서는, 이 오래된 진리가 통하지 않는다. 개발자는 각각의 테이블과 칼럼들을 유일하고 고정적인 개체로 보아야 한다. 왜 그럴까? 실행계획을 세울 때, 각각의 테이블은 고유의 통계값과 추정치를 가지고 있으며, SQL 서버에서 이러한 값은 상호 교환 가능한 값이 아니다. 복잡한 데이타 모델에서는, 현재 무엇이 사용되고 있는 가를 파악하는 것이 중요하다. 테이블 이름과 칼럼이름을 매개변수로 사용한다면, 이러한 관계를 파악하기기 어려워 진다.

코딩하면서 타이프하는 수고를 덜기 위한 목적으로 이런 식의 작업이 하고 싶다면(SELECT * 같은 코드는 실제 생산환경에서 사용되어서는 안된다는 것을 기억하라), 그것은 잘못된 선택이다. 이런 경우에는 서로 유사하더라도, 이름이 다른 10개 혹은 20개의 저장 프로시저를 작성하는 것이 훨씬 더 좋은 방법이다.

만약 SQL 문이 너무 복잡해서, 서로 다른 테이블들이 사용되더라도 한 군데서 관리하는 것이 유지보수 측면에 상당한 장점이 있다면, 고려될 수 있는 다른 실용적인 방법이 있다 : C/C++과 같은 전처리기를 사용하는 것이다. 테이블당 하나의 프로시저가 존재하더라도, 코드는 하나의 파일로 만들 수 있다.

select * from sales + @yymm

앞에서 든 예의 변형에 해당한다. 차이점은 앞 절에서는 유한한 갯수의 테이블이 있는 것으로 가정했다는 것이다. 만약 테이블들이 동적으로 생성되는 시스템이라면 어떻게 할까? 예를 들어 판매 자료를 위한 테이블이 매달 생성된다면? 이런 경우에, 테이블당 하나의 저장 프로시저를 생성시킨다는 것은 전처리기를 사용하더라도 사실상 불가능하게 된다.

그렇다면, 다른 대안이 없으므로 그냥 동적 SQL을 사용해야 할까? 아니다. 되돌아가서 이 상황을 다시한번 살펴보자. 사실 처음부터 잘못된 접근법이 사용되었다. 데이타 모델에 명백한 결함이 존재하는데, 월별로 하나의 테이블을 사용하는 것은 Access를 사용하는 시스템 혹은 파일 데이타 시스템에서 성능을 향상시키기 위해 사용가능한 방법이다. SQL 서버 혹은 기타 고급 RDBMS에서 이렇게 해야할 이유는 거의 존재하지 않는다. SQL 서버 혹은 그 경쟁제품은 막대한 양의 데이타를 처리하고 그 데이타를 키를 이용하여 효율적으로 관리하기 위해 고안되었다. 연(year) 혹은 월(month)은 sales 테이블의 PK(Primary Key)를 구성하는 요소일 뿐이다.

만약, 선임자로부터 이러한 시스템을 인계받은 경우에는, 리모델링을 위해 막대한 비용이 필요할 경우도 있다. (하지만 동적 SQL을 사용하므로써 발생되는 복잡한 코드에 소요되는 비용 또한 무시하지 못한다.) 만약 새로운 시스템을 개발하고 있다면, 동적으로 생성되는 테이블에 대해서는 잊어버려라. 그러한 테이블에 접근하거나 업데이트하기 위한 코드가 상당히 지저분해 질 것이다. 이를 테면 전자상거래 시스템에서 각 장바구니당 하나의 테이블을 생성시키는 것처럼 이러한 테이블을 자주 생성시킨다면, 시스템 테이블에 핫 스폿(※ 역주 : Hot Spot은 많은 Query들이 동시에 동일한 영역의 디스크에 데이터를 읽거나 쓰려고 하는 경우에 발생합니다. 이는 하드 디스크가 동시에 처리할 수 있는 것보다 많은 디스크 I/O 요청들을 받게 되기 때문에, 디스크 I/O 병목현상(Bottleneck)을 유발하게 됩니다. 참고: KB 601427)을 유도해 성능에 악영향을 미칠 수도 있다.

수백만개의 데이타가 있는데, 한 테이블에 모든 데이타를 저장해두면, 데이타베이스가 작동하지 않을꺼야 라며 아직도 수긍하지 못하고 궁시렁거릴 독자가 있을 것이다. 좋다. 테이블에 정말로 많은 행(rows)이 존재한다고 치자. 신경쓸 일이 많지? 그치만, 그건 수백만개의 데이타때문이 아니라, SQL 서버 관리를 위해 매일 해야 하는 당연한 업무이다. (인덱스가 현명하게 정의되었다고 가정한 것이다.) 일억개 이상의 행이 존재한다면, 고려해야할 다른 문제가 생긴다. 이런 목적을 위해, SQL 2000은 분할 뷰(partitioned views) 혹은 분산분할 뷰(distributed partitioned views)와 같은 몇가지 특성을 지원한다. 분할 뷰 혹은 분산분할 뷰를 이용하면, 큰 데이타 집합을 몇개의 테이블로 나눌 수 있고, 마치 하나의 테이블처럼 접근할 수 있게 해준다. (주의 : 정확한 표현을 위해서는 행의 갯수가 아닌 테이블의 크기(total size)에 대해 언급해야 한다. 물론 테이블의 크기는 행의 평균 크기와 밀접한 관계가 있다.)

update tbl set @colname = @value where keycol = @keyval

이 경우는, 실행시간에 선택되는 칼럼에 대한 update가 필요한 경우이다. 위의 T-SQL은 문법에 어긋나지 않지만, 실제 일어나는 일은 테이블에서 keycol의 값이 @keyval인 행들의 @value값이 변수 @colname에 대입되는 것 뿐이다.(※ 역주 : 실제로 실행시켜보면 에러메시지가 표시되지는 않지만, 테이블의 해당 레코드에 대한 update가 수행되는 것이 아니라, @colname 변수에 할당된 값이 update될 뿐입니다.)

이 경우에 동적 SQL을 사용하려면 사용자는 테이블에 대한 Update 권한을 갖고 있을 것이 요구된다. 이런 권한 설정은 가볍게 볼 수 있는 문제가 아니며 가능하면 피해야 하는 구성이다. 여기에는 상당히 간단한 해결책이 존재한다.:

 UPDATE tbl SET col1 = CASE @colname WHEN 'col1' THEN @value ELSE col1 END, col2 = CASE @colname WHEN 'col2' THEN @value ELSE col2 END, ...

Case에 익숙하지 않다면, 온라인 도움말을 참조하기 바란다. Case는 SQL의 상당히 강력한 특징중 하나이다.

여기서 왜 사람들이 이런 식의 작업을 하고 싶어하는지 살펴보자. 아마도 테이블이 다음과 같은 구조를 갖고 있어서일 것이다.:

 CREATE TABLE products (prodid prodid_type NOT NULL, prodname name_type NOT NULL, ... sales_1 money NULL, sales_2 money NULL, ... sales_12 money NULL, PRIMARY KEY (prodid))

이 경우에는 테이블을 분리하여 자식 테이블의 sales_n 칼럼을 이용하는 것이 보다 합리적이다.:

 CREATE TABLE product_sales (prodid prodid_type NOT NULL, month tinyint NOT NULL, sales money NOT NULL, PRIMARY KEY (prodid, month))

select * from @dbname + '..tbl'

이 경우는 테이블이 동적으로 결정되는 다른 데이타베이스에 있는 경우이다. 이런 작업방식에는 여러가지 이유가 있으며, 왜 이렇게 작업해야 하는가 하는 이유에 따라 해결책이 다르다.

다른 데이타베이스에서의 데이타 획득 만약 응용프로그램에서 사용하는 데이타가 어떤 이유로 2개이상의 데이타베이스에 분산되어 있다면, 데이타베이스 이름을 코드에 직접 참조시켜 고생할 필요가 없다. 왜냐하면, 테스트 환경에서 같은 서버에 존재하는 데이타베이스 이름이 실제 환경에서는 다른 서버에 존재할 수도 있기 때문이다. 이런 경우에는 설정 테이블에 다른 데이타베이스의 이름을 넣어두고 동적 SQL을 활용하는 것도 좋은 아이디어이지만, 다른 해결책 역시 존재한다. 만약 다른 데이타베이스에 대한 작업이 해당 저장프로시저 내에서 가능하다면, 다음과 같은 할 수 있다:

 SET @sp = @dbname + '..some_sp' EXEC @ret = @sp @par1, @par2...

저장 프로시저의 이름이 변수 @sp 값에 들어있다.

모든 데이타베이스를 대상으로 작업 이 경우는 아마도 sysadmin 고정서버 역할에 속한 사용자가 수행하는 작업일 것이다. 이런 경우에는 권한문제 혹은 캐쉬에 신경쓸 필요가 없기 때문에, 대개의 경우에 동적 SQL은 적절한 선택이다. 그럼에도 불구하고 다음 예제에서의 sp_MSforeachdb 같은 대안이 존재한다.:

 sp_MSforeachdb 'SELECT ''?'', COUNT(*) FROM sysobjects'

추측하는 바와 같이, sp_MSforeachdb는 동적 SQL 을 내부적으로 사용하므로, 개발자가 일일이 루프(loop) 코드를 작성하지 않아도 되는 장점이 있다. 덧붙여 말하고 싶은 것은, sp_MSforeachdb가 온라인 도움말에서 누락된 함수라는 점이다. 온라인 도움말에서 누락된 함수를 사용했을 때 문제가 발생하면 Microsoft로부터의 기술지원을 받을 수 없다.

"마스터" 데이타베이스 간혹, 동일한 테이블 구조를 가진 여러개의 데이타베이스를 관리하는 경우를 볼 수 있다. ASP 서비스(provider service)를 제공하거나, 혹은 고객 각각에 대해 별도의 데이타베이스가 존재하는 경우에 해당하며, 사업적인 이유로 모든 고객에 대한 데이타를 하나의 데이타베이스에 두는 것이 불가능하다. 이런 경우에 관리자들은 모든 데이타베이스에 대한 유지보수가 쉽지 않다고 느끼게 되며, 결과적으로 필요한 모든 프로시저를 저장해둘 "마스터" 데이타베이스가 필요하게 된다. 그러나, "마스터" 데이타베이스에 존재하는 저장프로시저는 동적 SQL을 필요로 하며, 또다른 끔찍한 유지보수 문제를 낳게 된다.

2가지 방법이 있는데, 하나는 SQL 서버에 기본 제공되는 Master 데이타베이스를 이용하여 사용자가 작성한 프로시저를 시스템 프로시저로 설치하는 것이다. (※ 역주: master 데이타베이스에 접두어 "sp_"로 시작하는 사용자 저장프로시저를 작성해 두면, 마치 시스템 저장프로시저처럼 다른 데이타베이스에서 사용가능하게 됩니다.) 하지만, 이렇게 하면 Microsoft에서 기술지원을 기대할 수 없으며, 보안 측면에서의 문제점도 존재하므로, 권장하고 싶은 방법은 아니다.

다른 방법? 저장 프로시저를 각각의 데이타베이스에 설치하고, SQL 개체에 대한 배포 루틴(Rollout Routines)을 개발하는 것이다. 테이블을 변경해야 할 필요가 생길 것으므로, 결국에는 이 방법이 필요하게 될 것이다. 게다가 개별 데이타베이스에 저장 프로시저를 만들어두면, 새로운 버젼으로 업그레이드하기를 꺼리는 고약한 고객들에 대한 대응도 가능해지며, 까다로운 고객을 위해 특정 목적에 맞는 프로시저를 제작하는 것이 가능해진다. 배포 루틴을 적용하는 방법은 설징 유지관리(configuration management)에 관한 주제까지 다루어야 하며, 이 기사의 범위를 벗어난다. 이에 대해서는 2가지 단서만 제공하겠다. SQL Server Resource Kit에 들어있는 Stored Procedure Builder를 사용하면, Visual SourceSafe를 이용하여 SQL 개체를 설치하는 것이 가능해진다. 내 경우에는 고객들에 대한 기술지원을 제공하기 위해 AbaPerls라는 툴을 몇년 동안 개발했으며, http://www.abaris.se/abaperls/에서 구할 수 있다. 이 툴은 프리웨어이다.

select * from tbl where col in (@list)

매우 흔한 질문으로, 동적 SQL을 사용하세요가 또한 일반적인 답변이다. 하지만 이 질문에 대해 동적 SQL을 사용하라는 것은 분명히 잘못된 답변으로, 이런 종류의 Select 권한이 필요하지도 않으며, @list에 많은 요소가 포함될 경우에는, 동적 SQL을 사용할 경우 틀림없이 성능저하를 경험하게 된다.

대안? 사용자 정의함수 혹은 저장프로시저를 사용해서 입력되는 문자열을 테이블에 나누어 저장해라. 이 기사에는 적당한 예가 포함되어 있지 않지만, 또다른 기사인 Arrays and Lists in SQL Server에서 이러한 문제를 다루는 방법과 여러 방법론들의 성능차이에 대한 자료를 제시하겠다. (제시된 방법들중 동적 SQL이 가장 하위에 있다!) 해당 기사는 매우 긴 분량으로, 기사의 첫부분에 각 SQL 버전에 적합한 내용으로 분기할 수 있는 링크를 제시하였다.)

select * from tbl where @condition

다음과 같은 프로시저를 작성하려 한다고 가정해 보자.

 CREATE PROCEDURE search_sp @condition varchar(8000) AS SELECT * FROM tbl WHERE @condition

그냥 관둬라. 이런 작업을 하고 있다면 저장 프로시저를 어중간한 상태로 사용하고 있는 것으로, 개발자가 여전히 클라이언트에서 SQL 코드를 조합하고 있는 것이다. 이 예는 다음 주제와 관련있다.

동적 검색 조건 (Dynamic Search Conditions)

사용자가 광범위한 매개변수로 부터 데이타를 검색하는 것은 드문 경우가 아니다. 입력 매개변수 각각의 조합에 대하여 최적화된 쿼리를 작성해내는 정적인 해결책을 만들어내는 것이 불가능하다는 데에는 논쟁의 여지가 없다. 그리고, 대부분의 프로그래머들이 모든 조건들을 "똑똑한" SQL을 이용하여 하나의 쿼리로 묶어낸 경우에 좋은 효율을 보여주리라고 기대하지도 않는다.

이런 유형의 문제에는 동적 SQL이 분명 더 좋은 해결책이다. 권한 문제만 해결할 수 있다면, 동적 SQL을 사용하는 것이 성능 및 유지비용 보수면에서 더 낫다. 분리된 다른 기사 동적 검색 조건 (Dynamic Search Conditions) 에서 동적 SQL을 사용하는 방법과 사용하지 않고 구현하는 방법에 대한 예를 들어보겠다.

select * from table order by @col

이 경우는 동

'SQL' 카테고리의 다른 글

[펌] 유용한 MS-SQL 사이트.. (0)	2005.12.20
[펌] 「MySQL 5.0」등장, 기업 사용자도 OK! (0)	2005.10.27
[펌] [Oracle]오라클 어드민 팁 (0)	2005.10.11
[mssql] 계층적 구조 표현하기. (0)	2005.10.07
오라클 클라이언트 툴... aquafold.... (0)	2005.09.26

Posted by tornado

|

[펌] [Oracle]오라클 어드민 팁

SQL 2005. 10. 11. 09:42

[Oracle]오라클 어드민 팁

Oracle Administration을 정리하다가 간단히 찾아고 조금이나마 도움이 되시라고 정리해서 올립니다.
너무 단시간에 두서없이 써서 보기도 좋지 않지만 필요하신 분들 심심할때 하나씩
해보세요.(다들 아시는거지만~~)
아래 tips 는 하나의 database에서 작성한 것이 아니므로 각종 정보들(file들의 위치등)이
tip마다 다를 수 있습니다. 각 tip은 개개의 것으로 생각하시고 응용하시기 바랍니다.
혹시 틀린 내용 발견되면 mail주세요. 바로 수정하겠습니다.
편집 이쁘게 못해서 죄송합니다.
나름대로 사연있는 글입니다.
정리하다가 날려먹어서 한 몇일 더 고생해서 작성한겁니다.^^;

님들도 좋은 정보 있으시면 공유하시죠.

================================================================================================
1. DBMS = database(data file & control file & redo log file) +
instance(memory & background processes)
================================================================================================

2. Oracle Architecture Component

================================================================================================

* Oracle Instance 확인 : v$instance

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
IBM

================================================================================================

* datafile들의 경로 및 정보 : v$datafile
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/ora_data/system/system01.dbf
/oracle/ora_data/data/tools01.dbf
/oracle/ora_data/data/rbs01.dbf
/oracle/ora_data/data/temp01.dbf
/oracle/ora_data/data/users01.dbf

================================================================================================

* control file의 경로 및 정보 : v$controlfile;

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle/ora_data/contr1/ora_control1
/oracle/ora_data/contr2/ora_control2

================================================================================================

* logfile의 경로 및 정보 : v$logfile

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oracle/ora_data/redolog_a/redo1a.log
/oracle/ora_data/redolog_b/redo1b.log
/oracle/ora_data/redolog_a/redo2a.log
/oracle/ora_data/redolog_b/redo2b.log
/oracle/ora_data/redolog_a/redo3a.log
/oracle/ora_data/redolog_b/redo3b.log

================================================================================================

* System Global Area 내용을 조회

SQL> select * from v$sga;

NAME VALUE
-------------------- ----------
Fixed Size 108588
Variable Size 27631616
Database Buffers 2252800
Redo Buffers 77824

SQL> show sga

Total System Global Area 30070828 bytes
Fixed Size 108588 bytes
Variable Size 27631616 bytes
Database Buffers 2252800 bytes
Redo Buffers 77824 bytes

================================================================================================

* 현재 수행중인 background process들을 확인

SQL> select paddr,name,description from v$bgprocess where paddr>'00';

PADDR NAME DESCRIPTION
---------------- ----- ----------------------------------------------------------------
070000000139ABC0 PMON process cleanup
070000000139AFD0 DBW0 db writer process 0
070000000139B3E0 LGWR Redo etc.
070000000139B7F0 CKPT checkpoint
070000000139BC00 SMON System Monitor Process
070000000139C010 RECO distributed recovery

SQL> !ps -ef|grep ora|grep

oracle 25148 1 0 19:25:34 - 0:00 ora_reco_IBM
oracle 60576 1 0 19:25:34 - 0:00 ora_smon_IBM
oracle 60782 1 0 19:25:34 - 0:00 ora_pmon_IBM
oracle 70166 1 0 19:25:34 - 0:00 ora_lgwr_IBM
oracle 72248 1 0 19:25:34 - 0:00 ora_ckpt_IBM
oracle 84918 1 0 19:25:34 - 0:00 ora_dbw0_IBM

================================================================================================

* 초기화 파라미터 파일 : init.ora

================================================================================================

* database log 모드 확인

SQL> connect internal
Connected.

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/8.1.6/dbs/arch
Oldest online log sequence 20
Current log sequence 22

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

================================================================================================

3. Managing an Oracle Instance

================================================================================================

단계별 :
shutdown : oracle이 내려가 있는 상태
nomount : instance started(SGA, B.G process를 시작 init.ora에서 읽어서)
alert, trace file open
- 이 단계에서 할 수 있는 것은
a. db creation
- 이 상태에서도 볼수있는 view
v$parameter
v$dga
v$option
v$process
v$session
v$version
v$instance

mount : control file opened for this instance
- 이 단계에서 할 수 있는 것은 control file의 내용을 변경하는것
a. archivelog mode로 변환
b. data file/redo log file rename시
c. recovery시

- SQL>alter database open read only;
로 하게되면 data file에 writing을 허용 안함.

open : control file에 기술된 모든 files open

================================================================================================

* parameter 변경 종류

a. init.ora 에서 변경
b. alter session set ~
c. alter system set ~ => shutdown 될때까지 변경된것 유효
alter system deffered set ~ => 현재 session에서만 변경된것 유효

================================================================================================

* 특정 session 죽이기

SQL> select sid, serial#,username,status from v$session; => (특정 user는 where username='SCOTT'로)
SID SERIAL# USERNAME STATUS
---------- ---------- ------------------------------ --------
1 1 ACTIVE
2 1 ACTIVE
3 1 ACTIVE
4 1 ACTIVE
5 1 ACTIVE
6 1 ACTIVE
7 1 SYS ACTIVE

SQL> alter system kill session '7,3' -- 7은 sid, 3은 serial#

================================================================================================

* alert file 과 trace file
- alert file은 꼭 1개, 중요한사건,시간순으로 (startup,shutdown,recovery)
- trace file은 여러개 가능, background process는 background_dump_dest에 생기고 server process는
user_dump_dest에 생성된다.

================================================================================================

4. Creating a Database

================================================================================================

* Create a Database Manually

a. OS Environment setting

.profile에 ORACLE_HOME,ORACLE_SID,ORA_NLS33,PATH,(ORACLE_BASE) 등을 편집한다.

ex)
DISPLAY=swsvrctr:0.0
ORACLE_HOME=/oracle/app/oracle/product/8.1.7
PATH=$ORACLE_PATH/bin:/usr/ccs/bin:$PATH
NLS_LANG=AMERICAN_AMERICA.KO16KSC5601
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
ORACLE_SID=IBM

b. init.ora file을 copy하고 편집한다.
file
db_name=KYS
control_files = (/home/oracle/data02/control/control01.ctl,/home/oracle/data02/control/control02.ctl)
db_block_size = 8192

기본적으로 위 두개 parameter외에
rollback_segments=(rbs1,rbs2,..) =>나중에 rollback segment생성후 DB start시 Online되는 rbs지정
background_dump_dest=/home/oracle/data02/bdump
user_dump_dest=/home/oracle/data02/udump
core_dump_dest=/home/oracle/data02/cdump

c. Starting the Instance

SQL> startup nomount
SQL> startup nomount pfile=initKYS.ora

SQL> create database KYS
2 maxlogfiles 5
3 maxlogmembers 5
4 maxdatafiles 100
5 maxloghistory 100
6 logfile
7 group 1 ('/home/oracle/data02/redolog/log1a.rdo','/home/oracle/data02/redolog2/log1b.rdo') size 1m,
8 group 2 ('/home/oracle/data02/redolog/log2a.rdo','/home/oracle/data02/redolog2/log2b.rdo') size 1m
9 datafile
10 '/home/oracle/data02/data/system01.dbf' size 50m autoextend on
11 character set "KO16KSC5601";

일단 여기까지 database는 생성이 되었다.
이후부터는 추가적인 작업이다.

d. 추가 system rollback segment 생성

SQL> create rollback segment r0 tablespace system
2 storage (initial 16k next 16k minextents 2 maxextents 10);

e. rollback sement online

SQL> alter rollback segment r0 online;

f. rollback segment tablespace 생성 & datafile 저장위치, 크기 및 초기값 지정

SQL> create tablespace rbs
2 datafile '/home/oracle/data02/data/rbs01.dbf' size 300m
3 default storage(
4 initial 4M
5 next 4M
6 pctincrease 0
7 minextents 10
8 maxextents unlimited);

g. rollback segment 생성

SQL> create rollback segment r01 tablespace rbs
2 storage (minextents 10 optimal 40M);
SQL> create rollback segment r02 tablespace rbs
2 storage (minextents 10 optimal 40M);
SQL> create rollback segment r03 tablespace rbs
2 storage (minextents 10 optimal 40M);
SQL> create rollback segment r04 tablespace rbs
2 storage (minextents 10 optimal 40M);

h. rollback segment online

SQL> alter rollback segment r01 online;
SQL> alter rollback segment r02 online;
SQL> alter rollback segment r03 online;
SQL> alter rollback segment r04 online;

i. 추가 system rollback segment off-line 및 삭제

SQL> alter rollback segment r0 offline;
SQL> drop rollback segment r0;

j. sorting 작업시 필요한 temporary tablespace 생성 & datafile 저장 위치, 크기 및 초기값 지정

SQL> create tablespace temp
2 datafile '/home/oracle/data02/data/temp01.dbf' size 300 temporary
3 default storage(
4 initial 4M
5 next 4M
6 maxextents unlimited
7 pctincrease 0);

k. 추가 tablespace 생성 & data file 저장 위치 및 크기 지정

SQL> create tablespace tools
2 datafile '/home/oracle/data02/data/tools.dbf' size 50m
3 default storage(
4 maxextents 505
5 pctincrease 0);

SQL> create tablespace users
2 datafile '/home/oracle/data02/data/user01.dbf' size 30M
3 default storage(
4 maxextents 505
5 pctincrease 0);

l. 작업 환경에서 추가적으로 필요한 tablespace는 위의 방법으로 생성한다.

================================================================================================

5. Data Dictionary and Standard Package

================================================================================================

* database 생성후 돌려줘야 할 script

$ORACLE_HOME/rdbms/admin/catalog.sql ==> dictionary views, export utility views 생성
$ORACLE_HOME/rdbms/admin/catproc.sql ==> procedures, functions 생성
$ORACLE_HOME/rdbms/admin/catdbsyn.sql ==> synonyms 생성

================================================================================================

* Dictionary list 확인

SQL> col table_name format a30
SQL> col comments format a45
SQL> set pages 800
SQL> spool dictionary.lst
SQL> select * from dictionary order by 1 ==> 전체 dictionary의 list를 볼 수 있다.
SQL> spool off
SQL> ed sictionary.lst
SQL> select * from dictionary where table_name like '%TABLE%'; ==> table 관련 dictionary
SQL> select * from dictionary where table_name like '%INDEX%'; ==> index 관련 dictionary

================================================================================================

* 유용한 dictionary

TABLE_NAME COMMENTS
------------------------------ ---------------------------------------------
DBA_USERS Information about all users of the database
DBA_TABLESPACES Description of all tablespaces
DBA_DATA_FILES Information about database data files
DBA_FREE_SPACE Free extents in all tablespaces
DBA_OBJECTS All objects in the database
DBA_SEGMENTS Storage allocated for all database segments
DBA_ROLLBACK_SEGS Description of rollback segments
DBA_EXTENTS Extents comprising all segments in the database
DBA_TABLES Description of all relational tables in the d
atabase
DBA_INDEXES Description for all indexes in the database
DBA_VIEWS Description of all views in the database
DBA_TRIGGERS All triggers in the database
DBA_SOURCE Source of all stored objects in the database

================================================================================================

* sample Query

SQL> select username,default_tablespace,temporary_tablespace from dba_users;
SQL> select tablespace_name,bytes,file_name from dba_data_files;
SQL> select tablespace_name,count(*),sum(bytes) from dba_free_space
2 group by tablespace_name;

================================================================================================

6. Maintiaining the Contorol File

================================================================================================

* Control File 리스트 조회

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/data01/oradata/IBM/control01.ctl
/home/oracle/data01/oradata/IBM/control02.ctl
/home/oracle/data01/oradata/IBM/control03.ctl

================================================================================================

* Control File 을 하나 추가해보자

a. database shutdown
SQL> shutdown immediate

b. control file 복사(os상 물리적인 복사)
/home/oracle/data01/oradata/IBM> cp control03.ctl control04.ctl ==> 실제는 다른 disk로 복사해야함
문제발생을 대비해 분리하는것임.

c. Parameter File 편집
control_files = ("/home/oracle/data01/oradata/IBM/control01.ctl",
"/home/oracle/data01/oradata/IBM/control02.ctl",
"/home/oracle/data01/oradata/IBM/control03.ctl",
"/home/oracle/data01/oradata/IBM/control04,ctl")

d. database startup & 확인
SQL> startup
SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/home/oracle/data01/oradata/IBM/control01.ctl
/home/oracle/data01/oradata/IBM/control02.ctl
/home/oracle/data01/oradata/IBM/control03.ctl
/home/oracle/data01/oradata/IBM/control04.ctl ==> 하나 더 추가되었지요...(실제는 다른disk로)

================================================================================================

7. Multiplexing Redo Log Files

================================================================================================

* Redo Log File 리스트 조회

SQL> select group#,sequence#,bytes,members,status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- --------------------------------
1 862 512000 1 CURRENT
2 860 512000 1 INACTIVE
3 861 512000 1 INACTIVE

SQL> select * from v$logfile;

GROUP# STATUS MEMBER
---------- -------------- --------------------------------------------------
1 /home/oracle/data01/oradata/IBM/redo03.log
2 /home/oracle/data01/oradata/IBM/redo02.log
3 /home/oracle/data01/oradata/IBM/redo01.log

================================================================================================

* Log Group 추가(기존 로그 파일과 동일한 사이즈로)

SQL> alter database add logfile
2 '/home/oracle/data01/oradata/IBM/redo04.log' size 200k;

SQL> select group#,sequence#,bytes,members,status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- --------------------------------
1 862 512000 1 CURRENT
2 860 512000 1 INACTIVE
3 861 512000 1 INACTIVE
4 0 204800 1 UNUSED

SQL> select * from v$logfile;

GROUP# STATUS MEMBER
---------- -------------- --------------------------------------------------
1 /home/oracle/data01/oradata/IBM/redo03.log
2 /home/oracle/data01/oradata/IBM/redo02.log
3 /home/oracle/data01/oradata/IBM/redo01.log
4 /home/oracle/data01/oradata/IBM/redo04.log

================================================================================================

* Log Group 별 멤버 파일 추가 ==> backup 시 risk줄이기 위해 실제는 다른 disk에 해야함.

SQL> alter database add logfile member
2 '/home/oracle/data01/oradata/IBM/redo01b.log' to group 1,
3 '/home/oracle/data01/oradata/IBM/redo02b.log' to group 2,
4 '/home/oracle/data01/oradata/IBM/redo03b.log' to group 3,
5 '/home/oracle/data01/oradata/IBM/redo04b.log' to group 4;

================================================================================================

* 확인

SQL> !ls /home/oracle/data01/oradata/IBM/*.log
/home/oracle/data01/oradata/IBM/redo01.log /home/oracle/data01/oradata/IBM/redo03.log
/home/oracle/data01/oradata/IBM/redo01b.log /home/oracle/data01/oradata/IBM/redo03b.log
/home/oracle/data01/oradata/IBM/redo02.log /home/oracle/data01/oradata/IBM/redo04.log
/home/oracle/data01/oradata/IBM/redo02b.log /home/oracle/data01/oradata/IBM/redo04b.log

SQL> select group#,sequence#,bytes,members,status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- --------------------------------
1 862 512000 2 CURRENT
2 860 512000 2 INACTIVE
3 861 512000 2 INACTIVE
4 0 204800 2 UNUSED ==> 아직 한번도 사용되지 않음

SQL> select * from v$logfile;

GROUP# STATUS MEMBER
---------- -------------- --------------------------------------------------
1 /home/oracle/data01/oradata/IBM/redo03.log
2 /home/oracle/data01/oradata/IBM/redo02.log
3 /home/oracle/data01/oradata/IBM/redo01.log
4 /home/oracle/data01/oradata/IBM/redo04.log
1 INVALID /home/oracle/data01/oradata/IBM/redo01b.log
2 INVALID /home/oracle/data01/oradata/IBM/redo02b.log
3 INVALID /home/oracle/data01/oradata/IBM/redo03b.log
4 INVALID /home/oracle/data01/oradata/IBM/redo04b.log

==> 현재 사용되고 있는 log group 은 group 1이고 나중에 추가한 member들은 invalid 한 상태이다.
강제로 log switch를 일으켜서 valid하게 바꾸자.

SQL> alter system switch logfile;

SQL> select group#,sequence#,bytes,members,status from v$log;

GROUP# SEQUENCE# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- --------------------------------
1 862 512000 2 ACTIVE
2 860 512000 2 INACTIVE
3 861 512000 2 INACTIVE
4 863 204800 2 CURRENT ==> unused에서 바뀜.

SQL> select * from v$logfile;

GROUP# STATUS MEMBER
---------- -------------- --------------------------------------------------
1 /home/oracle/data01/oradata/IBM/redo03.log
2 /home/oracle/data01/oradata/IBM/redo02.log
3 /home/oracle/data01/oradata/IBM/redo01.log
4 /home/oracle/data01/oradata/IBM/redo04.log
1 INVALID /home/oracle/data01/oradata/IBM/redo01b.log
2 INVALID /home/oracle/data01/oradata/IBM/redo02b.log
3 INVALID /home/oracle/data01/oradata/IBM/redo03b.log
4 /home/oracle/data01/oradata/IBM/redo04b.log ==> valid하게 바뀜

================================================================================================

Log Miner

================================================================================================

* Parameter File 의 utl_file_dir 편집

a. 확인

SQL> select name,value from v$parameter
2 where name='utl_file_dir';

NAME VALUE
-------------------- ------------------------------
utl_file_dir

SQL> !mkdir $ORACLE_HOME/LOG

b. LogMiner사용을 위해 init.ora file 편집
utl_file_dir=/oracle/app/oracle/product/8.1.7/LOG

c. restart

SQL> shutdown immediate
SQL> startup

확인
SQL> select name,value from v$parameter
2 where name='utl_file_dir';

NAME VALUE
-------------------- ------------------------------
utl_file_dir /oracle/app/oracle/product/8.1.7/LOG ==> LogMiner 준비를 위한 parameter set

d. LogMiner setting - 반드시 트랜잭션의 첫번째 명령이어야 함

SQL> commit;
SQL> exec dbms_logmnr_d.build('v817dict.ora','/oracle/app/oracle/product/8.1.7/LOG');
BEGIN dbms_logmnr_d.build('v817dict.ora','/oracle/app/oracle/product/8.1.7/LOG'); END;

*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
ORA-06512: at line 1

SQL> !ls $ORACLE_HOME/LOG

SQL> exec dbms_logmnr.add_logfile('/home/oracle/data01/oradata/IBM/redo01.log',DBMS_LOGMNR.NEW);
SQL> exec dbms_logmnr.add_logfile('/home/oracle/data01/oradata/IBM/redo02.log',DBMS_LOGMNR.ADDFILE);
SQL> exec dbms_logmnr.add_logfile('/home/oracle/data01/oradata/IBM/redo03.log',DBMS_LOGMNR.ADDFILE);
SQL> exec dbms_logmnr.add_logfile('/home/oracle/data01/oradata/IBM/redo04.log',DBMS_LOGMNR.ADDFILE);
SQL> exec dbms_logmnr.start_logmnr('/oracle/app/oracle/product/8.1.7/LOG/v817dict.ora');

e. 트랜잭션 수행

SQL> descc scott.dept
SQL> select * from scott.dept;
SQL> insert into scott.dept values(99,'test','test');
SQL> update scott.dept set loc='TEST' where deptno=99;
SQL> commit;

f. log miner 정보 분석
SQL> select timestamp,username,sql_redo from v$logmnr_contents
2 where seg_name='DEPT';

g. 로그마이닝 종료

SQL> exec dbms_logmnr.end_logmnr;

================================================================================================

8. Managing TableSpace and Data Files

================================================================================================

* tablespace와 datafile 조회

SQL> col tablespace_name format a15
SQL> col file_name format a45
SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME STATUS CONTENTS
--------------- ------------------ ------------------
SYSTEM ONLINE PERMANENT
TOOLS ONLINE PERMANENT
RBS ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
INDX ONLINE PERMANENT
DRSYS ONLINE PERMANENT

SQL> select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf
DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf
USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf
INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf
RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf
TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf
SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf

================================================================================================

* tablespace 생성 및 사이즈 변경

SQL> create tablespace data05
2 datafile '/home/oracle/data01/oradata/IBM/data05_01.dbf' size 1m;

Tablespace created.

1m 짜리 datafile 하나를 가진 tablespace data05를 추가하였다. 확인.

SQL> select tablespace_name,bytes,file_name from dba_data_files
2 where tablespace_name='DATA05';

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf

tablespace가 부족할때 늘리는 방법은 두가지가 있다.
하나는 datafile을 추가하는 방법이고 다른하나는 datafile의 size를 늘리는 방법이다.

a. datafile을 하나 추가해보자.

SQL> alter tablespace data05
2 add datafile '/home/oracle/data01/oradata/IBM/data05_02.dbf' size 1m;

SQL> select tablespace_name,bytes,file_name from dba_data_files
2 where tablespace_name='DATA05';

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_02.dbf

제대로 추가되었다.

b. 그렇다면 하나의 사이즈를 변경해보자.

SQL> alter database datafile
2 '/home/oracle/data01/oradata/IBM/data05_02.dbf' resize 2m;

SQL> select tablespace_name,bytes,file_name from dba_data_files
2 where tablespace_name='DATA05';

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf
DATA05 2097152 /home/oracle/data01/oradata/IBM/data05_02.dbf

2m로 제대로 변경이 되었다.

다시 원상복구
SQL> alter database datafile
2 '/home/oracle/data01/oradata/IBM/data05_02.dbf' resize 1m;

전체를 다시 확인해보자

SQL> select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf
DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf
USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf
INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf
RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf
TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf
SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_01.dbf
DATA05 1048576 /home/oracle/data01/oradata/IBM/data05_02.dbf

================================================================================================

* tablespace 삭제 : Dictionary에서만 삭제되는것으로 실제 물리적으로 파일은 os command로 삭제해야한다.

SQL> select tablespace_name from dba_tablespaces
2 where tablespace_name like 'DATA%'
3 minus
4 select distinct tablespace_name from dba_segments;

TABLESPACE_NAME
---------------
DATA05

SQL> drop tablespace data05;
SQL> select tablespace_name,bytes,file_name from dba_data_files;

TABLESPACE_NAME BYTES FILE_NAME
--------------- ---------- ---------------------------------------------
TOOLS 10485760 /home/oracle/data01/oradata/IBM/tools01.dbf
DRSYS 20971520 /home/oracle/data01/oradata/IBM/drsys01.dbf
USERS 20971520 /home/oracle/data01/oradata/IBM/users01.dbf
INDX 20971520 /home/oracle/data01/oradata/IBM/indx01.dbf
RBS 52428800 /home/oracle/data01/oradata/IBM/rbs01.dbf
TEMP 20971520 /home/oracle/data01/oradata/IBM/temp01.dbf
SYSTEM 283115520 /home/oracle/data01/oradata/IBM/system01.dbf

SQL> !ls //home/oracle/data01/oradata/IBM/*.dbf
//home/oracle/data01/oradata/IBM/data05_01.dbf //home/oracle/data01/oradata/IBM/system01.dbf
//home/oracle/data01/oradata/IBM/data05_02.dbf //home/oracle/data01/oradata/IBM/temp01.dbf
//home/oracle/data01/oradata/IBM/drsys01.dbf //home/oracle/data01/oradata/IBM/tools01.dbf
//home/oracle/data01/oradata/IBM/indx01.dbf //home/oracle/data01/oradata/IBM/users01.dbf
//home/oracle/data01/oradata/IBM/rbs01.dbf

dictionary에서는 삭제되었으나 여전히 물리적인 file은 존재한다. 삭제하면 된다.
(tablespace생성시에는 file이 그냥 생성되나 삭제시는 dictionary삭제후 강제로 삭제해줘야 한다.)

SQL> !rm /home/oracle/data01/oradata/IBM/data05*

================================================================================================

* tablespace 의 online/offline, read only/read write

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
TOOLS ONLINE
RBS ONLINE
TEMP ONLINE
USERS ONLINE
INDX ONLINE
DRSYS ONLINE

7 rows selected.

SQL> select tablespace_name from dba_tables
2 where table_name ='DEPT' and owner='SCOTT';

TABLESPACE_NAME
------------------------------------------------------------
SYSTEM

default로 생성시 scott user의 data가 system tablespace에 생성되었으나 이렇게 쓰면 안된다.
하나 생성해볼까?

SQL> create tablespace data01
2 datafile '/home/oracle/data01/oradata/IBM/data01.dbf' size 1m;

Tablespace created.

SQL> connect scott/tiger
Connected.

SQL> create table dept_tmp tablespace data01
2 as select * from dept;

SQL> connect internal
Connected.
SQL> select tablespace_name from dba_tables
2 where table_name ='DEPT_TMP' and owner='SCOTT';

TABLESPACE_NAME
------------------------------------------------------------
DATA01

SQL> select * from scott.dept_tmp;

DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

제대로 된다. 그렇다면 tablespace를 offline으로...

SQL> alter tablespace data01 offline;
SQL> select tablespace_name, status from dba_tablespaces
2 where tablespace_name='DATA01';

TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
DATA01 OFFLINE

SQL> select * from scott.dept_tmp;
select * from scott.dept_tmp
*
ERROR at line 1:
ORA-00376: file 8 cannot be read at this time
ORA-01110: data file 8: '/home/oracle/data01/oradata/IBM/data01.dbf'

위와 같이 error가 발생한다.
다시 online으로 해두자.
SQL> alter tablespace data01 online;

이번엔 read only로 변경
SQL> alter tablespace data01 read only;

SQL> select tablespace_name, status from dba_tablespaces
2 where tablespace_name='DATA01';

TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
DATA01 READ ONLY

변경되었다.

SQL> insert into scott.dept_tmp values(80,'new_dept','new_loc');
insert into scott.dept_tmp values(80,'new_dept','new_loc')
*
ERROR at line 1:
ORA-00372: file 8 cannot be modified at this time
ORA-01110: data file 8: '/home/oracle/data01/oradata/IBM/data01.dbf'

insert같은 DML(write성) 수행시 위와 같은 error 발생

원상복구
SQL> alter tablespace data01 read write;
SQL> insert into scott.dept_tmp values(80,'test','test');

제대로 된다.
================================================================================================

9. Storage Structure and Relationships

================================================================================================

* Extent 정보 조회 : 다음과 같이 각종 extent,segment 등의 정보를 조회해 볼 수 있다.

SQL> col owner format a10
SQL> col segment_type format a12
SQL> col segment_name format a12
SQL> col tablespace_name format a10

SQL> select owner,segment_name,segment_type, tablespace_name,max_extents,extents,pct_increase
2 from dba_segments
3 where max_extents - extents <= 10 and owner !='SYS';

no rows selected

SQL> select owner,segment_name,segment_type, tablespace_name,max_extents,extents,pct_increase
2 from dba_segments
3 where owner='SCOTT';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE MAX_EXTENTS EXTENTS PCT_INCREASE
---------- ------------ ------------ ---------- ----------- ---------- ------------
SCOTT DEPT_TMP TABLE DATA01 505 1 50
SCOTT DEPT TABLE SYSTEM 2147483645 1 50
SCOTT EMP TABLE SYSTEM 2147483645 1 50
SCOTT BONUS TABLE SYSTEM 2147483645 1 50
SCOTT SALGRADE TABLE SYSTEM 2147483645 1 50
SCOTT PK_DEPT INDEX SYSTEM 2147483645 1 50
SCOTT PK_EMP INDEX SYSTEM 2147483645 1 50

SQL> select segment_name,extents, initial_extent, next_extent,pct_increase
2 from dba_segments
3 where owner='SCOTT' and segment_name='EMP';

SEGMENT_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
------------ ---------- -------------- ----------- ------------
EMP 1 65536 65536 50

SQL> select segment_name,extent_id,block_id,bytes,blocks
2 from dba_extents
3 where owner='SCOTT' and segment_name='EMP';
4 order by 2,3;

SEGMENT_NAME EXTENT_ID BLOCK_ID BYTES BLOCKS
------------ ---------- ---------- ---------- ----------
EMP 0 33945 65536 8

================================================================================================

* Free space 관리

tablespace내에 free space를 먼저 확인해본다.
SQL> select * from dba_free_space
2 where tablespace_name ='DATA01' order by 1,2,3;

TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
DATA01 8 7 999424 122 8

테이블을 여러개 생성해보자.
SQL> create table scott.dept2 tablespace data01 as select * from scott.dept;
SQL> create table scott.dept3 tablespace data01 as select * from scott.dept;
SQL> create table scott.dept4 tablespace data01 as select * from scott.dept;
SQL> create table scott.dept5 tablespace data01 as select * from scott.dept;
SQL> create table scott.dept6 tablespace data01 as select * from scott.dept;

SQL> select * from dba_free_space
2 where tablespace_name ='DATA01' order by 1,2,3;

TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
DATA01 8 32 794624 97 8

사용함에 따라 tablespace내 free space 가 줄어듦을 알 수 있다.

SQL> drop table scott.dept2;
drop table dept2
*
ERROR at line 1:
ORA-04098: trigger 'SYS.JIS$ROLE_TRIGGER$' is invalid and failed re-validation
이건 또 뭐야 ? trigger가 걸려있네요...
table drop 을 위해
SQL> alter trigger SYS.JIS$ROLE_TRIGGER$ disable;
drop table scott.dept3; ==> dept4 만 빼고 전부 drop
drop table scott.dept5;
drop table scott.dept6;

SQL> select * from dba_free_space
2 where tablespace_name ='DATA01' order by 1,2,3;

TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
DATA01 8 7 40960 5 8
DATA01 8 32 794624 97 8

tablespace의 free space가 늘긴 했는데 쪼개졌네요..
빈공간을 병합하자
SQL> alter tablespace data01 coalesce;

SQL> select * from dba_free_space
2 where tablespace_name ='DATA01' order by 1,2,3;

TABLESPACE FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
---------- ---------- ---------- ---------- ---------- ------------
DATA01 8 7 40960 5 8
DATA01 8 32 794624 97 8

그래도 두개로 쪼개져 있는 이유는? 중간에 dept4 가 사용하는 space가 coalesce 되지 않았기 때문

SQL> drop table scott.dept4;
SQL> alter tablespace data01 coalesce;

완전히 병합되었다.

================================================================================================

10. Managing Rollback Segments

================================================================================================

* rollback segment의 정보 조회

SQL> col owner format a10
SQL> col segment_name format a12
SQL> col segment_type format a12
SQL> col tablespace_name format a10
SQL> col status format a7

SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents
2 from dba_rollback_segs;

SEGMENT_NAME TABLESPACE STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------ ---------- ------- -------------- ----------- -----------
SYSTEM SYSTEM ONLINE 57344 57344 2
RBS0 RBS ONLINE 524288 524288 8
RBS1 RBS ONLINE 524288 524288 8
RBS2 RBS ONLINE 524288 524288 8
RBS3 RBS ONLINE 524288 524288 8
RBS4 RBS ONLINE 524288 524288 8
RBS5 RBS ONLINE 524288 524288 8
RBS6 RBS ONLINE 524288 524288 8

================================================================================================

* rollback segment 생성

SQL> create rollback segment rbs99
2 tablespace rbs
3 storage(initial 20k next 20k minextents 2 optimal 80k);

Rollback segment created.

SQL> select segment_name,tablespace_name,status,initial_extent,next_extent,min_extents
2 from dba_rollback_segs;

SEGMENT_NAME TABLESPACE STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------ ---------- ------- -------------- ----------- -----------
SYSTEM SYSTEM ONLINE 57344 57344 2
RBS0 RBS ONLINE 524288 524288 8
RBS1 RBS ONLINE 524288 524288 8
RBS2 RBS ONLINE 524288 524288 8
RBS3 RBS ONLINE 524288 524288 8
RBS4 RBS ONLINE 524288 524288 8
RBS5 RBS ONLINE 524288 524288 8
RBS6 RBS ONLINE 524288 524288 8
RBS99 RBS OFFLINE 24576 32768 2

추가되었다. online으로 전환하자.

SQL> alter rollback segment rbs99 online;

SQL> create table emp2 as select * from emp;

SQL> select name,extents,xacts,shrinks,optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn;

NAME EXTENTS XACTS SHRINKS OPTSIZE
--------------------- ----------- ---------- ---------- ----------
SYSTEM 9 0 0
RBS0 8 0 0 4194304
RBS1 8 0 0 4194304
RBS2 8 0 0 4194304
RBS3 8 0 0 4194304
RBS4 8 0 0 4194304
RBS5 8 0 0 4194304
RBS6 8 0 0 4194304
RBS99 2 0 0 81920 ==> extents,xacts의 변화 관찰

SQL> set transaction use rollback segment rbs99;
SQL> update emp2 set hiredate=sysdate;

SQL> select name,extents,xacts,shrinks,optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn;

NAME EXTENTS XACTS SHRINKS OPTSIZE
--------------- ---------- ---------- ---------- ----------
SYSTEM 9 0 0
RBS0 8 0 0 4194304
RBS1 8 0 0 4194304
RBS2 8 0 0 4194304
RBS3 8 0 0 4194304
RBS4 8 0 0 4194304
RBS5 8 0 0 4194304
RBS6 8 0 0 4194304
RBS99 2 1 0 81920 ==> transaction이 시작됨

SQL> update emp2 set hiredate=sysdate-1;
sql> insert into emp2 select * from emp2; ==> 엄청 많이 수행 하자.

SQL> select name,extents,xacts,shrinks,optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn;

NAME EXTENTS XACTS SHRINKS OPTSIZE
--------------- ---------- ---------- ---------- ----------
SYSTEM 9 0 0
RBS0 8 0 0 4194304
RBS1 8 0 0 4194304
RBS2 8 0 0 4194304
RBS3 8 0 0 4194304
RBS4 8 0 0 4194304
RBS5 8 0 0 4194304
RBS6 8 0 0 4194304
RBS99 3 1 0 81920 ==> extents 증가

SQL> rollback;
SQL> set transaction use rollback segment rbs99; <

'SQL' 카테고리의 다른 글

[펌] 「MySQL 5.0」등장, 기업 사용자도 OK! (0)	2005.10.27
[펌] 동적 SQL의 축복과 저주 (0)	2005.10.13
[mssql] 계층적 구조 표현하기. (0)	2005.10.07
오라클 클라이언트 툴... aquafold.... (0)	2005.09.26
[펌] window 2000, 2003에서 오라클 삭제하기 (0)	2005.09.08

Posted by tornado

|

[mssql] 계층적 구조 표현하기.

SQL 2005. 10. 7. 15:29

sql server 온라인 도움말 에 있는것.

계층 확장

데이터베이스에 계층 정보가 포함될 경우도 있습니다. 예를 들어, 다음 데이터는 전세계 지역에 대한 계층을 표현한 것입니다. 그러나 데이터에 함축되어 있는 구조를 명확하게 알 수 없습니다.

Parent                             Child                             ---------------------------------- ----------------------------------World                              Europe                            World                              North America                     Europe                             France                            France                             Paris                             North America                      United States                     North America                      Canada                            United States                      New York                          United States                      Washington                        New York                           New York City                     Washington                         Redmond

다음 예제와 같이 정리하면 더 이해하기 쉽습니다.

World   North America      Canada      United States         Washington            Redmond         New York            New York City   Europe      France         Paris

다음의 Transact-SQL 프로시저는 인코딩된 계층을 임의의 깊이로 확장합니다. 비록 Transact-SQL이 재귀를 지원하지만 진행 중인 처리를 위해 모든 항목을 추적하는 스택으로 임시 테이블을 사용하는 것이 보다 효율적입니다. 특정한 항목의 처리가 끝나게 되면 스택에서 제거되고 새로운 항목이 발견되면 스택에 추가됩니다.

CREATE PROCEDURE expand (@current char(20)) asSET NOCOUNT ONDECLARE @level int, @line char(20)CREATE TABLE #stack (item char(20), level int)INSERT INTO #stack VALUES (@current, 1)SELECT @level = 1WHILE @level > 0BEGIN   IF EXISTS (SELECT * FROM #stack WHERE level = @level)      BEGIN         SELECT @current = item         FROM #stack         WHERE level = @level         SELECT @line = space(@level - 1) + @current         PRINT @line         DELETE FROM #stack         WHERE level = @level            AND item = @current         INSERT #stack            SELECT child, @level + 1            FROM hierarchy            WHERE parent = @current         IF @@ROWCOUNT > 0            SELECT @level = @level + 1      END   ELSE      SELECT @level = @level - 1END -- WHILE

입력 매개 변수 (@current)는 시작할 계층의 위치를 지정합니다. 이 매개 변수는 주 루프의 현재 항목 역시 추적합니다.

계층에서 현재 수준을 계속 추적하는 @level 과 들여쓰여진 줄을 만드는 데 이용되는 작업 공간인 @line의 두 가지 지역 변수가 사용됩니다.

SET NOCOUNT ON문은 각 SELECT의 ROWCOUNT 메시지로 인해 출력이 혼동되는 것을 방지합니다.

계층의 시작점에서 항목 식별자로 임시 테이블인 #stack이 만들어지고 초기화되며 @level이 이에 맞게 설정됩니다. #stack의 level 열은 데이터베이스의 복수의 수준에 같은 항목이 나타나게 합니다. 비록 이러한 상황이 예제의 지리 데이터에 응용되진 않지만 다른 예제에 응용할 수 있습니다.

이 예제에서 @level이 0보다 크면 프로시저는 다음 단계를 따릅니다.

현재 수준(@level)에서의 스택에 항목이 남아 있으면 프로시저는 하나를 선택하고 @current로 호출합니다.
항목을 @level 공백만큼 들여 쓴 후 인쇄합니다.
더 이상 처리되지 않는 항목을 스택에서 삭제한 후 다음 수준(@level + 1)의 스택에 모든 하위 항목을 추가합니다. 이 곳이 계층 테이블(#stack)이 유일하게 사용되는 곳입니다..
상용 프로그래밍 언어를 사용할 경우 각 하위 항목을 찾아서 일일이 스택에 추가해야 합니다. Transact-SQL을 사용하면 별도의 중첩 루프를 쓰지 않고도 하나의 문으로 모든 하위 항목을 찾고 추가할 수 있습니다.
하위 항목이 존재할 경우(IF @@ROWCOUNT > 0), 처리를 위해 수준을 한 단계 낮추고(@level = @level + 1) 그 외에는 현재 수준에서 처리를 진행합니다.
현재 수준에서 처리 대기 중인 스택의 항목이 없으면 이전 수준(@level = @level - 1)에서 대기 중인 처리가 있는지 알아보기 위해 한 수준 뒤로 갑니다. 이전 수준이 없으면 확장은 완료됩니다.

'SQL' 카테고리의 다른 글

[펌] 동적 SQL의 축복과 저주 (0)	2005.10.13
[펌] [Oracle]오라클 어드민 팁 (0)	2005.10.11
오라클 클라이언트 툴... aquafold.... (0)	2005.09.26
[펌] window 2000, 2003에서 오라클 삭제하기 (0)	2005.09.08
[mssql] 날짜 형식 convert (0)	2005.08.26

Posted by tornado

|

오라클 클라이언트 툴... aquafold....

SQL 2005. 9. 26. 20:54

http://www.aquafold.com/

한글 폰트 깨져서 폰트를 바꿔줘야함..

생각보다 빠르고.. 좋음..

'SQL' 카테고리의 다른 글

[펌] [Oracle]오라클 어드민 팁 (0)	2005.10.11
[mssql] 계층적 구조 표현하기. (0)	2005.10.07
[펌] window 2000, 2003에서 오라클 삭제하기 (0)	2005.09.08
[mssql] 날짜 형식 convert (0)	2005.08.26
MySQL 간단백업 (0)	2005.08.24

Posted by tornado

|

[펌] window 2000, 2003에서 오라클 삭제하기

SQL 2005. 9. 8. 11:02

오라클 삭제하기

1. 오라클을 삭제하기 전에 서비스에서 동작하고 있는
오라클(Oracle로 시작 되는것들)을 정지 시킨다..
2. uninstall한다.

오라클 레지스트리에서 삭제하기

1. 시작->실행->regedit 입력을 하면 레지스트리 편집기가 나온다..
   레지스트리에서
2. HKEY_LOCAL_MACHINE -> SOFTWARE -> ORACLE 삭제
3. HKEY_LOCAL_MACHINE -> SYSTEM -> ControlSet001 -> Services
   HKEY_LOCAL_MACHINE -> SYSTEM -> ControlSet002 -> Services
   HKEY_LOCAL_MACHINE -> SYSTEM -> CurrentControlSet -> Services에서
   Oracle로 시작되는 것을 삭제.
4. HKEY_CLASSES_ROOT 에서 Oracle과 Ora로 시작되는 것을 삭제
   (주의:Ole로 시작되는 것은 지우지 마세요)
5. 윈도우를 재부팅후 Oracle폴더를 삭제한다.

'SQL' 카테고리의 다른 글

[mssql] 계층적 구조 표현하기. (0)	2005.10.07
오라클 클라이언트 툴... aquafold.... (0)	2005.09.26
[mssql] 날짜 형식 convert (0)	2005.08.26
MySQL 간단백업 (0)	2005.08.24
[ms-sql] 카테고리 만들던 중...(허접 -.-) (0)	2005.08.01

Posted by tornado

|

[mssql] 날짜 형식 convert

SQL 2005. 8. 26. 11:56

select getdate() 라고 입력하면.... 2005-08-26 11:42:09.530 과 같이 출력된다.

내가 필요한거는 20050826 만 필요하다..

도움말 보니까.. convert 가 보임..

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

요런 문법인데.. style 옵션을 어케 주느냐에 따라 형식이 바뀐다..

select conver(varchar, getdate(), 112)

결과값은 20050826

'SQL' 카테고리의 다른 글

오라클 클라이언트 툴... aquafold.... (0)	2005.09.26
[펌] window 2000, 2003에서 오라클 삭제하기 (0)	2005.09.08
MySQL 간단백업 (0)	2005.08.24
[ms-sql] 카테고리 만들던 중...(허접 -.-) (0)	2005.08.01
[펌] START WITH and CONNECT BY in Oracle SQL (0)	2005.08.01

Posted by tornado

|

MySQL 간단백업

SQL 2005. 8. 24. 18:51

MySQL 간단 백업

MySQL 백업하기
****************************************************

MySQL 의 백업에는 두가지가 있습니다.
테이블 내부의 데이터만 백업하는 방법과 어떤 데이터 베이스에서 각 테이블 구조와 데이터를
몽땅 백업하는 두가지의 방법입니다.
앞쪽의 내부 데이터만 백업하는 방법은 SQL LEVEL BACKUP 이라고 하구요.
MySQL 안에 들어가서 작업을 합니다.

두번째방법인 전체 백업은 FULL BACKUP 이라고 하구요.
mysql 안에서 하는것이 아니고 mysqldump 라는 유틸리티를 이용해서 백업을 합니다.
이 방법은 테이블의 구조까지 다 백업을 해준다는 장점이 있습니다.

여러분들이 사용할때는 각각의 목적에 맞게 수행하셔야 합니다.
목적에 맞게 라는 말이 좀 그런데요. 각자의 상황에 맞게 대처하셔야 한다는 이야기입니다.
그럼 먼저 데이터만 백업하는 방법을 사용해 보도록 하겠습니다.
먼저 테이블을 하나 만들어 보져.

create table backupTest (
num int unsigned not null auto_increment primary key,
name varchar(20) not null,
email varchar(50) not null,
addr varchar(150) not null,
tel varchar(20) not null
);

insert into backupTest values
(null, '유희성','tornado@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '홍길동','hong@dolpung.pe.kr','한양 워디메쯤','02-202-2020'),
(null, '유오성','ohSung@dolpung.pe.kr','부산.. 칭구동.. 챔피언구','02-222-2020'),
(null, '송종국','song1@dolpung.pe.kr','주소 모름','02-202-2020'),
(null, '憫梔?,'park@dolpung.pe.kr','주소 모름','02-202-2020'),
(null, '희동구','ddink@dolpung.pe.kr','주소 모름','02-202-2020'),
(null, '안정환','ahn@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-5858'),
(null, '유상철','sangchul@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-4938'),
(null, '이을용','ul_yong@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '이천수','tenhand@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '최진철','choi1@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '김남일','kim111@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '이영표','pyo@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '홍명보','hong2@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020'),
(null, '황선홍','hwang@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');

select * from backupTest; 로 잘 들어갔는지 확인을 해봅니다.

잘 들어가 있다면 테이블의 자료들을 특정 File 로 백업을 하겠습니다.

구문은 아래와 같습니다.

mysql> select * into outfile '/tmp/backupTest.sql' from backupTest;
Query OK, 15 rows affected (0.00 sec)

mysql>

이제 백업이 되었는지 확인을 해 봐야 합니다.

터미널을 하나 더 띠우고 /tmp 디렉토리에 backupTest.sql 이 만들어져 있는지 확인을 해봅니다.

# vi /tmp/backupTest.sql 이라고 치시면
각내용이 보이게 됩니다.

[tornado@dolpung tornado]$ vi /tmp/backupTest.sql
1 유희성 tornado@dolpung.pe.k 서울시 뭐라구, 안들? 02-202-2020
2 홍길동 hong@dolpung.pe.kr 한양 워디메쯤 02-202-2020
3 유오성 ohSung@dolpung.pe.kr 부산.. 칭구동.. 챔피 02-222-2020
4 송종국 song1@dolpung.pe.kr 주소 모름 02-202-2020
5 박지성 park@dolpung.pe.kr 주소 모름 02-202-2020
6 희동구 ddink@dolpung.pe.kr 주소 모름 02-202-2020
7 안정환 ahn@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-5858
8 유상철 sangchul@dolpung.pe. 서울시 뭐라구, 안들? 02-202-4938
9 이을용 ul_yong@dolpung.pe.k 서울시 뭐라구, 안들? 02-202-2020
10 이천수 tenhand@dolpung.pe.k 서울시 뭐라구, 안들? 02-202-2020
11 최진철 choi1@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
12 김남일 kim111@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
13 이영표 pyo@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
14 홍명보 hong2@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
15 황선홍 hwang@dolpung.pe.kr 서울시 뭐라구, 안들? 02-202-2020
~
~

위와 같은 내용이 잘 들어가 있다면 성공하신 겁니다.
각각의 필드 사이에는 TAB 으로 구별이 되어있습니다.
데이터를 작성하실때도 TAB 으로 구별해서 작성하시면 다시 인서트가 가능합니다.

그리고 백업을 할때 꼭 TAB 으로 구별을 하라는 법은 없습니다.
백업 구문 뒷쪽에 옵션 하나를 붙여주게 되면 됩니다.
각각의 문자로 구별을 하실수 있습니다.

아래와 같이 쿼리문장을 수행합니다.

mysql> select * INTO OUTFILE
-> '/tmp/backupTest1.sql' FIELDS TERMINATED BY ','
-> from backupTest;
Query OK, 15 rows affected (0.00 sec)

mysql>

이제 결과 값이 어떻게 나왔는지 봅시다.
다른 터미널 창에서 아래와 같이 입력합니다.
# vi /tmp/backupTest1.sql

1,유희성,tornado@dolpung.pe.k,서울시 뭐라구, 안들?02-202-2020
2,홍길동,hong@dolpung.pe.kr,한양 워디메쯤,02-202-2020
3,유오성,ohSung@dolpung.pe.kr,부산.. 칭구동.. 챔피,02-222-2020
4,송종국,song1@dolpung.pe.kr,주소 모름,02-202-2020
5,박지성,park@dolpung.pe.kr,주소 모름,02-202-2020
6,희동구,ddink@dolpung.pe.kr,주소 모름,02-202-2020
7,안정환,ahn@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-5858
8,유상철,sangchul@dolpung.pe.,서울시 뭐라구, 안들?02-202-4938
9,이을용,ul_yong@dolpung.pe.k,서울시 뭐라구, 안들?02-202-2020
10,이천수,tenhand@dolpung.pe.k,서울시 뭐라구, 안들?02-202-2020
11,최진철,choi1@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020
12,김남일,kim111@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020
13,이영표,pyo@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020
14,홍명보,hong2@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020
15,황선홍,hwang@dolpung.pe.kr,서울시 뭐라구, 안들?02-202-2020

아까는 각 필드의 구별이 TAB 으로 띠워져 있었습니다.
Fields TERMINATED BY ',' 라고 옵션을 붙였더니 TAB 대신 , 가
필드들의 구별자 역할을 해주는 것을 볼 수있습니다.
가끔 MYSQL 데이터를 MSSQL 이나 ACCESS 로 이전할때 각 필드의
공백문자를 인식 못할때가 있을 수 있습니다.
이때 위와 같이 필드 구별자를 지정하여 사용하시면 됩니다.

이제 데이터를 백업했으니 테이블에 집어 넣어야 합니다.
위에서 한 방법은 SQL 레벨 백업이라고 했습니다.
그러니 백업한 데이터를 복구할때도 SQL 차원에서 해결해야 합니다.

사용방법은 아래와 같습니다.

mysql> delete from backupTest;

mysql> LOAD DATA INFILE '/tmp/backupTest.sql'
-> INTO TABLE backupTest;
Query OK, 15 rows affected (0.00 sec)
Records: 15 Deleted: 0 Skipped: 0 Warnings: 0

mysql>
mysql> select * from backupTest;
+-----+--------+----------------------+----------------------+-------------+
| num | name | email | addr | tel |
+-----+--------+----------------------+----------------------+-------------+
| 1 | 유희성 | tornado@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 2 | 홍길동 | hong@dolpung.pe.kr | 한양 워디메쯤 | 02-202-2020 |
| 3 | 유오성 | ohSung@dolpung.pe.kr | 부산.. 칭구동.. 챔피 | 02-222-2020 |
| 4 | 송종국 | song1@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 5 | 박지성 | park@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 6 | 희동구 | ddink@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 7 | 안정환 | ahn@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-5858 |
| 8 | 유상철 | sangchul@dolpung.pe. | 서울시 뭐라구, 안들?| 02-202-4938 |
| 9 | 이을용 | ul_yong@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 10 | 이천수 | tenhand@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 11 | 최진철 | choi1@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 12 | 김남일 | kim111@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 13 | 이영표 | pyo@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 14 | 홍명보 | hong2@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 15 | 황선홍 | hwang@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
+-----+--------+----------------------+----------------------+-------------+
15 rows in set (0.00 sec)

mysql>

잘 들어간걸 확인 하실 수 있습니다.
그럼 이번에는 두번째 백업한 파일인 backupTest1.sql 을 복구시켜보겠습니다.

기존에 들어가 있는 데이터를 다 지우고 Load Data 구문을 실행합니다..
mysql> delete from backupTest;
mysql> LOAD DATA INFILE '/tmp/backupTest1.sql'
-> INTO TABLE backupTest FIELDS TERMINATED BY ',';
Query OK, 15 rows affected (0.00 sec)
Records: 15 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

이제 검사를 해봅시다.

mysql> select * from backupTest
-> ;
+-----+--------+----------------------+----------------------+-------------+
| num | name | email | addr | tel |
+-----+--------+----------------------+----------------------+-------------+
| 1 | 유희성 | tornado@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 2 | 홍길동 | hong@dolpung.pe.kr | 한양 워디메쯤 | 02-202-2020 |
| 3 | 유오성 | ohSung@dolpung.pe.kr | 부산.. 칭구동.. 챔피 | 02-222-2020 |
| 4 | 송종국 | song1@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 5 | 박지성 | park@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 6 | 희동구 | ddink@dolpung.pe.kr | 주소 모름 | 02-202-2020 |
| 7 | 안정환 | ahn@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-5858 |
| 8 | 유상철 | sangchul@dolpung.pe. | 서울시 뭐라구, 안들?| 02-202-4938 |
| 9 | 이을용 | ul_yong@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 10 | 이천수 | tenhand@dolpung.pe.k | 서울시 뭐라구, 안들?| 02-202-2020 |
| 11 | 최진철 | choi1@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 12 | 김남일 | kim111@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 13 | 이영표 | pyo@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 14 | 홍명보 | hong2@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
| 15 | 황선홍 | hwang@dolpung.pe.kr | 서울시 뭐라구, 안들?| 02-202-2020 |
+-----+--------+----------------------+----------------------+-------------+
15 rows in set (0.01 sec)

mysql>

위와 같이 들어갔다면 정상적으로 된것입니다.
만약 번호만 입력이 되고 데이터가 안들어갔다면 FIELDS TERMINATED BY 로 지정한
컬럼 구분자가 틀렸다는 이야기 입니다.
그것을 확인하시고 입력을 하신다면 데이터가 입력되는데 전혀 문제가 없을 것입니다.

이제 MySQL 의 풀레벨 백업을 해 보겠습니다.

/usr/local/mysql/bin 디렉토리를 보게 되면 mysqldump 라는 명령이 있습니다.
이 명령어를 이용해서 테이블 구조와 모든 레코드 들을 백업하겠습니다.

일단 MySQL 을 종료시켜야 합니다.
quit 나 q 를 쳐서 빠져 나옵니다.

# cd /usr/local/mysql/bin
# ./mysqldump -u root -p test > /tmp/fullBackup1.sql
Enter password:
#

-u 옵션은 유져를 지정하고
-p 는 패스워드를 지정합니다.
test 는 test 데이터베이스를 지정한 것이고
> 표시는 test 데이터 베이스의 내용을 /tmp/fullBackup1.sql 로 백업을 하겠다
라는 이야기 입니다.
만약 에러가 난다면 에러메세지를 잘 읽어보시고 대처하시기 바랍니다.

이제 vi editor 로 파일을 열어보겠습니다.
# vi /tmp/fullBackup1.sql

내용이 확실히 보이시지요??

아래의 쿼리를 다시 날려보고 먼저 백업한 파일과 비교를 하겠습니다.

# ./mysqldump --add-drop-table -u root -p test > /tmp/fullbackup1.sql

결과치의 일부분은 아래와 같습니다.

DROP TABLE IF EXISTS backuptest;
CREATE TABLE backuptest (
num int(10) unsigned NOT NULL auto_increment,
name varchar(20) NOT NULL default '',
email varchar(50) NOT NULL default '',
addr varchar(150) NOT NULL default '',
tel varchar(20) NOT NULL default '',
PRIMARY KEY (num)
) TYPE=MyISAM;

설명 없습니다.

아래는 -e 옵션을 붙인 겁니다.
-e 옵션은 빠른 insert 문으로 쿼리를 작성해 줍니다.

# ./mysqldump -e -u root -p test > /tmp/fullbackup2.sql

INSERT INTO backuptest VALUES (1,'유희성','tornado@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(2,'홍길동','hong@dolpung.pe.kr','한양 워디메쯤','02-202-2020')
,(3,'유오성','ohSung@dolpung.pe.kr','부산.. 칭구동.. 챔피언구','02-222-2020')
,(4,'송종국','song1@dolpung.pe.kr','주소 모름','02-202-2020')
,(5,'박지성','park@dolpung.pe.kr','주소 모름','02-202-2020')
,(6,'희동구','ddink@dolpung.pe.kr','주소 모름','02-202-2020')
,(7,'안정환','ahn@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-5858')
,(8,'유상철','sangchul@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-4938')
,(9,'이을용','ul_yong@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(10,'이천수','tenhand@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(11,'최진철','choi1@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(12,'김남일','kim111@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(13,'이영표','pyo@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(14,'홍명보','hong2@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020')
,(15,'황선홍','hwang@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');

-t 옵션을 줘서 한번 백업을 해보져
-t 옵션은 데이터만 백업합니다. 테이블 구조는 백업 안합니다.

# ./mysqldump -t -u root -p test > /tmp/fullbackup3.sql

아래와 같은 구조가 되겠지요.

--
-- Dumping data for table 'backuptest'
--

INSERT INTO backuptest VALUES (1,'유희성','tornado@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (2,'홍길동','hong@dolpung.pe.kr','한양 워디메쯤','02-202-2020');
INSERT INTO backuptest VALUES (3,'유오성','ohSung@dolpung.pe.kr','부산.. 칭구동.. 챔피언구','02-222-2020');
INSERT INTO backuptest VALUES (4,'송종국','song1@dolpung.pe.kr','주소 모름','02-202-2020');
INSERT INTO backuptest VALUES (5,'박지성','park@dolpung.pe.kr','주소 모름','02-202-2020');
INSERT INTO backuptest VALUES (6,'희동구','ddink@dolpung.pe.kr','주소 모름','02-202-2020');
INSERT INTO backuptest VALUES (7,'안정환','ahn@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-5858');
INSERT INTO backuptest VALUES (8,'유상철','sangchul@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-4938');
INSERT INTO backuptest VALUES (9,'이을용','ul_yong@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (10,'이천수','tenhand@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (11,'최진철','choi1@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (12,'김남일','kim111@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (13,'이영표','pyo@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (14,'홍명보','hong2@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');
INSERT INTO backuptest VALUES (15,'황선홍','hwang@dolpung.pe.kr','서울시 뭐라구, 안들리 3동','02-202-2020');

아래는 -d 옵션을 준겁니다. -d 옵션은 테이블만 덤프합니다.

# ./mysqldump -d -u root -p test > /tmp/fullbackup4.sql

결과는 다음과 같습니다.

-- MySQL dump 9.07
--
-- Host: localhost Database: test
---------------------------------------------------------
-- Server version 4.0.4-beta-max-nt

--
-- Table structure for table 'aaa'
--

CREATE TABLE aaa (
id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

--
-- Table structure for table 'backuptest'
--

CREATE TABLE backuptest (
num int(10) unsigned NOT NULL auto_increment,
name varchar(20) NOT NULL default '',
email varchar(50) NOT NULL default '',
addr varchar(150) NOT NULL default '',
tel varchar(20) NOT NULL default '',
PRIMARY KEY (num)
) TYPE=MyISAM;

--
-- Table structure for table 'bbb'
--

CREATE TABLE bbb (
id int(10) unsigned NOT NULL default '0'
) TYPE=MyISAM;

즐 MySQL 합시다.

'SQL' 카테고리의 다른 글

[펌] window 2000, 2003에서 오라클 삭제하기 (0)	2005.09.08
[mssql] 날짜 형식 convert (0)	2005.08.26
[ms-sql] 카테고리 만들던 중...(허접 -.-) (0)	2005.08.01
[펌] START WITH and CONNECT BY in Oracle SQL (0)	2005.08.01
[mssql] rowcount .... (0)	2005.06.21

Posted by tornado

|

[ms-sql] 카테고리 만들던 중...(허접 -.-)

SQL 2005. 8. 1. 19:12

.NET 관련 프로젝트 하는 중 부서 를 표현할 일이 생겼다.

음악 >> 가요>> 발라드

머 이런 구조로 표현해야 하는데.. 적응 안되는 ms-sql 이다..

구조는 PK 값이 있고.. Parent 값으로 부모값을 찾아가는 평범한 형태이다.

함수로 구현할까.. 하다가.. 삽질 한번 하고 프로시져로 대충 결과만 나오게 함 ㅡㅡ;

오라클에 start with ... connect by 처럼 하고 싶었으나... 구차니즘과...

스킬의 부족함으로 인해 ㅠㅠ 이렇게 밖에 못함..

Create proc DeptSortList(@idx int)
AS

Declare @parent as int
set @parent = 1

if(@idx < 1) Begin

select top 1 @idx = idx from dept order by idx asc
END

select * into #temp_dept from dept where idx = @idx order by idx asc

WHILE (@parent > 0)
BEGIN

  select top 1 @parent = parent from #temp_dept order by idx asc
  insert into #temp_dept select a.* from dept a join #temp_dept b on a.idx = b.parent where a.idx = @parent
END
SELECT * FROM #temp_dept order by idx asc

GO

콜 해보면 그냥 보통 테이블 구조로 특정 부서의 상위 코드가 출력된다.

적당히 가공해서

a >> a_a >> a_a_a >> a_a_a_a

처럼 출력하고 대충대충 링크 걸고.. 대충대충 iframe 으로 보내서 대충대충 입력하면 된다 ㅡㅡ;

'SQL' 카테고리의 다른 글

[mssql] 날짜 형식 convert (0)	2005.08.26
MySQL 간단백업 (0)	2005.08.24
[펌] START WITH and CONNECT BY in Oracle SQL (0)	2005.08.01
[mssql] rowcount .... (0)	2005.06.21
[ms-sql] 실행 계획 보기... 아주 쌩초보다.. (0)	2005.06.20

Posted by tornado

|

[펌] START WITH and CONNECT BY in Oracle SQL

SQL 2005. 8. 1. 14:49

The start with connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).
It is also being used when an sql execution plan is explained.

In the following example, the table from which that data is selected consists of just these attributes: parent and child. We make sure (by means of a uniqe constraint) that the child is uniqe within the table. This is just like in the real life where (as of yet) a child cannot have to different mothers. The data that gets filled into the table is such that a the sum over the childs with the same parent is the value of the parent:

set feedback offcreate table test_connect_by (  parent     number,  child      number,  constraint uq_tcb unique (child));

5 = 2+3

insert into test_connect_by values ( 5, 2);insert into test_connect_by values ( 5, 3);

18 = 11+7

insert into test_connect_by values (18,11);insert into test_connect_by values (18, 7);

17 = 9+8

insert into test_connect_by values (17, 9);insert into test_connect_by values (17, 8);

26 = 13+1+12

insert into test_connect_by values (26,13);insert into test_connect_by values (26, 1);insert into test_connect_by values (26,12);

15=10+5

insert into test_connect_by values (15,10);insert into test_connect_by values (15, 5);

38=15+17+6

insert into test_connect_by values (38,15);insert into test_connect_by values (38,17);insert into test_connect_by values (38, 6);

38,26,16 have no parents (the parent is null)

insert into test_connect_by values (null,38);insert into test_connect_by values (null,26);insert into test_connect_by values (null,16);

Now, let's select the data hierarchically:

select lpad(' ',2*(level-1)) || to_char(child) s   from test_connect_by   start with parent is null  connect by prior child = parent;

This select statement results in:

38  15    10    5      2      3  17    9    8  626  13  1  1216

How must the select statement be read? If Oracle encounters an SQL statement with a start with and a connect by clause in it, it can be thought of proceeded like this:

  for rec in (select * from table) loop    if FULLFILLS_START_WITH_CONDITION(rec) then      RECURSE(rec, rec.parent);    end if;  end loop;  procedure RECURSE (rec_parent in RECORD_MATCHING_SELECT_STMT, field_par IN field_type) is    APPEND_RESULT_LIST(rec_parent);         for rec_recurse in (select * from table) loop      if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child,field_par) then        RECURSE(rec_recurse,rec_recurse.parent);      end if;    end loop;  end procedure RECURSE;

Pruning branches

Sometimes, it might be a requirement to only partially retrieve a hierarchical tree and to prune branches. Here, a tree is filled. Each child is the number of its parent plus a new digit on the right side.

create table prune_test (  parent  number,  child   number);insert into prune_test values (null,   1);insert into prune_test values (null,   6);insert into prune_test values (null,   7);insert into prune_test values (   1,  12);insert into prune_test values (   1,  14);insert into prune_test values (   1,  15);insert into prune_test values (   6,  61);insert into prune_test values (   6,  63);insert into prune_test values (   6,  65);insert into prune_test values (   6,  69);insert into prune_test values (   7,  71);insert into prune_test values (   7,  74);insert into prune_test values (  12, 120);insert into prune_test values (  12, 124);insert into prune_test values (  12, 127);insert into prune_test values (  65, 653);insert into prune_test values (  71, 712);insert into prune_test values (  71, 713);insert into prune_test values (  71, 715);insert into prune_test values (  74, 744);insert into prune_test values (  74, 746);insert into prune_test values (  74, 748);insert into prune_test values ( 712,7122);insert into prune_test values ( 712,7125);insert into prune_test values ( 712,7127);insert into prune_test values ( 748,7481);insert into prune_test values ( 748,7483);insert into prune_test values ( 748,7487);

Now, we want to retrieve the tree, but prune everything below the branch 1 and 71. It would be false to put these into a where clause of the sql statement, rather, it belongs to the connect by clause:

select  lpad(' ', 2*level) || childfrom  prune_teststart with  parent is nullconnect by  prior child=parent   and parent not in (1, 71);

This returns:

  1  6    61    63    65      653    69  7    71    74      744      746      748        7481        7483        7487

Do two items stand in a ancestor descendant relationship

Sometimes, one want's to know if two items are in an ancestor descendant relationship, that is if XYZ as grandfather, or grand-grandfather, or ... of ABC. The following template of a query can be used to determine that.

set feedback offdrop table parent_child;create table parent_child(parent_ varchar2(20), child_ varchar2(20));insert into parent_child values (null,  'a')insert into parent_child values (  'a',  'af');insert into parent_child values (  'a',  'ab');insert into parent_child values (  'a',  'ax');insert into parent_child values ( 'ab', 'abc');insert into parent_child values ( 'ab', 'abd');insert into parent_child values ( 'ab', 'abe');insert into parent_child values ('abe','abes');insert into parent_child values ('abe','abet');insert into parent_child values ( null,   'b');insert into parent_child values (  'b',  'bg');insert into parent_child values (  'b',  'bh');insert into parent_child values (  'b',  'bi');insert into parent_child values ( 'bi', 'biq');insert into parent_child values ( 'bi', 'biv');insert into parent_child values ( 'bi', 'biw');

The following query 'asks' for a parent and a supposed child (grand child, grand grand child) and answers the question if the are indeed in an ancester successor relationship.

set verify offselect  case when count(*) > 0 then    '&&parent is an ancestor of &&child' else    '&&parent is no ancestor of &&child' end     "And here's the answer"from  parent_childwhere  child_ = '&&child'start with  parent_ = '&&parent'connect by   prior child_ = parent_;undefine childundefine parent

Features of 9i

sys_connect_by_path

With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child:

Using hierarchical result sets

With this technique, it is possible to show all kind of hierarchical data relations. Here is an example that lists privileges, roles and users in their hierarchical relation.

connect_by_root

connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.

I have yet to dig into this subject and will write about it when things become clearer.

connect_by_is_leaf

connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.

I have yet to dig into this subject and will write about it when things become clearer.

connect_by_iscycle

connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.

I have yet to dig into this subject and will write about it when things become clearer.

Thanks to Peter Bruhn, Jonathan Schmalze and Jeff Jones who each pointed out an error on this page.

I always try to improve my site. If you see something that could be better, or is simply wrong, feel free to send any suggestions, ideas, comments to rene.nyffenegger@adp-gmbh.ch

This page is hosted by init 7. I am extremely satisfied with this super friendly swiss based company. So, if you're after web hosting or serverhousing, I recommend to take a look at init 7. As an aside, they also provide a free (except phone call fee) dial up (from within Switzerland).

'SQL' 카테고리의 다른 글

MySQL 간단백업 (0)	2005.08.24
[ms-sql] 카테고리 만들던 중...(허접 -.-) (0)	2005.08.01
[mssql] rowcount .... (0)	2005.06.21
[ms-sql] 실행 계획 보기... 아주 쌩초보다.. (0)	2005.06.20
MySQL 1067 에러... (3)	2005.05.20

Posted by tornado

|

[mssql] rowcount ....

SQL 2005. 6. 21. 15:37

프로시저 만들다가... rowcount 를 처음 봤다..

보통 select top 20 ... 이런식으로 데이터를 가져오는데... 이렇게 안해두 된다.

SET ROWCOUNT @PAGE_SIZE

select * from xxx where .. 어짜구...

결과는 TOP 문장을 쓴거랑 똑같다..

'SQL' 카테고리의 다른 글

[ms-sql] 카테고리 만들던 중...(허접 -.-) (0)	2005.08.01
[펌] START WITH and CONNECT BY in Oracle SQL (0)	2005.08.01
[ms-sql] 실행 계획 보기... 아주 쌩초보다.. (0)	2005.06.20
MySQL 1067 에러... (3)	2005.05.20
[펌] 데이터 모델 정규화/반정규화의 실전 프로젝트 적용 (0)	2005.05.13

Posted by tornado

|

[ms-sql] 실행 계획 보기... 아주 쌩초보다..

SQL 2005. 6. 20. 15:32

SET STATISTICS IO ON
SET STATISTICS PROFILE ON

GO

SELECT * FROM Employees
GO

일케 하니까... 뭔가 계획이 나오긴 한다..

아직 뭐가 뭔지 모르니... 책보구 요점을 좀 봐야겠다...

'SQL' 카테고리의 다른 글

[펌] START WITH and CONNECT BY in Oracle SQL (0)	2005.08.01
[mssql] rowcount .... (0)	2005.06.21
MySQL 1067 에러... (3)	2005.05.20
[펌] 데이터 모델 정규화/반정규화의 실전 프로젝트 적용 (0)	2005.05.13
[펌] ‘6개의 열쇠’로 데이터 모델링의 고수가 되자 (0)	2005.03.02

Posted by tornado

|

MySQL 1067 에러...

SQL 2005. 5. 20. 16:13

오늘 .NET 2003 깔았다.

기존에 회사 내부 인트라넷이 MySQL 로 되어있어서... MySQL 을 계속 사용해야 한다.

포맷하고.. 닷넷 서버 깔고... MySQL 깔고 ... 서비스 시작하려니..

MySQL 1067 에러 발생...

c:/Windows 디렉토리에 my.ini 파일이 없다 .... 당근 없쥐..

그래서 아래와 같이 생성...

[WinMySQLAdmin]
Server=D:/mysql/bin/mysqld-max.exe
[mysqld]
basedir=D:/mysql
datadir=D:/mysql/data

저장하고...

d:/mysql/bin 디렉토리에 가서

mysqld-max-nt --install

한 담에..

net start mysql

1067 에러 해결....

'SQL' 카테고리의 다른 글

[mssql] rowcount .... (0)	2005.06.21
[ms-sql] 실행 계획 보기... 아주 쌩초보다.. (0)	2005.06.20
[펌] 데이터 모델 정규화/반정규화의 실전 프로젝트 적용 (0)	2005.05.13
[펌] ‘6개의 열쇠’로 데이터 모델링의 고수가 되자 (0)	2005.03.02
mysql 4.0.x 이하 버전 .. 한글셋팅.. (0)	2005.02.16

Posted by tornado

|

카테고리

태그목록

공지사항

최근에 올라온 글

최근에 달린 댓글

링크

글 보관함

달력

'SQL'에 해당되는 글 52건

'SQL > MySQL' 카테고리의 다른 글

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

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

요약

목차

동적 쿼리

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

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

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

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

- 예제 2. 동적 SELECT 절

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

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

- 예제 5. 동적 GROUP BY, HAVING

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

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

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

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

- 예제 10. 동적 UPDATE

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

'SQL > MySQL' 카테고리의 다른 글

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

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

'SQL' 카테고리의 다른 글

'SQL' 카테고리의 다른 글

동적 SQL의 축복과 저주

목 차:

왜 저장 프로시저를 사용하는가?

1. 권한 체계 (The Permission System)

2. 실행계획 캐쉬 (Caching Query Plans)

3. 네트워크 소통량 최소화 (Minimizing Network Traffic)

4. 출력매개변수 사용 (Using Output Parameters)

5. 업무규칙 모듈화 (Encapsulating Logic)

6. 의존성 파악 (Keeping Track of what Is Used)

EXEC()와 sp_executesql

동적 SQL과 저장 프로시저

1. 권한 (Permissions)

2. 실행계획 캐쉬 (Caching Query Plans)

3. 네트워크 소통량 최소화 (Minimizing Network Traffic)

4. 출력매개변수 사용 (Using Output Parameters)

5. 업무규칙 모듈화 (Encapsulating Logic)

6. 의존성 파악 (Keeping Track of what Is Used)

SQL Injection - 심각한 보안 문제

좋은 코딩 습관과 동적 SQL

동적 SQL을 사용(하지 말아야)하는 일반적인 경우 (Common Cases when to (Not) Use Dynamic SQL)

select * from @tablename

select * from sales + @yymm

update tbl set @colname = @value where keycol = @keyval

select * from @dbname + '..tbl'

select * from tbl where col in (@list)

select * from tbl where @condition

동적 검색 조건 (Dynamic Search Conditions)

select * from table order by @col

'SQL' 카테고리의 다른 글

'SQL' 카테고리의 다른 글

sql server 온라인 도움말 에 있는것.

계층 확장

'SQL' 카테고리의 다른 글

'SQL' 카테고리의 다른 글

'SQL' 카테고리의 다른 글

'SQL' 카테고리의 다른 글

'SQL' 카테고리의 다른 글

'SQL' 카테고리의 다른 글

Pruning branches

Do two items stand in a ancestor descendant relationship

Features of 9i

sys_connect_by_path

Using hierarchical result sets

connect_by_root

connect_by_is_leaf

connect_by_iscycle