레이블이 SQL인 게시물을 표시합니다. 모든 게시물 표시
레이블이 SQL인 게시물을 표시합니다. 모든 게시물 표시

2025년 4월 8일 화요일

MSSQL - Cursor vs Temp Table 


#테이블 변수사용의 예


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


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


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

print @print



#커서사용의 예


declare @title varchar(100)
declare @print varchar(5000)

set @print = ''
declare c_cursor CURSOR FOR
   select title from titles
for read only

open c_cursor
fetch next from c_cursor
into @title

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

  into @title
end

close c_cursor
deallocate c_cursor

print @print


출처 : https://using.tistory.com/14



시간:
라벨: ,

2025년 3월 28일 금요일

MSSQL - SQL Server에서 Oracle 연결

 

1) 개체 탐색기에서 서버 > 서버 개체 > 연결된 서버를 오른쪽 마우스 클릭하여 새 연결된 서버를 선택한다.


2) 일반 정보 설정
연결된 서버(N) : 연결명
공급자(P) : Microsoft OLE DB Provider for Oracle
제품 이름(U) : Oracle
(오라클인 경우에는 Oracle)
데이터 원본(D) : 오라클 서비스 네임 즉, sid
(orcl)
공금자 문자열(V) : 오라클 서비스 네임 즉, sid
(orcl)


3) 보안 설정
원격지 SQL Server의 접속 정보(원격로그인 / 암호)를 입력한다. 

4) 서버 옵션 설정
페이지 선택에서 서버 옵션을 선택합니다. RPC(Remote Procedure Call), RPC 내보내기를 True로 변경합니다. RPC는 원격 프로시져를 호출 할 수 있도록 하는 옵션입니다. 확인버튼을 눌러 DB 링크를 생성합니다. 생성을 하면 연결 테스트를 자동 수행합니다.


5) 확인버튼을 클릭하여 Linked Server 생성 완료하기. (자동으로 연결 테스트를 수행하여,테스트에 실패하면 오류가 발생한다.)

6) 로컬서버 > 서버 개체 > 연결된 서버를 보면 새로운 Linked Server 가 생성된 것을 확인할 수 있다.

※ 쿼리 사용법 (SELECT문)
-- Oracle은 dbo개념이 없기 때문에, FROM 절에서 dbo를 생략하여 .를 연속으로 2개 입력한다. (오타가 아님!) SELECT * FROM [Linked Server명]..[DB명].[테이블명] EX) Linked Server명 : ORACLE, DB명 : TEST, 테이블명 : EMP 인 경우, EMP 테이블의 모든 데이터 조회하기 SELECT * FROM ORACLE..TEST.EMP;



[출처] https://m.blog.naver.com/sophie_yeom/220903408641




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




테스트환경 :
MSSQL 2008 (64bit)
Oracle 11.2.0.1 (64bit)

사전준비 사항
MSSQL이 설치된 서버에 서버의 bit수와 동일한 Oracle Client가 설치되어 있어야 합니다.


설치사이트

64bit의 경우
https://www.oracle.com/kr/database/technologies/112010-win64soft.html


32bit의 경우

https://www.oracle.com/database/technologies/112010-win32soft.html

 

Oralce Client 설치


1.Oracle Client를 MSSQL 서버에 설치합니다.(Custom에 놓고 Next를 합니다)

 

2.언어선택인데 저는 English를 선택 후 Next를 했습니다.(Korean이 있으면 해도 무방합니다)

 

3.ORACLE_BASE와 ORACLE_HOME을 선택하는 창입니다. Default로 하고 Next를 누릅니다.

4.필요한 부분을 선택합니다. 이부분이 가장 중요합니다.

Oracle Database Utilities

SQL*Plus

Oracle JDBC/THIN interfaces

Oracle Call Interface(OCI)

OracleNet

Oracle ODBC Driver

Oracle Counters for Windows Perfoemance Monitor

Oracle Objects for OLE

Oracle Provider for OLE DB

Oracle Data Provider for .NET

 

 

5.설치를 진행합니다.

 

