목차 1. 개요 목적 사용환경 특장점 데이터베이스생성 데이터베이스이름 데이터베이스생성 데이터베이스이전 사용법 스키마

Transcription

1

2 목차 1. 개요 목적 사용환경 특장점 데이터베이스생성 데이터베이스이름 데이터베이스생성 데이터베이스이전 사용법 스키마 예약어 타입 제약조건 질의 연산자와함수 연산자 함수 일련번호 Stored Procedure(Procedure & Function) 환경설정 작성방법 프로시저 / 펑션작성 성능 힌트 rownum

3 9.3 정렬 max, min covering index 질의수행계획 응용 interface 연결포트 JDBC PHP HA 구성시고려사항

4 1. 개요 1.1 목적 ORACLE 사용자들이 CUBRID 로의이전을보다쉽게하기위해만들어졌으며, ORACLE 과 CUBRID 사용시차이점을주로정리하였으며, 일반적으로사용되는함수들중에서차이가있는부분에대하여정리하여, 개발자들의불편을최소화할수있도록하였습니다. 또한함수의경우, 모든부분을다루기는어려우므로관련함수의지원여부에대하여는매뉴얼을참조하시면됩니다. 이하문서의편의를위해존칭은생략한다. 1.2 사용환경 CUBRID : 9.0 이상 (2008 R4.4 일부포함. 기능에대하여미지원여부표기 ) JAVA : 1.6 이상 (Stored Procedure 사용시, 데이터베이스서버환경, 응용환경아님 ) 2. 특장점 CUBRID 는보편적인 RDBMS 구조를가지고있으며, 지원되는주요기능들에대하여 정리하였다. 구분 기능 SQL ANSI SQL(SQL-92 기준, SQL-99/2003 호환 ) 용량 테이블개수, 레코드건수제약없슴 data type char, varchar, int, numeric(number), etc LOB(CLOB, BLOB) charset 문자단위의문자처리 (length, substring 등 ) EUC-KR, UTF8 등지원 * 9.0 아래버전은 byte 단위문자처리 transaction record based locking commit/rollback/savepoint 별도의트랜잭션시작구문없이, 임의의질의수행시트랜잭션시작됨 auto commit 은언어별설정에따름 Backup on-line(hot)/off-line(cold) backup Full/Incremental backup Time based recovery Full recovery ( 반드시백업이존재하여야, 복구를할수있다 ) Miscellaneous Partition data encryption 3

5 Stored Procedure(JAVA) HA 자체지원별개의데이터베이스 (shared nothing) 로스토리지장애에유연함 Sharding scale out 형태의데이터분산 ( 수평분할 ) API 미지원 JDBC, ODBC, PHP,.NET, C-API, Ruby, Phython, etc DB link, Temporary Table, Parallel Query, Materialized View, Synonym 3. 데이터베이스생성 3.1 데이터베이스이름 대소문자구분하므로, 생성시대소문자에신경을써야한다. 응용에서연결시에도대소문자를구분한다. 3.2 데이터베이스생성 1 개의데이터베이스에 1 개의인스턴스만을가진다. 따라서여러개의인스턴스가필요하다면여러개의데이터베이스를생성하여야한다. 생성후바로백업을하는것이좋다. 백업이있어야만원하는시점으로복구를할수가있기때문이다. 테이블스페이스는자동설정되므로, 별도로지정할필요없다. 필요한데이터양을산정후, 실제데이터베이스의크기는 x1.5 를해주면된다. 데이터, 인덱스, 질의처리공간 (temp) 별도생성해주어야한다. 인덱스공간도산정을통해계산하는것이좋으며, 그렇지못한경우데이터의 20~30% 정도로산정할수있다. 질의처리공간은통상적으로데이터의 10% 수준으로생성한다. 예 ) 데이터베이스이름 : mydb, 데이터 2G, 인덱스 1G 의경우, /DB 아래생성 cubrid created F /DB mydb cubrid addvoldb S p data --db-volume-size=2g mydb cubrid addvoldb S p index --db-volume-size=1g mydb cubrid addvoldb S p temp --db-volume-size=200m mydb 4. 데이터베이스이전 데이터베이스이전은 CMT(CUBRID Migration Toolkit) 을이용하여이전할수있다. 이는 1:1 이전 ( 이전전후의스키마와데이터가동일 ) 만이가능하다. 또한 CMT 를이용하여전환가능한대상데이터베이스는 ORACLE, MySQL, MS-SQL 이가능하다. 4

6 업무에맞게변경하는것은, 업무를파악하고업무에맞게조정되어야하므로이전후별도의과정을통해응용단에서처리하여야한다. 4.1 사용법 사용법은비교적간단하며, ftp.curid.org 에서다운받을수있다. 여기서는기본적인사용법을제시하며, 보다자세한내용은매뉴얼을참고하기바란다. 1. 마이그레이션유형선택 2. 소스데이터베이스연결설정 5

