나의 일상생활~

대용량 테이블에서 특정 설명 컬럼에서 값을 조회할 때 사용되는 index다.

create index 인덱

스명 on 테이블명(컬럼명) indextype is ctxsys.context;

위와 같은 식으로  text 인덱스를 사용한다.

데이터 건수가 엄청 많은 테이블에서 text 내용을 검색할 때 유리하다.

총 3400만건의 데이터에서 Dox라는 검색어로 조회를 하니

19초 가량이 걸렸다.

contains 함수를 이용하여, Dox 검색어를 조회하니. 0.7초의 조회 결과가 나온다.

생각보다 괜찮은 것 같다.

결과값이 달라서 확인해보니, contains 함수는 대소문자 구분없이 검색을 하는 듯 하다.

위에 쿼리에 upper 함수를 사용해서 조회를 해보니 245760이 나왔는데.

시간은 36초 걸렸다.

단 요즘에, 18c 오라클에서 같은 트랜잭션 내에서 서로 다른 DB 업데이트를 하는 경우가 있을 때

text index가 설정된 곳에서 분산트랜잭션 오류가 발생하는 경우가 많이 있다.

DBA에게 문의도 해보고 하지만 아직 확실한 결과를 얻지 못했다.

대용량 텍스트 검색에서 사용하면 유용할 듯 싶다.

일을 하다보니. 특정 항목이 varchar형이라, 숫자만이 아니라, 숫자(문자) 형식으로 들어오는 경우가 종종 있다.

예를 들어 아기 몸무게 항목 값에

g)   1200,  1400,  1500 이런식으로 대부분 입력이 되지만

업무하는 부서 특성상 1200(+30) 이런식으로 증가값을 더 입력해서

업무적으로 눈에 띄게끔 하는 부서들이 가끔 있다.

몸무게를 kg으로 변환해서 연동하는 경우에. 오류가 발생한다.

1200(+30)이 문자열이기에 발생한다.

이런 경우가 생길 때, 1200(+30) 에서 1200의 값만 잘라야 하는 경우가 생긴다.

이런경우에 instr, substr을 이용해서, 데이터를 잘라낼 수 있다.

select substr('1200(+30)', 0, instr('1200(+30)', '(') -1)
  dual

;

instr함수를 이용해서 '(' 문자열의 위치를 찾아

substr함수로 처음부터 찾은 위치 이전까지의 문자를 잘라, 1200의 값만 가져온다.

merge into 테이블명

       using dual

                  on ( deptcd = '1000'

                   and seqno = 1

                 ) -- pk값에 대한 정보

       when matched then

               update

                    set  deptnm = 'A부서'

       when not matched then

               inesrt (

                         deptcd

                       , seqno

                       , deptnm

              ) values (

                        '1000'

                       , 1

                       , 'A부서'

              )

데이터가 있는지 여부를 체크해서 없을경우에는 insert를 하고

데이터가 있을 경우에는 update를 해야하는 경우가 많을때 사용한다.

//두시간의 차이를 초로 가져오기.

select

      (trunc(MOD(to_date(a.resusteamcalldd || a.resusteamcalltm, 'yyyymmddhh24miss')
     - to_date(a.cardarrestmdd || a.cardarrestmtm, 'yyyymmddhh24miss'), 1) *24 *60*60))

from a

//위와같이 했을 경우에는 두시간의 차이.. 10분 차이가 나면 600이란 값이 나온다..

시간이나 분단위를 구하기 위해서는..date형식으로 바꾼후 char형식으로 변환해서 format설정을 하면된다..

select

to_char(to_date(trunc(MOD(to_date(a.resusteamcalldd || a.resusteamcalltm, 'yyyymmddhh24miss')
     - to_date(a.cardarrestmdd || a.cardarrestmtm, 'yyyymmddhh24miss'), 1) *24 *60*60), 'sssss'), 'hh24mi')

from a

--위의 포맷은 0010(10분)  0110(1시간 10분) 이런식으로 가져온다..

-- 단, to_date함수에서는 -값이 들어올수 없으므로, 그런경우에는 따로 예외처리를 해주어야한다..

오라클 outer join을 하다보면 가끔 실수를 하는 경우가 있다.

select *

   from inpt  a

       , usrb b

where a.userid = b.userid(+)

    and b.poscd = '1'

위의 쿼리와 같이 outer join을 할때, 키값을 (+)로 outer처리 했으나, b.poscd='1'과같이 b.poscd(+)로 하지 않은 경우에는 outer join으로 인식하지 않고 inner join으로 인식하므로 유의해야함.