MSSQL 설정

1. DB접속 후 Server Objects -> Linked Servers -> Providers에 OraOLEDB.Oracle이 있는지 확인해줍니다.

 

2.Client의 SQLNET.ora를 수정합니다.

아래의 내용 추가

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAMES)
SQLNET.EXPIRE_TIME = 30

 

3.Client의 TNSNAMES.ORA에 Oracle DB의 접속정보를 추가합니다.

YSBAE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.58)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ysbae)
    )
  )

 

 

tnsping ysbae와 sqlplus test/test@ysbae를 통해서 접속 테스트를 진행하여도 됩니다.

 

4. DB LINK를 생성합니다. New Linked Server..를 클릭합니다.

 

 

 

5General 내용을 작성합니다.

Linked server : DB링크 이름

Provider : Oracle Provider for OLE DB

Data source : TNS 이름

 

6. Security 내용을 작성합니다.

Remote Login,With password칸에 Oracle DB의 스키마 이름과 비밀번호를 입력합니다.

 

7.Server Options내용을 작성합니다.  설정이 완료되었으므로 OK를 클릭 후 테스트를 진행합니다.

RPC RPC Out을 TRUE로 설정합니다.

8. Oracle의 sysdate를 구하는 쿼리로 테스트를 진행합니다.

select * from OPENQUERY(DBLINK_TEST,'select sysdate from dual');

 

 

[출처] https://bae9086.tistory.com/128




시간:
라벨: ,

2025년 3월 25일 화요일

SQLP #8 - 일반

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
drop table 고객 ;
drop table 주문;
 
create table 고객(
    고객번호 varchar2(10) primary key,
    고객명 varchar2(100) ,
    연락처 varchar2(12) ,
    거주지역코드 varchar(2)     
) ;
 
create table 주문(
   주문번호 varchar(20) primary key ,
   고객번호 varchar2(10) not null, 
   주문일시 timestamp ,
   주문금액 number ,
   배송지 varchar2(100)  ,
   constraint fk_주문 foreign key(고객번호) references 고객(고객번호)      
) partition by range(주문일시)
(
partition p3 values less than (timestamp '2015-04-01 00:00:00.000000' ) ,
partition p4 values less than (timestamp '2015-05-01 00:00:00.000000' ) ,
partition p5 values less than (timestamp '2015-06-01 00:00:00.000000' ) ,
partition p6 values less than (timestamp '2015-07-01 00:00:00.000000' ) ,
partition p7 values less than (timestamp '2015-08-01 00:00:00.000000' ) ,
partition p8 values less than (timestamp '2015-09-01 00:00:00.000000' ) ,
partition p9 values less than (timestamp '2015-10-01 00:00:00.000000' ) ,
partition p10 values less than (timestamp '2015-11-01 00:00:00.000000' ) ,
partition p11 values less than (timestamp '2015-12-01 00:00:00.000000' ) ,
partition p12 values less than (timestamp '2016-01-01 00:00:00.000000' ) ,
partition pmax values less than (maxvalue) ); 
 
 
 
insert into 고객 
select object_id, object_name, '0100001004' , lpad(namespace, 2 ,'0')   from all_objects
where rownum <= 10000 ;
 
update 고객 set 고객명 = '김철수' , 거주지역코드 = '02'
where  고객번호 = '3' ;
 
update 고객 set 고객명 = '홍길동', 거주지역코드 = '05'
where  고객번호 = '6' ;
 
commit;
 
truncate  table 주문;
insert into 주문
select lpad(rownum, 20, '0' )
     , round(dbms_random.value(3, 6)) 
     , add_months(created, -24) + rownum 
     , round(dbms_random.value(10000, 50000)) 
     , object_type 
from all_objects
where rownum <= 20000 ; 
 