7 3. 대상데이터베이스연결설정 6

8 4. 객체매핑 스키마정보는기본적으로모두소문자로적용된다. 실제질의에서대소문자는구분하지 않으므로문제되지는않는다. 5. 내용확인 7

9 6. 마이그레이션진행 8

10 7. 완료 완료후보고서를볼수있으며, 만약에러가발생한경우붉은색으로표시되며, 상세를 클릭해보면에러내용을확인해볼수있다. 9

11 5. 스키마 5.1 예약어 예약어는일부상이하며, 사용시, ``( 백틱, 작은따옴표가아닌 ~ 키에있는 ) 또는 [] 로감싸서사용하면된다. 일반적으로타입명, 함수명등이예약어가된다. 자세한예약어리스트는매뉴얼을참고한다. 5.2 타입 대부분유사하나, 명칭이다르거나지원되지않는부분에대하여사용가능한타입을정리한다. NULL 과 는서로다르게취급하므로, not null default 을이용하거나하여야한다. ORACLE CUBRID 비고 bit BIT BIT 로사용 boolean number smallint, int, bigint, float/real, double, numeric smallint:2byte int: 4byte bigint: 8byte numberic: 최대 38 자리 ( 소수점포함 ) * unsigned 미지원 date date time datetime date: 날자부분만포함 time: 시간부분만포함 datetime: 날자, 시간포함하며, 1/1000 초 lob lob 외부에파일로저장. 내부에저장하기위해서는 clob varchar, blob bit varying 사용 char char 고정길이이거나, 인덱스로사용될때사용고려 varchar, varchar2 varchar, string: varchar(1g) 와동일 string long varchar CUBRID varchar 는 1G 까지지원 5.3 제약조건 일반적인제약조건들은지원되며, 차이가있는부분들에대하여정리한다. Key : PK, FK 모두지원하며, key 로지정시인덱스로도같이사용되므로별도로인덱스를생성할필요없다. Primary key : 모든컬럼의값이 not null 로설정된다. 즉, 멀티컬럼으로 PK 를구성할경우어느컬럼의값도 null 이허용되지않는다. Foreign key : on update 에대하여 cascade 를지원하지않는다. 10

