유영락-정보시스템 감리도구를 통해 생산성 향상

SELECT
info. TABLE_NAME,
info. COLUMN_NAME,
info.udt_name as type,
case when info.character_maximum_length is null then info.numeric_precision else info.character_maximum_length end as length,
info.column_default,
info.is_nullable,
comm.column_comment as comment,
case when pri_key.column_name is null then '' else 'PK' end as PK
FROM
information_schema. COLUMNS info
LEFT JOIN (
SELECT
PS.schemaname as SCHEMA_NAME,
PS.RELNAME AS TABLE_NAME,
PA.ATTNAME AS COLUMN_NAME,
PD.DESCRIPTION AS COLUMN_COMMENT
FROM
PG_STAT_ALL_TABLES PS,
PG_DESCRIPTION PD,
PG_ATTRIBUTE PA
WHERE
PS.RELID = PD.OBJOID
AND PD.OBJSUBID <> 0
AND PD.OBJOID = PA.ATTRELID
AND PD.OBJSUBID = PA.ATTNUM
ORDER BY
PS.RELNAME,
PD.OBJSUBID
) comm ON comm.SCHEMA_NAME = info.table_schema
AND comm. TABLE_NAME = info. TABLE_NAME
AND comm. COLUMN_NAME = info. COLUMN_NAME
LEFT JOIN (
SELECT
CC.*
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC
WHERE
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
   AND TC.TABLE_CATALOG   = CC.TABLE_CATALOG
   AND TC.TABLE_SCHEMA    = CC.TABLE_SCHEMA
   AND TC.TABLE_NAME      = CC.TABLE_NAME
   AND TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
) pri_key ON pri_key.table_schema = info.table_schema
AND pri_key. table_name = info.TABLE_NAME
AND pri_key. column_name = info. COLUMN_NAME
WHERE
info.table_schema = 'public'
ORDER BY
info. TABLE_NAME,
info.ordinal_position;

출처 : https://wogus789789.tistory.com/266

오라클에서 GROUP BY 절과 SUM 함수를 사용하여 숫자를 합산할 수 있다. 그러나 문자열인 경우 SUM 함수를 사용하여 문자열을 합칠 수는 없고 대신에 LISTAGG, XMLAGG, WM_CONCAT 함수를 사용하여 그룹별 문자열 칼럼의 값을 하나의 칼럼으로 합칠 수 있다. 문자열을 합치는 함수는 오라클 버전마다 조금씩 사용법이 다르므로 아래의 예제를 참고하여 상황에 맞는 방법을 사용하면 된다.

LISTAGG 함수를 사용하여 문자열 합치기 (Oracle 11g R2 이상)

SELECT job
     , LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS enames
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 GROUP BY job

오라클 11g R2 이상에서 LISTAGG 함수를 사용하여 그룹별 문자열을 합칠 수 있다.

ORDER BY 절을 사용하여 문자열을 정렬하여 합칠 수 있다. 오라클 19C 이상 버전에서는 DISTINCT를 사용하여 문자열 중복을 제거할 수 있지만, 이전 버전에서는 DISTINCT를 사용할 수 없기 때문에 정규식 함수를 사용하여 문자열 중복을 제거한다. (포스팅 하단의 LISTAGG 함수 사용법 링크 참조)

XMLAGG 함수를 사용하여 문자열 합치기 (Oracle 9i 이상)

SELECT job
     , SUBSTR(XMLAGG(XMLELEMENT(nm, ',', ename) ORDER BY ename).EXTRACT('//text()').GETSTRINGVAL(), 2) AS enames
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 GROUP BY job

LISTAGG 함수를 사용할 수 없는 오라클 버전에서는 주로 XMLAGG 함수를 사용하여 문자열을 합친다. 구문이 조금 복잡해 보이지만 기능은 아주 잘 작동한다. ORDER BY 절을 사용하여 문자열을 정렬 후 문자열을 합칠 수 있다.

WM_CONCAT 함수를 사용하여 문자열 합치기 (Oracle 10g ~ 11g R1)

SELECT job
     , WM_CONCAT(ename) AS enames
  FROM emp
 WHERE job IN ('MANAGER', 'SALESMAN')
 GROUP BY job

WM_CONCAT 함수는 오라클 10g 버전부터 11g R1 버전까지 사용할 수 있다. 해당 버전이 아니면 오류가 발생한다. (ORA-00904: "WM_CONCAT": 부적합한 식별자)