select * from 고객;
select * from 주문;
 
 
select /*+ ordered gather_plan_statistics*/o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지
from 고객 c, 주문 o 
where o.주문일시 between to_date('20150301', 'yyyymmdd')
and to_date('20150314235959', 'yyyymmddhh24miss')
and o.고객번호 = c.고객번호
and c.거주지역코드 || c.고객명 in ('02김철수', '05홍길동')
order by o.주문일시, c.고객명 ;
------------------------------------------------------------------------------------------------------------------------
--| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
--|   0 | SELECT STATEMENT         |      |      1 |        |      1 |00:00:00.03 |     108 |       |       |          |
--|   1 |  SORT ORDER BY           |      |      1 |      1 |      1 |00:00:00.03 |     108 |  2048 |  2048 | 2048  (0)|
--|*  2 |   HASH JOIN              |      |      1 |      1 |      1 |00:00:00.03 |     108 |  1335K|  1335K|  538K (0)|
--|*  3 |    TABLE ACCESS FULL     | 고객 |      1 |    199 |      2 |00:00:00.02 |      68 |       |       |          |
--|   4 |    PARTITION RANGE SINGLE|      |      1 |      1 |      6 |00:00:00.01 |      37 |       |       |          |
--|*  5 |     TABLE ACCESS FULL    | 주문 |      1 |      1 |      6 |00:00:00.01 |      37 |       |       |          |
------------------------------------------------------------------------------------------------------------------------
 
select /*+ ordered gather_plan_statistics*/o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지
from 고객 c, 주문 o 
where o.주문일시 between to_date('20150301', 'yyyymmdd')
and to_date('20150314235959', 'yyyymmddhh24miss')
and o.고객번호 = c.고객번호
--and c.거주지역코드 || c.고객명 in ('02김철수', '05홍길동')
and (c.거주지역코드 , c.고객명 ) in ( ('02','김철수'), ('05','홍길동'))
order by o.주문일시, c.고객명 ;
 
create index 고객_ix01 on 고객(거주지역코드, 고객명) ;
create index 주문_ix01 on 주문(고객번호, 주문일시) ;
--주문 글로벌인덱스 사용
---------------------------------------------------------------------------------------------------------------------------------------------------
--| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
--|   0 | SELECT STATEMENT                        |         |      1 |        |      1 |00:00:00.01 |      10 |     19 |       |       |          |
--|   1 |  SORT ORDER BY                          |         |      1 |      1 |      1 |00:00:00.01 |      10 |     19 |  2048 |  2048 | 2048  (0)|
--|   2 |   NESTED LOOPS                          |         |      1 |      1 |      1 |00:00:00.01 |      10 |     19 |       |       |          |
--|   3 |    NESTED LOOPS                         |         |      1 |      1 |      1 |00:00:00.01 |       9 |     19 |       |       |          |
--|   4 |     INLIST ITERATOR                     |         |      1 |        |      2 |00:00:00.01 |       5 |      8 |       |       |          |
--|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| 고객    |      2 |      1 |      2 |00:00:00.01 |       5 |      8 |       |       |          |
--|*  6 |       INDEX RANGE SCAN                  | 고객_IX0|      2 |      1 |      2 |00:00:00.01 |       4 |      8 |       |       |          |
--|*  7 |     INDEX RANGE SCAN                    | 주문_IX0|      2 |      1 |      1 |00:00:00.01 |       4 |     11 |       |       |          |
--|   8 |    TABLE ACCESS BY GLOBAL INDEX ROWID   | 주문    |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------
 
--주문 로컬인덱스 사용
drop index 주문_ix01 ;
create index 주문_x1 on 주문(고객번호, 주문일시) local ;
------------------------------------------------------------------------------------------------------------------------------------------
--| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
--|   0 | SELECT STATEMENT                        |         |      1 |        |      1 |00:00:00.01 |       8 |       |       |          |
--|   1 |  SORT ORDER BY                          |         |      1 |      1 |      1 |00:00:00.01 |       8 |  2048 |  2048 | 2048  (0)|
--|   2 |   NESTED LOOPS                          |         |      1 |      1 |      1 |00:00:00.01 |       8 |       |       |          |
--|   3 |    NESTED LOOPS                         |         |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
--|   4 |     INLIST ITERATOR                     |         |      1 |        |      2 |00:00:00.01 |       5 |       |       |          |
--|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| 고객    |      2 |      1 |      2 |00:00:00.01 |       5 |       |       |          |
--|*  6 |       INDEX RANGE SCAN                  | 고객_IX0|      2 |      1 |      2 |00:00:00.01 |       4 |       |       |          |
--|   7 |     PARTITION RANGE SINGLE              |         |      2 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
--|*  8 |      INDEX RANGE SCAN                   | 주문_X1 |      2 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
--|   9 |    TABLE ACCESS BY LOCAL INDEX ROWID    | 주문    |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
 
