유영락-정보시스템감리사

컬렉션 문서검색
▪ find()는 컬렉션 안에 저장된 문서들을 검색하는 함수
▪ <검색_조건> 인자는 필드명과 필드값의 쌍으로, 검색 대상 문서를 필터링하여, 일치하는 문서들을 반환
▪ 일부 필드 정보만 검색하고 싶다면, <검색_필드목록>에 원하는 필드 이름들을 추가로 작성
▪ 문서 하나를 검색하려면 findOne()
( find( {< 검색_ _ 조건, >}, {< 검색_ _ 필드목록} >} ) )
<검색_조건> 작성방법
{ { 필드명: : 상수값} }
<검색_필드목록> 작성방법
{ { 필드명: : 논리값, , 필드명n n: : 논리값n n } }
▪ 검색한 문서의 모든 필드 정보를 반환받을 필요가 없는 경우도 있음, <검색_필드목록> 인자는 어떤 필드값을 검색결과로 반환할지를 결정
▪ <검색_필드목록> 인자를 생략하면 검색 결과에 모든 필드 정보가 포함
▪ <검색_필드목록> 인자 안에는 검색할 모든 필드들의 값을 'true'(또는 1)로 설정하거나, 검색하지 않을 모든 필드들의 값을 'false'(또는 0)를 설정하는 두 가지만 가능
▪ 즉, '_id' 필드를 제외만 나머지 필드들은 'true'(또는 1)와 false'(또는 0)두 값 중 하나를 필드값으로 설정
▪ '_id’ 필드는 기본적으로 검색 결과에 포함되며, 'false'(또는 0)로 설정할때만 반환되는 검색 결과에서 제외

