* Windows 기반 Oracle Hot Backup

1. Data file Backup

2. Control file Backup

2012 년 9 월 10 일 PostgreSQL Global Development Group은 오픈 소스 데이터베이스 업체 인 PostgreSQL의 최신 버전 9.2의 출시를 발표합니다. 5 월에 베타 버전이 출시 된 이후, 개발자와 벤더는 성능, 확장 성, 유연성 측면에서 이 릴리스가 크게 진보 한 것을 높이 평가했습니다.

1. 성능과 확장성 향상 : 64 코어까지의 선형 확장성, index-only scan, CPU 전력 소비 감소

- 초당 최고 350,000 번의 검색 Query (기존 대비 4 배 빠른)

- index-only scan에 의한 DW Query (기존 대비 2 배에서 20 배의 속도)

- 초당 최고 14,000 개의 update 작업 (기존 대비 5 배 빠른)

2. 계단식 복제 기능 추가

3. RANGE 형과 JSON을 지원

출처 : http://lets.postgresql.jp/news/9_2_release

-- 개인적으로 힌트나 좀 추가해주지... 라는 생각과 index-only scan 이 지금 추가된 것으로 보아 상용 DBMS 와의 경쟁에서 아직은 아쉽다라는 생각... 무료라는 것과 의외로 넓은 활용성이 엄청난 강점이지만... 지속적인 upgrade가 되는 것으로 보아 언젠가는 상용 DBMS를 따라 잡을 수 있을 것 같다는 기대감이 있습니다.

dowload : http://www.postgresql.org/download/

Database의 Transaction Log가 해당 Disk 혹은 Volume 을 모두 차지해서 Postgresql service가 내려가는 경우라 던가... 여타의 이유로 Postgresql service 를 시작하고자 하는데.. Transaction Log의 replay 를 막고자 하는 경우가 있다.

이 경우 pg_resetxlog 를 사용하면 된다. 당연히 Postgresql service 는 내려가 있는 상태에서 실행해야 한다.

Overview

pg_trgm의 모듈은 trigram 매칭으로 ASCII 영숫자 텍스트의 유사성을 결정하는 함수와 연산자를 제공.
이를 활용하여 앞뒤의 와일드카드가 포함된 검색을 빠르게 처리.

환경

• CentOS
• Postgresql 9.1.4
• Postgresql의 pg_trgm.so Library

한번에 성공하다니... ^0^

개발을 하다 보면 1차원 배열을 받아서 테이블 형태로 가공한 뒤 처리해야 할 경우가 발생한다.

이를 처리하는 방법에 대한 정리.

Method 1: Dynamic SQL

CREATE PROC dbo.GetOrderList1

(

@OrderList varchar(500)

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL varchar(600)

SET @SQL = 

'SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders

WHERE OrderID IN (' + @OrderList + ')'

EXEC(@SQL)

END

GO

EXEC dbo.GetOrderList1 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'

GO

Method 2: Pass...

Method 3: Parsing the comma separated values into a table variable and joining the table variable to main table

CREATE PROC dbo.GetOrderList3

(

@OrderList varchar(500)

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @TempList table

(

OrderID int

)

DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','

SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''

BEGIN

WHILE @Pos > 0

BEGIN

SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))

IF @OrderID <> ''

BEGIN

INSERT INTO @TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion

END

SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)

SET @Pos = CHARINDEX(',', @OrderList, 1)

END

END

SELECT o.OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders AS o

JOIN 

@TempList t

ON o.OrderID = t.OrderID

END

GO

EXEC dbo.GetOrderList3 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'

GO

Method 4: Using XML

CREATE PROC dbo.GetOrderList4