select /*+ ordered  use_concat gather_plan_statistics*/o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지
from 고객 c, 주문 o 
where o.주문일시 between to_date('20150301', 'yyyymmdd')
and to_date('20150314235959', 'yyyymmddhh24miss')
and o.고객번호 = c.고객번호
--and c.거주지역코드 || c.고객명 in ('02김철수', '05홍길동')
--and (c.거주지역코드 , c.고객명 ) in ( ('02','김철수'), ('05','홍길동'))
and ((c.거주지역코드 = '02' and c.고객명 = '김철수') or (c.거주지역코드 = '03' and  c.고객명 = '홍길동') )
order by o.주문일시, c.고객명 ;
 
------------------------------------------------------------------------------------------------------------------------------------------
--| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
--|   0 | SELECT STATEMENT                        |         |      1 |        |      1 |00:00:00.01 |       7 |       |       |          |
--|   1 |  SORT ORDER BY                          |         |      1 |      1 |      1 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
--|   2 |   NESTED LOOPS                          |         |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |
--|   3 |    NESTED LOOPS                         |         |      1 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
--|   4 |     INLIST ITERATOR                     |         |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
--|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| 고객    |      2 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
--|*  6 |       INDEX RANGE SCAN                  | 고객_IX0|      2 |      1 |      1 |00:00:00.01 |       4 |       |       |          |
--|   7 |     PARTITION RANGE SINGLE              |         |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--|*  8 |      INDEX RANGE SCAN                   | 주문_X1 |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
--|   9 |    TABLE ACCESS BY LOCAL INDEX ROWID    | 주문    |      1 |      2 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------
 
--Predicate Information (identified by operation id):
-----------------------------------------------------
--
--6 - access(("C"."고객명"='김철수' AND "C"."거주지역코드"='02' OR "C"."고객명"='홍길동' AND "C"."거주지역코드"='03'))
--8 - access("O"."고객번호"="C"."고객번호" AND "O"."주문일시">=TIMESTAMP' 2015-03-01 00:00:00' AND "O"."주문일시"<=TIMESTAMP' 2015-03-14
--23:59:59')
 
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) ;
 
 
 
cs


조건절을 가공하면 인덱스를 사용할 수 없다.

OR 또는 UNION ALL 을 사용할 수 있다. OR 사용시 USE_CONCAT 힌트를 같이사용해야 한다.

주문은 파티션 테이블이다. 위와 같을때 일반글로벌인덱스보다 로컬 Nonprefixed 인덱스가 유용할 수 있다.

정확히 하나의 파티션만 읽는다.

보기와 같을때 인덱스는 고객번호 + 주문일시로 하자 , 주문일시를 선두조건으로 두면 데이터가 흩어지므로

인덱스 스캔시 비효율이 발생할 수 있다.

 



[출처] https://weplaythedb.tistory.com/16



시간:
라벨: ,
피드 구독하기: 글 (Atom)

MSSQL - Cursor vs Temp Table

#테이블 변수사용의 예 use pubs go declare @tmptable table (     nid int identity(1,1) not null,     title varchar (80) not null ) -- 테이블 변수 선언 inse...

  • DB - Best SQL Management Tools
    11 SQL Client for Productive Database Administration & Development Working as a web developer or database administrator, often n...
  • Unity - Local DB - C# - Sqlite
    The Class Code (csharp): import          System. Data ;   // we import our  data class import          Mono. Data . Sqlite...