적고 쓰고 끄적이며 남기자

오라클  12c에는 컬럼을 보이지 않도록하는 invisible 기능이 있다.

ALTER TABLE 테이블명 MODIFY 컬럼명 INVISIBLE;

을 실행하면 해당컬럼은 마치 없는것처럼 테이블 DESCRIPTION에서 사라진다. 

용도는 뭘까? 

주된 용도인지는 모르겠으나 활용방법의 하나로 

테이블 컬럼 순서 조정이 가능하다. 

원래 오라클의 테이블 컬럼 순서를 조정하려면 DROP & CREATE 밖엔 방법이 없었으나 

12c 버전부터는 INVISIBLE, VISIBLE 기능을 이용하여 테이블 DROP 없이 컬럼 순서 조정을 할 수 있다.

ALTER TABLE 테이블명 MODIFY 컬럼명 INVISIBLE;

ALTER TABLE 테이블명 MODIFY 컬럼명 VISIBLE;

하면 '컬럼명' 컬럼은 맨 끝에 붙게 된다. 

중간에 위치한 컬럼도 INVISIBLE, VISIBLE 하면 맨 끝으로 컬럼 순서가 바뀐다. 

PK를 제외한 모든 컬럼을 INVISIBLE로 하고 

원하는 컬럼 순서대로 VISIBLE 하면 컬럼순서 조정은 끝.

아래 SQL을 약간 수정하여 활용하면 편리하게 INVISIBLE, VISIBLE  스크립트를 뽑을 수 있다. 

DBA권한이 없는 사용자는 DBA_TAB_COLUMNS 대신 USER_TAB_COLUMNS 로 ...

SELECT SQL

FROM (

SELECT TABLE_NAME, 1 NO, COLUMN_ID,  'ALTER TABLE '|| OWNER ||'.'|| TABLE_NAME||' MODIFY '|| COLUMN_NAME ||'  INVISIBLE ;' SQL

FROM DBA_TAB_COLUMNS

WHERE TABLE_NAME = '테이블명'

UNION ALL

SELECT TABLE_NAME, 1 NO, COLUMN_ID,  'ALTER TABLE '|| OWNER ||'.'|| TABLE_NAME||' MODIFY '|| COLUMN_NAME ||'  VISIBLE ;' SQL

FROM DBA_TAB_COLUMNS

WHERE TABLE_NAME = '테이블명'

)

WHERE TABLE_NAME  = '테이블명'

ORDER BY NO,COLUMN_ID, SQL

0.1 을 TO_CHAR(0.1) 하면 .1 로 표현됨

0.1로 하려면. 이렇게 하면 된다.

SELECT TO_CHAR(0.1,'FM9990.99') FROM DUAL 

SELECT REGEXP_SUBSTR('24-61하하8971|04-10/15|9^-^'|| '-', '(.*?)\'|| '-', 1, 2)

FROM DUAL

결과 >

61하하8971|04-

SELECT REGEXP_SUBSTR('FWE2§12§4', '[^'||'§'||']+', 1,2)

FROM DUAL 

결과 > 

12

첫번째보다 두번째가 좀 더 간단하고 쉽다. 

뭔가 다른게 있는것 같기도 한데.... 

두 번째 입력란에 '(.*?)\' || 구분자 를 입력하고 뒤에 자리수를 입력하면

원하는 번째부터 다음번까지의 문자열을 찾을 수 있다. 

REGEXP_INSTR(SL3DONET.BLD_PRE_L, '[^[:digit:]]') > 0

숫자가 아닌 값의 위치 반환

>> 문자로만 이루어진 문자열인지 확인 

WHERE NOT REGEXP_LIKE(c, '[0-9]') 
   WHERE REGEXP_LIKE(c, '^[^0-9]*$') 

>> 숫자로만 이루어진 문자열인지
WHERE NOT REGEXP_LIKE(c, '[^0-9]') 
WHERE REGEXP_LIKE(c, '^[0-9]*$') 

>> 숫자+문자 조합문자열에서 숫자만 추출하기

SELECT REGEXP_REPLACE(c, '[0-9]') 

>> 숫자+문자 조합문자열에서 문자만 추출하기

SELECT REGEXP_REPLACE(c, '[^0-9]'A)

오라클 파라미터 조회 시 

v$parameter 로는 조회되지 않는 숨겨진 파라미터들이 많다. 

SYS 계정에서 아래 SQL로 조회하면 숨겨진 오라클 파라미터를 조회할 수 있다.

SELECT KSPPINM, KSPPSTVL

FROM SYS.X$KSPPI X, X$KSPPCV Y

WHERE 1 = 1

AND X.INDX = Y.INDX

< SUM(컬럼) OVER(PARTITION BY 컬럼 ORDER BY 컬럼) >

SUM과 달리 SUM() OVER() 함수는 GROUP BY 가 필요없다. 다시말해 성능 향상을 꾀할 수 있다.

다른 OVER()를 사용하는 함수와 마찬가지로 OVER() 절이 의미하는 바는 동일하다. 

PARTITION BY 컬럼으로 구분하여 합계를 구하고 

ORDER BY 컬럼 순서로 합계를 구한다. 

>쉽게 풀이된 블로그 링크

간단하지 않은 샘플)