[실습]
> db.비회원.find( )
> db.비회원.find({})
> db.비회원.find({비회원이름:”이승기”})
> db.비회원.find({나이:22, 비회원이름:”이민기"})
> db.영화.find({}, {영화제목: true, 상영시간: true})
> db.영화.find({등급: “A”}, {영화제목:1 1, , 상영시간:1 1})
> db.비회원.find({}, , {_ _ id: : false, , 나이:0 0} )
> db.비회원.find( ) //모든 문서 검색
> db.비회원.find({}) //모든 문서 검색
> db.비회원.find({비회원이름:”이승기”}) //조건 검색
> db.비회원.find({나이:22, 비회원이름:”이민기"}) //조건 검색, AND
> db.영화.find({}, {영화제목: true, 상영시간: true}) //해당 필드 목록만 조회
> db.영화.find({등급: “A”}, {영화제목:1 1, , 상영시간:1 1}) //해당 필드 목록만 조회
> db.비회원.find({}, , {_ _ id: : false, , 나이:0 0} ) //해당 필드 목록만 제외하고 조회

1 검색비교연산자 : $eq, $ne, $gt, $gte, $It, $Ite
▪ 필드값 검색 조건이 범위 값일 경우에는 <검색_조건> 인자 안에 여러 <비교_연산자>를 사용, '$'접두사로 시작
▪ '$eq'(=)는 같다, '$ne'(≠)는 다르다
▪ '$gt'(>)는 크다, '$gte'(>=)는 크거나 같다
▪ '$lt'(<)는 작다, '$Ite'(<=)는 작거나 같다
▪ '$all'은 순서와 상관없이 조건에 해당하는 배열 요소를 모두 가지고 있으면 검색 결과에 포함
<검색_ 조건> 작성방법
필드명: { < <$ $ 비교_ _ 연산자1 1> >: : " " 값1 1, ", < <$ $ 비교_ _ 연산자2 2> >: : " " 값2 2, ", }
> db.영화.find()
> db.영화.find({"상영시간.시간": {$gte: 2, $lte:3}})
> db.영화.find({영화제목: {$eq: "기생충"}})
> db.영화.find({영화제목: "기생충"})
> db.영화.find({개봉날짜: {$gt: "2020-1-1"}})
➢ db.영화.find({출연배우: {$all: ["송강호", "이선균"]}})
➢ db.영화.find({출연배우: ["송강호", "이선균"]}) // 일치하지 않아서 검색 안됨
➢ db.영화.find({출연배우: ["송강호", "이선균", "조여정"]})
➢ db.영화.find({출연배우: ["송강호", "조여정", "이선균"]}) //일치하지 않아서 검색 안 됨

2 검색논리연산자 : $and, $or, $in, $nin
▪ AND 조건은 논리 연산자 없이 콤마(,)로 조건들을 나열 '$and' 논리 연산자를 사용해도 됨
▪ OR 조건은 반드시 '$or' 논리 연산자를 사용, '$or' 연산자를 이용하여 <검색 조건> 인자에 특정 필드를 두 개 이상 콤마(,)로 구분하여 나열하면, 여러 조건들 중 하나라도 만족하는 문서를 검색하여 반환
▪ '$in' 연산자는 나열된 값들 중 하나라도 일치하는 경우를 검색
▪ '$nin' 연산자는, not in의 의미이며, 나열된 값들 중 어떤 것과도 일치하지 않는 경우를 검색
> db.회원.find{ ({ $ $ and: : { [{ 나이: : 24, }, { { 회원이름: : " " 홍나리" "] }] ) })
> db.회원.find({회원이름:"홍나리", 나이:24})
> db.회원.find({나이:24, 회원이름: "홍나리"})
> db.비회원.find{ ({$ $ or: : { [{ 나이: : { {$ $ in: : [ [ 22, , 23, , 24, ]}}, { { 비회원이름: : " " 이승기" "] }]) })
> db.영화.find({ 등급: : { {$ $ nin: : ["B"]}})
> db.영화.find({ 등급: : {$ $ in: : ["A","C"]}})
> db.영화.find{ ({" " 상영시간. . 시간" ":1, " 상영시간. . 분": {$gte:30} ) })
> db.영화.find({" 상영시간. . 분": {$gte:30}, " 상영시간. . 시간":1 })
> db.영화.find({"댓글평가.댓글작성자": /^ 홍/ /, , "댓글평가.평점": {$gt: 80} })

3 검색문자열 연산자: $regex
▪ 문자열의 부분 조건으로 문서를 검색
▪ regex는 문자열의 <정규식>을 표현할 때 많이 사용되는 명령
▪ <정규식>은 특정한 규칙을 가진 문자열의 집합을 검색하기 위해, 검색 조건으로 작성하는 표현 식
<정규식> 작성방법
{ {$ $ regex: : /< 문자열_ 패턴 >/}

< < 문자열_패턴 > 예 의미
/한/ /와 /사이의 문자열('한')이 한번 이상 존재(1~n)
/^한/ ^기호 바로 뒤의 문자('한')로 문자열이 시작
/한$/ $기호 바로 앞의 문자('한')로 문자열이 끝남
[한, 국, ...] 대괄호 안에 있는 문자('한','국', ...)들이 존재하는지 검색
> db.영화.find{ ({ 영화제목: :{ {$ $ regex: : /^ 기/ /} } ) })
> db.영화.find({영화제목:/ / 량$ $/ /})
> db.영화.find({영화제목:/ / 생/ /})
> db.영화.find({영화제목:/ / 명량[ [1 1- -3 3/ ]/ })
> db.영화.find({영화제목: { {$ $ regex: : / / 명량[ [1 1- -3 3, ]/, $ $ not: : / / 명량2 2/ /} }})

4 검색결과의 정렬, 생략, 제한: sort(), skip(), limit()
> db.영화.find( ).sort({영화제목:1, 개봉날짜:-1})
> db.영화.find( ). skip(1 1)
> db.영화.find( ). limit(2 2) )
> db.영화. findOne( ( ) )

오라클 힌트(Hint) 구문

/*+ ALL_ROWS */

   Goal : Best Throughput

   용도 : 전체 RESOURCE 소비를 최소화 시키기 위한 힌트로 Cost-Based 접근방식.

            ALL_ROWS는 Full Table Scan을 선호.

            CBO(Cost Based Optimization)는 default로 ALL_ROWS를 선택.

/*+ FIRST_ROWS */

   Goal : Best Response Time

   용도 : 조건에 맞는 첫번째 row를 리턴하기 위한 Resource 소비를 최소화 시키기 위한 힌트로 Cost-Based 접근방식.

   특징 : Index Scan 이 가능하다면 Optimizer가 Full Table Scan 대신 Index Scan을 선택.

            Index Scan 이 가능하다면 Optimizer가 Sort-Merge 보다 Nested Loop 을 선택.

            Order By절에 의해 Index Scan 이 가능하다면, Sort과정을 피하기 위해 Index Scan을 선택.

            Delete/Update Block 에서는 무시되며 Group  Function, Distinct, For Update 등을 포함한 Select 문에서도 무시됨.

            Full Table Scan보다는 index scan을 선호하며 Interactive Application인 경우 best response time을 제공.

/*+ CHOOSE */

   Goal : Access되는 테이블에 통계정보에 따라 Rule-Based Optimization과 Cost-Based Optimization 중 하나를 선택.

   용도 : Data Dictionary가 해당테이블에 대해 통계정보를 가지고 있다면 Optimizer는 Cost-Based Approach를 선택

            Data Dictionary가 해당테이블에 대해 통계정보가 없으면 Rule-Based Approach를 선택.

            Hint Level의 CHOOSE는 RBO(Rule Based Optimization)인지 CBO(Cost Based Optimization) 인지를 선택.

/*+ CLUSTER(table_name) */

   Cluster Scan을 선택하도록 지정하며 clustered object에만 적용.

/*+ FULL(table_name) */

   Table을 Full Scan하길 원할 때 사용.

/*+ HASH(table) */

   Hash scan을 선택하도록 지정.

   HASHKEYS parameter를 가지고 만들어진 cluster내에 저장된 table에만 적용.

/*+ HASH_AJ */

   NOT IN SubQuery 를 HASH Anti-join으로 변형

/*+ HASH_SJ */

   Correlated Exists Subquery 를 Hash Semi-join으로 변형

/*+ INDEX(table_name index_name) */

   지정된 index를 강제적으로 쓰게끔 지정.

/*+ INDEX_COMBINE(table_name index_name) */

   Index명이 주어지지 않으면 Optimizer는 해당 테이블의 Best Cost 로 선택된 Boolean Combination Index 를 사용.

   Index 명이 주어지면 주어진 특정 Bitmap Index 의 Boolean Combination 의 사용을 시도.

/*+ INDEX_ASC(table_name index_name) */

   지정된 index를 오름차순으로 쓰게끔 지정.

   Default로 Index Scan은 오름차순

/*+ INDEX_DESC(table_name index_name) */

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);