12 Check : 컬럼속성중 check 속성은지원하지않는다. Constraint : key 이름지정을위한 constraint 는컬럼속성뒤에명시할수없고, 별도로명시하여야한다. my_col int constraint pk primary key(my_col) : 지원않됨. my_col int, constraint pk primary key(my_col) 제약조건을비활성화할수없다. 6. 질의 질의사용시사용법이다르거나, 지원되지않는부분에대하여정리한다. ORACLE CUBRID 비고 dual connect by with regexp_replace for update SELECT SUM(COUNT(col_1)) FROM tbl_1 GROUP BY col_1 db_root 또는 from 절없이사용 connect by SELECT SUM(col_2) FROM (SELECT COUNT(col_1) AS col_2 FROM tbl_1 GROUP BY col_1) select sysdatetime 지원않함지원않함지원않함. 응용에서 write lock 을설정하는형태로변경집계함수중복사용불가 from a, b where a.i = b.i(+) from a left outer join b on a.i = b.i outer join 시 ANSI 표준사용권장 from a full outer join b on a.i = b.i full outer join 지원하지않음 case when exists (select 1 from ) case when 1=(select count(*) from case 문에서 exist 는지원하지않음. 11

13 order by col nulls first 9.2 부터 nulls first/last 지원 CUBRID 에서 null 은제일작은값이다. 9.1 이하에서는 order by isnull(col), col 7. 연산자와함수 7.1 연산자 ORACLE CUBRID 비고 = = <=>!=!= <> <> ^= concat concat + minus Difference <=>: null 과비교시 is null 을사용하지 않아도가능 문자열합치기 Intersect Intersection 1/2 = 0.5 1/2 = 0 정수연산시둘다정수이므로, 결과를 정수로넘겨줌 datetime 연산시숫자는최소단위인 1/1000 초로계산 7.2 함수 함수는종류가많아모두다열거하기는어렵다. 일반적으로많이사용되는함수를기반으로정리하였으며, 지원되는모든함수의목록은매뉴얼을참고한다. ORACLE CUBRID 비고 sysdate to_date instr sysdate systime sysdatetime to_date to_time to_datetime instr position 사용되어지는데이터타입에맞게사용 사용되어지는데이터타입에맞게사용 instr() 의경우 3 개의아규먼트만지원한다. 12

14 dbms_random.value rand d 가 붙은 것은 0~1 사이의 실수를 drand random 넘겨준다. rand() 는 질의 기반으로 난수를 drandom 만들어주며, random() 은질의결과레코드 기반으로난수를만든다. sys_guid to_char(random(), ) + to_char(sys_datetime, YYYYMMDDHH24MISSFF) + to_char(random()%10000, ) sys_extract_utc 지원하지않음 regexp_replace xml 함수 지원하지않음 지원하지않음 over over 함수 9.1 부터지원되며, window 절은현재 지원되지않음. 7.3 일련번호 ORACLE CUBRID 비고 sequence serial auto increment 데이터입력시자동증가속성을부여하면, 해당컬럼의값이자동으로주어진값만큼증가 create table my_tbl ( id int auto_increment, name char(10) ) insert into my_tbl(name) values( name1 ) id 값은자동부여되며, 순차증가 8. Stored Procedure(Procedure & Function) Stored procedure 구현을위해사용되는언어는 java 이다. 따라서 PG-SQL 구문을 java 구문으로변경하여사용한다. 배치작업을위한 procedure 에서의사용은무난하지만, function 등의사용은권장하지않는다. 13

15 8.1 환경설정 아래 2 개의환경변수가설정되어있어야하며, 적용을위해서는 CUBRID 서비스를재구동해야반영된다. JAVA_HOME=/usr/java/jdk1.6.0_10 LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64:$JAVA_HOME/jre/lib/amd64/server 8.2 작성방법 JDK1.6 이상 프로스져내부에서트랜잭션명령 (commit, rollback) 을사용할수없다. 사용하여도무시되므로, 트랜잭션처리는프로시져외부에서하여야한다 프로스저 / 펑션등록 컴파일된 java class 를 loadjava 를이용하여등록한다. loadjava mydb mysp.class 프로시져를등록한다. create procedure myprocecure (name varchar) as language java name mysp.myprocecure(java.lang.string) ; create function myfunc(id int) return varchar as language java name mysp.myfunc(int) return int ; 8.3 프로시저 / 펑션작성 java method 작성과동일한방법을사용하면된다. 굵은글씨로표시된부분만작성시유의하면된다. 프로스저 / 펑션으로넘어오는인자값과리턴값에대한처리는일반 java method 와동일하게하면된다. 다음은펑션작성예이다. import java.sql.*; public class mysp { public static void myfunc(int args) throws Exception { Connection conn = null; String ret_val = null; try { Class.forName("cubrid.jdbc.driver.CUBRIDDriver"); conn = DriverManager.getConnection("jdbc:default:connection:"); 14

16 } } return ret_val; } catch ( SQLException e ) { System.err.println(e.getMessage()); } catch ( Exception e ) { System.err.println(e.getMessage()); } finally { if ( conn!= null ) conn.close(); } 9. 성능 성능에관련하여지원되는기능및질의작성시성능을위해고려할부분도정리한다. 9.1 힌트 조인힌트 ORACLE 과동일한형태로사용하며, 조인대상한정이가능하며, 지원되는구문은다음과같다. USE_IDX : 일반적인 netsted loop 조인시, 조인되어지는테이블의조인조건에인덱스가있는경우반드시인덱스를사용하여조인을한다. USE_MERGE : sort merge join 을사용한다. USE_NL : nested loop join 을사용한다. ORDERED : from 절에명시된순서대로 join 을수행한다 인덱스힌트 ORACLE 과달리 USING INDEX 라는구문을사용하며, 사용법은다음과같다. Where USING INDEX a.idx1, b.idx2(+) Order by 주어진인덱스만있는것으로간주하여, 주어진인덱스와풀스캔중비용이저렴한것을선택한다. 조인시특정테이블에대한인덱스만지정할수있으며, 이경우지정하지않은테이블은옵티마이저가선택한다. 주어진인덱스명이잘못된경우에러를발생한다. 15

17 (+) 를이용하여인덱스사용비용을 0 으로만들수있다. 이는해당인덱스사용을강제하는효과를얻을수있다. 9.2 rownum ORACLE 과달리 rownum between 10 and 20 과같은형태로사용할수있다. 다만정렬전에생성이되어, 정렬과함께사용시사용법이좀복잡해지므로 MySQL 형태의 limit 를사용하면훨씬편리하다. 또한, inline view 를사용하지않아도된다. ORACLE select rn, * from ( select rownum rn, * from ( select * from tbl where order by ) where rownum <= 20 ) where rn >= 11 CUBRID select orderby_num() rn, * from tbl where order by limit 11, 20 정렬후정렬된순번을얻고자한다면, orderby_num() 을사용하면된다. 9.3 정렬 정렬을하는경우, 정렬을위한비용이상당하므로정렬을피하기위해정렬대상과검색조건을인덱스로생성하게되면, CUBRID 옵티마이저가해당인덱스를사용하여정렬을회피할수있다. 이를통해검색성능을올릴수있다. 다만, 모든경우에대하여성능향상이이루어지지않을수있으므로, 반드시질의수행계획및성능개선여부를확인하여야한다. where id = 1 order by name desc create index idx1 on tbl(id, name desc) 9.4 max, min max, min 대상을인덱스로생성시인덱스를이용하여 max, min 값을얻을수있으므로성능향상을꾀할수있다. Select max(id) from tbl Create index idx1 on tbl(id) 16

18 9.5 covering index 검색에필요한정보가인덱스에모두존재하는경우, 인덱스정보만으로데이터를확인할수있으므로성능향상을얻을수있다. 특히, 코드에대한이름을얻는형태의조인의경우많은도움이될수있다. select name from tbl where id = 1 create index idx1 on tbl(id, name) 9.6 질의수행계획 질의수행계획을보는방법에대하여간단히정리한다. 자세한내용은매뉴얼을참고하기바란다. 1 질의수행계획및통계정보를보기위한버튼. 2 athelete table 에대하여 full scan 을하였으며, game table 에대하여는 fk_game_athlete_code 를이용하여인덱스스캔을하였다는의미 3 질의수행을위해발생한 I/O 양이 페이지 (CUBRID I/O 단위 ) 이며, 그중최종결과에는사용되지않는페이지는 8683 페이지라는의미. 결국이 I/O 량을줄이는것이제일중요. 10. 응용 interface 응용개발을위한 driver 사용시고려되어야할부분을정리한다 연결포트 CUBRID 응용에서는데이터베이스와연결을할때반드시포트를지정해주어야한다. 기본적으로제공되어지는포트는 번이므로이를사용하면된다. 만약하나의서버에여러개의데이터베이스를가지게되면, 데이터베이스마다별개의포트를사용하는것을권장한다. 17

19 CUBRID 는 3-tier 구조를가지며, broker 라는미들웨어에서실제데이터베이스와연결하여작업을수행한다. broker 에서는데이터베이스와의연결에대하여 pooling 기능을가지고있으므로, 이기능을제대로활용하기위해 broker 별로데이터베이스연결을따로가지도록하는것이다. 응용에서는이 broker 와연결을하여작업을처리하게되므로, 데이터베이스별로별개의 broker 를구성하고그 broker 에주어진포트를사용하면된다. broker 를추가하는방법은별도로매뉴얼을참고하면된다 JDBC JAVA 1.6 이상을지원하며, 일반적은작성방법은 JDBC 표준을따른다. 연결문자열은다음과같으며, charset 을지정해주어야문자셋이깨지지않고저장될수있다. HA 환경에서는 url_ha 형태와같이, althosts 를 stand-by 서버로지정해주어야한다. import java.sql.*; import cubrid.jdbc.driver.*; String url = jdbc:cubrid: :33000:demodb:::?charset=utf8 ; String url_ha = jdbc:cubrid: :33000:demodb:::? althosts= :33000& charset=utf8 ; Class.forName( cubrid.jdbc.driver.cubriddriver ); Connection conn = DriverManager.getConnection(url, db_user, dbpw ); 10.3 PHP PHP 의경우함수에대한표준이없다. 다만대부분의함수들이 prefix 부분을제외하고는유사하므로, 기존사용함수의 prefix 부분만 cubrid 로변경하여검색해보면된다. HA 를사용할경우, url 형식의연결문자열 (JDBC 참고 ) 을사용하는 cubrid_connect_with_url 이라는함수를사용하여야하며, 사용방법은다음과같다. HA 를사용하지않더라도해당함수는사용가능 (althosts 부분만제거 ) 하다. $url_ha = jdbc:cubrid: :33000:demodb:::? althosts= :33000& charset=utf8 ; $con = cubrid_connect_with_url(url, db_user, dbpw ); 기본적으로자동커밋모드로동작한다. 자동커밋을끄기위해서는 cobrid_set_autocommit($con, CUBRID_AUTOCOMMIT_FALSE) 를이용하거나, 연결문자열뒷부분에 autocommit=false 를추가해주면된다. 18

20 11. HA 구성시고려사항 장애대비를위해자체적으로지원되고있는 HA 를사용시주의할사항을정리한다. 주의사항 비고 플랫폼 Primary key trigger lob LINUX, AIX 에서만사용가능하며, 동일한플랫폼끼리만 HA 구성이가능하다. 스토리지장애에도대처하기위해, 스토리지를공유하지않은방식을사용한다. 따라서서로다른별개의데이터베이스를복제를통해데이터동기를유지하므로, 반드시 PK 가존재하여야한다. 9.2 부터사용가능 lob 데이터가별도의파일로저장되며, 이파일이복제되지않으므로사용할수없으며, bit varying 을사용하여야한다. 19