위와 같이 매 날짜별로 생성되어있는 데이터를 

DC_YN 열의 값이 동일한 경우 GROUP BY 하고 (파란색 부분의 연속된 값을 각각 하나의 ROW로...)

다를 경우 표시하여 아래와 같이 시작~종료일로 표시하는 SQL은 아래와 같다. 

간단하면서 알고보면 쉽지만, 아무나 생각해낼 수는 없는 풀이방법을 제공해주신 이*철 책임님께 경의를 표합니다. :D

<좋은 SQL>

SELECT PT_NO 환자번호

           , ORD_DTE 처방일

           , ORD_SEQ 처방순번

           ,MIN(DC_YN) DC_YN

           ,MAX(DC_YN) DC_YN

           ,GROUP_NUM 

     , MIN(RSV_DTIM) AS 시작일 

     , MAX(RSV_DTIM) AS 종료일 

FROM ( 

      SELECT RN 

           , PT_NO

           , ORD_DTE

           , ORD_SEQ

           , DC_YN 

           , SUM(CASE WHEN DC_YN = 'D' THEN 1 

                      WHEN DC_YN = 'C' AND 앞수 = 'D' THEN 1 

                      ELSE 0 END) OVER(ORDER BY ORD_SEQ,RN) AS GROUP_NUM 

           , RSV_DTIM 

      FROM ( 

        SELECT ROWNUM RN

             , LAG(DC_YN) OVER(ORDER BY ROWNUM) AS 앞수 

             , A.*  

        FROM 테이블 A 

          WHERE PT_NO = '12345678' 

          AND ORD_DTE = '2004-10-01' 

          AND ORD_SEQ = 4 

      ) A 

) A 

GROUP BY PT_NO

           , ORD_DTE

           , ORD_SEQ, GROUP_NUM 

ORDER BY PT_NO,ORD_DTE,ORD_SEQ,시작일

PLAN은 아래와 같다. (컬럼 60여개, 약 2600만건)

안좋은 SQL

WITH AA AS ( 

      SELECT * FROM (   SELECT ROWNUM RNUM, PT_NO, ORD_DTE, ORD_SEQ, RMCD, RSV_DTIM, DC_YN

        , LAG(DC_YN) OVER (PARTITION BY PT_NO, ORD_DTE, ORD_SEQ, RMCD ORDER BY RSV_DTIM) LAG_DC_YN 

        , LEAD(DC_YN) OVER (PARTITION BY PT_NO, ORD_DTE, ORD_SEQ, RMCD ORDER BY RSV_DTIM) LEAD_DC_YN

      FROM (SELECT * 

            FROM 테이블 

            ) A 

      ORDER BY PT_NO, ORD_DTE, ORD_SEQ, RMCD, RSV_DTIM, DC_YN)

), BB AS (

        SELECT ROWNUM RNUM2, AA.* FROM AA WHERE NVL(DC_YN,'NULL') <> NVL(LAG_DC_YN,'NULL') ORDER BY RNUM

), CC AS (

        SELECT ROWNUM RNUM2, AA.* FROM AA WHERE NVL(DC_YN,'NULL') <> NVL(LEAD_DC_YN,'NULL') ORDER BY RNUM

)

SELECT BB.*, CC.RSV_DTIM, CC.RNUM - BB.RNUM + 1 ROW_CNT 

FROM BB INNER JOIN CC 

ON BB.RNUM2 = CC.RNUM2

  AND BB.DC_YN = CC.DC_YN;

아래와 같이 PLAN 이 정말 어마무시하게 나온다. 

CONNECT_BY_ROOT

계층구조 쿼리에서 LEVEL이 0인 최상위 로우의 정보를 얻어 올 수 있다.

CONNECT_BY_ISLEAF

계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환한다. 최하이 레벨이면 1, 아니면 0

SYS_CONNECT_BY_PATH

계층구조 쿼리에서 현재 로우 까지의 PATH 정보를 쉽게 얻어 올 수 있다.

아래와 같이 Leaf Node만 전체 PATH 정보가 나오도록 작성 할 수 있다

ORDER SIBLINGS BY

계층구조 쿼리에서 상관관계를 유지하면서 정렬을 할 수 있게 해준다. 아래 예제를 가지고 ORDER SIBLINGS BY와 ORDER BY로 테스트 해보자

출처 : 구루비 http://www.gurubee.net/lecture/1903

 SELECT C.TABLE_NAME, C.COLUMN_NAME 

 FROM USER_CONS_COLUMNS C, 

         USER_CONSTRAINTS S

 WHERE C.CONSTRAINT_NAME = S.CONSTRAINT_NAME 

 AND S.CONSTRAINT_TYPE = 'P'

 AND C.TABLE_NAME = '테이블명'