(

@OrderList varchar(1000)

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @DocHandle int

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @OrderList

SELECT o.OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders AS o

JOIN

OPENXML (@DocHandle, '/ROOT/Ord',1) WITH (OrderID  int) AS x

ON o.OrderID = x.OrderID

EXEC sp_xml_removedocument @DocHandle 

END

GO

EXEC dbo.GetOrderList4 '

<ROOT>

<Ord OrderID = "10248"/> <Ord OrderID = "10252"/>

<Ord OrderID = "10256"/> <Ord OrderID = "10261"/>

<Ord OrderID = "10262"/> <Ord OrderID = "10263"/>

<Ord OrderID = "10264"/> <Ord OrderID = "10265"/>

<Ord OrderID = "10300"/> <Ord OrderID = "10311"/>

<Ord OrderID = "11068"/> <Ord OrderID = "11069"/>

<Ord OrderID = "11070"/> <Ord OrderID = "11071"/>

<Ord OrderID = "11072"/> <Ord OrderID = "11073"/>

<Ord OrderID = "11074"/> <Ord OrderID = "11075"/>

<Ord OrderID = "11076"/> <Ord OrderID = "11077"/>

</ROOT>'

GO

Method 5: Using a table of numbers or pivot table, to parse the comma separated list

--Create a table called Numbers

CREATE TABLE dbo.Numbers

(

Number int PRIMARY KEY CLUSTERED

)

GO

--Insert 8000 numbers into this table (from 1 to 8000)

SET NOCOUNT ON

GO

DECLARE @CTR int

SET @CTR = 1

WHILE @CTR < 8001

BEGIN

INSERT INTO dbo.Numbers (Number) VALUES (@CTR)

SET @CTR = @CTR + 1

END

GO

--The above two steps are to be run only once. The following stored procedure uses the number table.

CREATE PROC dbo.GetOrderList5

(

@OrderList varchar(1000)

)

AS

BEGIN

SET NOCOUNT ON

SELECT o.OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders AS o

JOIN

(

SELECT LTRIM(RTRIM(SUBSTRING(OrderID, number+1, CHARINDEX(',', OrderID, number+1)-number - 1))) AS OrderID

FROM

(

SELECT ',' + @OrderList + ',' AS OrderID

) AS InnerQuery

JOIN 

Numbers n 

ON 

n.Number < LEN(InnerQuery.OrderID)

WHERE SUBSTRING(OrderID, number, 1) = ','

) as Derived

ON o.OrderID = Derived.OrderID

END

GO

EXEC dbo.GetOrderList5 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'

GO

Method 6: Using a general purpose User Defined Function (UDF) to parse the comma separated OrderIDs

--The following is a general purpose UDF to split comma separated lists into individual items.

--Consider an additional input parameter for the delimiter, so that you can use any delimiter you like.

CREATE FUNCTION dbo.SplitOrderIDs

(

@OrderList varchar(500)

)

RETURNS 

@ParsedList table

(

OrderID int

)

AS

BEGIN

DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','

SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''

BEGIN

WHILE @Pos > 0

BEGIN

SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))

IF @OrderID <> ''

BEGIN

INSERT INTO @ParsedList (OrderID) 

VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion

END

SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)

SET @Pos = CHARINDEX(',', @OrderList, 1)

END

END

RETURN

END

GO

CREATE PROC dbo.GetOrderList6

(

@OrderList varchar(500)

)

AS

BEGIN

SET NOCOUNT ON

SELECT o.OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders AS o

JOIN

dbo.SplitOrderIDs(@OrderList) AS s

ON

o.OrderID = s.OrderID

END

GO

EXEC dbo.GetOrderList6 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'

GO

Best Solution (최상의 성능과 간단한 구현)

: 천재다...

DECLARE @textXML XML

DECLARE @data NVARCHAR(MAX), 

        @delimiter NVARCHAR(5)

SELECT  @data = 'A,B,C',

        @delimiter = ','

SELECT    @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML)

SELECT  T.split.value('.', 'nvarchar(max)') AS data

FROM    @textXML.nodes('/d') T(split)

참고 URL

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

http://stackoverflow.com/questions/1922191/passing-array-as-parameter-to-sql-2005-stored-procedure

SQL Server로 Linked Query를 사용하여 작업할 경우가 많다.

Linked Query 자체를 생성하는 것이 힘겨운 경우도 있고... 우여곡절이 많다. ㅠㅠ

이번 기록은 Linked Query를 일단 생성한 후의 활용에 대해서 정리한다.

Query

