오라클 to CUBRID로 마이그레이션 수행 시 주의사항

ORACLE

select * from (select sysdate from dual)

CUBRID

select * from (select sysdate from dual) tbl

ORACLE

delete tbl where ...

CUBRID

delete from tbl where ...

ORACLE

select * from a connect by prior id = pid start with id = 1

CUBRID

select * from a connect by prior id = pid start with id = 1
--> ERROR: syntax error, unexpected START_

select * from a start with id = 1 connect by prior id = pid

ORACLE

select id from a order by id 시 결과 순서 : 1, 2, 3, ..., NULL 
select id from a order by id nulls first : NULL, 1, 2, 3, ...

CUBRID

select id from a order by id 시 결과 순서 : NULL, 1, 2, 3, ...

ORACLE

select /*+ tbl ix_tbl01 */ from tbl, tbl2
where tbl.i = 1 and tbl.j = tbl2.j
ix_tbl01 을 tbl 검색시 사용하고, tbl2 에 대하여는 optimizer 가 알아서 선택.

CUBRID 8.2.x~8.3.x버전

select from tbl, tbl2
where tbl.i = 1 and tbl.j = tbl2.j
using index tbl.ix_tbl01, tbl2.ix_tbl2_j
ix_tbl01 을 tbl 검색시 사용하고, tbl2 에 대하여는 full scan 일어나므로 tbl2에 대한 인덱스도 반드시 지정해주어야 한다.

CUBRID 8.4.0 이상 버전

select from tbl, tbl2
where tbl.i = 1 and tbl.j = tbl2.j
using index tbl.ix_tbl01;
ix_tbl01 을 tbl 검색시 사용하고, tbl2 에 대하여는 optimizer 가 알아서 선택.

ORACLE

select /*+ INDEX_DESC(tbl ix_tbl01) */ from tbl, tbl2
where tbl.i = 1 and tbl.j = tbl2.j

CUBRID 8.2.x~8.3.x버전

select /*+ USE_DESC_IDX */ from tbl, tbl2
where tbl.i = 1 and tbl.j = tbl2.j
using index tbl.ix_tbl01, tbl2.ix_tbl2_j

CUBRID 8.4.0 이상 버전

select /*+ USE_DESC_IDX */ from tbl, tbl2
where tbl.i = 1 and tbl.j = tbl2.j
using index tbl.ix_tbl01;

ORACLE

create table tbl (id int constraint pk_id primary key); 
create table tbl2 (id int constraint fk_id references tbl(id));

CUBRID

create table tbl (id int constraint pk_id primary key); 
create table tbl2 (id int foreign key references tbl(id));

ORACLE

sys user로 로그인
create table usera.tbl ( ...
소유자는 usera

CUBRID

dba 유저로 로그인
create table usera.tbl (...
소유자는 dba.

GRANT ALL PRIVILLEGES ON tbl TO usera;

usera에게 tbl에 대한 모든 권한을 부여해야 함.

ORACLE

sys user로 로그인
create index usera.idx on usera.tbl ( ... 
소유자는 usera

CUBRID

dba 유저로 로그인 
create index usera.idx on usera.tbl ( ...

--> 에러 발생함. 테이블 소유자가 인덱스 소유자가 되므로, 인덱스 권한을 별도로 부여할 수 없음 
create index idx on usera.tbl ( ...

--> OK

ORACLE

select a, id, count(*) over() from tbl where a>0 and a<101;

CUBRID

8.4.1 이하 버전

select a, id, cnt from tbl, (select count(*) from tbl where a>0 and a<101) t(cnt) where a>0 and a<101;

ORACLE

MERGE INTO t_Target A
using
(
select key1, key2, value1, value2 
from t_Base
where key1 = 'abc' 
) B
ON (A.key1 = B.key1 and A.key2 = B.key2)
WHEN MATCHED THEN 
update set 
A.value1 = B.value1,
A.value2 = B.value2
WHEN NOT MATCHED THEN 
insert (A.key1 , A.key2 , A.value1, A.value2)
values (B.key1 , B.key2, B.value1, B.value2)

ORACLE

sleect SYS_GUID() from dual;
SYS_GUID() 
--------------------------------
B4CBD3E9BEA52132E040007F01003B9D

ORACLE

select id from tbl for update wait 10

write lock 을 잡기 위해 10초동안 기다림. 10초 초과시 에러.
update tbl set id = ...