특정 오라클 버전에서 한정적으로 사용할 수 있으므로 사용을 권장하지 않지만, 간단한 쿼리나 데이터  추출할 때 자주 사용한다. ORDER BY 절을 사용하여 합칠 문자열을 정렬할 수는 없으나 DISTINCT를 사용할 수 있기 때문에 아주 유용하다. (예, WM_CONCAT(DISTINCT ename)  AS enames )

출처: https://gent.tistory.com/550

󰊱 [오라클] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

󰊲 [MYSQL] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

󰊳 [MSSQL] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

󰊴 [CUBRID) SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행(버전 10.X 기준)

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행(버전 10.X 기준)

󰊵 [SYBASE] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

󰊶 [TIBERO] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

MS-SQL 미사용 테이블 확인 SQL

SQL Server의 SYS.dm_db_index_usage_stats 뷰의 최종접근일시 정보(last_user_scan, last_user_update 등)를 기반으로 미사용 Object를 식별

SELECT  "?" AS dbname,
        A.object_id,
        A.objectname,
        A.rowcnt,
        A.createdate,
        A.modifydate,
        B.database_id,
        B.object_id,
        B.last_user_seek,
        B.last_user_scan,
        B.last_user_lookup,
        B.last_user_update,
        B.last_system_scan
FROM    (
            SELECT 
                    A.object_id, -- 오브젝트 아이디
                    object_name(A.object_id) AS objectname, -- 오브젝트 이름
                    max(C.row_count) as rowcnt, -- 오브젝트 행 수
                    max(B.create_date) as createdate, -- 오브젝트 생성일
                    max(B.modify_date) as modifydate -- 오브젝트 마지막 수정일
            FROM    sys.indexes AS A
                    INNER JOIN sys.objects AS B ON A.object_id = B.object_id
                    INNER JOIN sys.dm_db_partition_stats AS C ON C.object_id = B.object_id
            WHERE
                    B.type IN (''U'', ''V'')
            GROUP BY
                    A.object_id
        ) AS A LEFT JOIN (
            SELECT  database_id, -- 데이터베이스 아이디
                    object_id, -- 오브젝트 아이디
                    MAX(last_user_seek) AS last_user_seek, -- 마지막 Seek 일
                    MAX(last_user_scan) AS last_user_scan, -- 마지막 Scan 일
                    MAX(last_user_lookup) AS last_user_lookup, -- 마지막 lookup 일
                    MAX(last_user_update) AS last_user_update, -- 마지막 update 일
                    MAX(last_system_scan) AS last_system_scan -- 마지막 시스템 Scan
            FROM    sys.dm_db_index_usage_stats
            WHERE   db_name(database_id) = "?" -- 해당 데이터베이스
            GROUP BY
                    database_id,
                    object_id
        ) AS B ON A.object_id = B.object_id
WHERE
        B.object_id is null
        AND "?" != ''tempdb''

오라클 데이터 패턴 검색

Z : 숫자

C : 영문대문자

c : 영문소문자

특수기호 그대로 표시

나머지 : H (한글)

SELECT  
 
    TRANSLATE(RSDN_RGST_NO, '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）' || TRANSLATE(RSDN_RGST_NO, '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）', 'ZZZZZZZZZZCCCCCCCCCCCCCCCCCCCCCCCCCCccccccccccccccccccccccccccB()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）'), 'ZZZZZZZZZZCCCCCCCCCCCCCCCCCCCCCCCCCCccccccccccccccccccccccccccB()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH'), 
    COUNT(*) AS CNT 
FROM TB_CUSTOMER10 
GROUP BY TRANSLATE(RSDN_RGST_NO, '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）' || TRANSLATE(RSDN_RGST_NO, '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）', 'ZZZZZZZZZZCCCCCCCCCCCCCCCCCCCCCCCCCCccccccccccccccccccccccccccB()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）'), 'ZZZZZZZZZZCCCCCCCCCCCCCCCCCCCCCCCCCCccccccccccccccccccccccccccB()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH'); 
;

대량의 data를 를 Exists 으로 연결시 HASH SEMI JOIN 고려
SELECT deptno, dname
FROM dept d
WHERE EXISTS (SELECT /*+ HASH_SJ*/ * FROM emp WHERE deptno = d.deptno AND comm > 500);

대량의 data를 를 NOT IN 으로 연결시 HASH ANTI JOIN 고려
SELECT empno, ename
FROM emp e
WHERE deptno NOT IN (SELECT /*+ HASH_AJ */ deptno FROM dept WHERE loc = ‘CHICAGO’)
AND deptno IS NOT NULL;