: SELECT * FROM <Linked Server Name>..<Owner Name>.<Table Name> WHERE ~~;

-- 성능 않좋고... 어딘가에 써먹을 데가 별로 없다.

-- 쿼리 작성이 간단하다.

-- 4-Part 라고 한다.

-- INSERT, UPDATE, DELETE 문장을 보통 쿼리문과 비슷하게 사용한다.

: SELECT * FROM OPENQUERY(<Linked Server Name>, 'SELECT * FROM <Table Name> WHERE ~~');

-- 성능이 나쁘지 않고, 자주 사용.

-- 쿼리를 문자열로 조합하기 귀찮음.

-- OPENQUERY 라고 한다.

-- INSERT의 경우 OPENQUERY 내의 SELECT 문에 WHERE 1<>1 로 UPDATE, DELETE 문은 검색 제한자를 정확히 써서 인덱스를 잘 사용하면 성능이 봐줄만 하다.

-- OPENQUERY 내의 문자열은 완성된 형태의 문자열만 가능하며, 변수는 받을 수 없다. <-- 이게 치명적인 귀찮음...

Procedure 호출

: Oracle Procedure

execute ('call <OracleSchema>.<OracleProcedureName>()') at <Linked Server Name>

관련 link : http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/12e9c126-496b-49ad-9bf7-4320f7d09af8

: Oracle Package

SELECT *

FROM  OPENQUERY(ls_name , '{CALL  user1.upk_test.up_test_inpkg(1111,''222'', {resultSet 1, po_RecordSet }}');

"sqlServer에 linked server로 oracle에 연결하고, oracle의 sp를 call한 후, 응답코드에 따라
 이후 작업 진행"

위와 같은 방법보다는 application에서 control하는 것이 좋을 듯 하지만, 어쩔 수 없이 이렇게
해야만 하는 경우가 있어, 코드 부스러기 주워담아 아래와 같이 해결했다.

return값을 table형식이 아닌 number형식으로 받는 방법을 몰라서 응답코드를 table형식으로 return
하도록 처리하였다.(이에 대해서 좀 더 효율적인 방법에 대해서 댓글 부탁합니다.)

CASE1) oracle에서 package를 작성
1. oracle에서 패키지 작성
----------------------------------------------------------------
create or replace package upk_test
as
    type tbltype is table of number(10)
    index by pls_integer;

    procedure up_test_inpkg(
    pi_nParam1        IN  NUMBER,
    pi_szParam1       IN  VARCHAR2,

    po_RecordSet      OUT TblType
    );
end upk_test;
/

create or replace package body upk_test
as
    procedure up_test_inpkg(
    pi_nParam1        IN  NUMBER,
    pi_szParam1       IN  VARCHAR2,

    po_RecordSet      OUT TblType
    ) as
    begin

    /**
    business logic 구현
    ......
    **/
    
    --넘겨줄 처리결과 코드 세팅
    po_RecordSet(1) := v_nResult;

    end up_test_inpkg;
end upk_test;
----------------------------------------------------------------

2. sqlserver에서 openquery를 통해 query
----------------------------------------------------------------
SELECT *
FROM  OPENQUERY(ls_name , '{CALL  user1.upk_test.up_test_inpkg(1111,''222'', {resultSet 1, po_RecordSet }}');
----------------------------------------------------------------

CASE2) oracle에서 package에 type선언만 하고 해당 type을 sp에서 참조
----------------------------------------------------------------
create or replace package upk_types
as
    type numtbltype is table of number(10)
    index by pls_integer;
end upk_types;
/

create or replace procedure up_test(
    pi_nParam1        IN  NUMBER,
    pi_szParam1       IN  VARCHAR2,

    po_RecordSet      OUT upk_type.numtbltype
)
as
    
begin
    /**
    business logic 구현
    ......
    **/
    
    --넘겨줄 처리결과 코드 세팅
    po_RecordSet(1) := v_nResult;
end;

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

관련 link : http://dialup.egloos.com/927560

: PostgreSQL Procedure

SELECT * FROM OPENQUERY(<Linked Server Name>, 'SELECT * FROM <Procedure Name>');