Daylogs/DB

Mapping Table 에서 and 조건으로 조회하기

발생일: 2010.02.10

문제:
업무 공유를 위해 팀원끼리 사용할 간단한 게시판을 하나 만들고 있다.

한 게시물에 여러 개의 태그를 달 수 있는 게시판이며, 태그는 < 게시물 1 : n 태그 > 형태로 구성되어 있다.
또한 gmail 의 목록처럼, 전체 목록을 뿌릴 때 엮여 있는 모든 태그를 보여준다.


업무 게시판

업무 게시판




좌측 메뉴에서 태그를 선택할 경우, 아래와 같이 해당 태그에 해당하는 게시물만 필터링하려고 한다.





그러다 매핑 테이블에서 and 조건으로 조회를 하려다가 난관에 봉착했다.
매핑 테이블에서 AND 조건을 추가하기가 쉽지 않다.

task 테이블과 tag 테이블이 존재하며 매핑 테이블은 아래와 같이 구성되어 있다.

[task_tag_map]
task_id
tag_id

데이터 샘플은 아래와 같다.
(task_id, tag_id)
(1, 2)
(1, 4)
(2, 1)
(2, 4)
(3, 1)
(3, 4)
(3, 5)

기존에는 아래와 같은 형태로 tag_id 를 받아와 조회했는데,
이 경우 OR 조건으로 선택한 태그를 포함한 모든 목록을 보여준다.

SELECT *
FROM task
WHERE
    task_id IN (
        SELECT task_id
        FROM task_tag_map
        WHERE tag_id IN (1, 4) -- 이 부분의 tag_id 를 패러미터로 가져와 넣는다
    )


이 경우, 위 데이터 샘플을 기준으로 조회하면 아래와 같은 결과가 나온다.

(task_id, tag_id)
(1, 4)
(2, 1)
(2, 4)
(3, 1)
(3, 4)

원하는 대로라면 2번과 3번 게시물만 조회되어야 하는데 말이다.

어떤 식으로 해야할까....


해결책:
한 게시물 당, 중복된 태그는 없기 때문에 count 를 조건에 추가했다.
1번과 4번 태그를 포함한 결과라면 결과가 2건 이상이어야 하기 때문이다.

아래와 같이 수정해서 문제를 해결했다.

SELECT *
FROM task
WHERE
    task_id IN (
        SELECT task_id
        FROM task_tag_map
        WHERE
            tag_id IN (1, 4) -- 이 부분의 tag_id 를 패러미터로 가져와 넣는다
        GROUP BY task_id
        HAVING COUNT(*) >= 2 -- 이 값은 tag_id 의 개수의 값으로, 유동적이다
    )


수정한 쿼리로 조회하면, 원하는 결과를 얻을 수 있다.

(task_id, tag_id)
(2, 1)
(2, 4)
(3, 1)
(3, 4)


저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG 매핑 테이블에서 태그 AND 조회, 태그 테이블 AND
Trackback 0 Comment 0
Daylogs/DB

PK 가 없는 테이블에서 중복된 데이터 제거하기

발생일: 2009.11.06

문제:
타시스템으로부터 인터페이스 받아오는 임시 테이블이 있다.
이 테이블은 데이터 전달용으로 임시로만 사용하기 때문에 따로 PK 를 두지 않았다.
헌데 이 테이블에 중복된 데이터가 인터페이스 되게 되었다.
PK 가 없는 테이블에서 중복된 데이터를 어떻게 제거해야 할까...

다른 테이블로 옮기는 과정에서 프로시저를 통해 중복데이터를 제거하면 되겠지만,
현재 상황에서는 프로시저를 수정할 수는 없다.
단순하게 DELETE 구문을 통해 중복 데이터를 제거하려고 한다.

테이블 스키마는 대략 아래와 같다고 가정한다.
IF_TEMP
id (number)
content (varchar2)

현재 데이터는 아래과 같다.
id content
 123  가나다
 123  가나다
 234  마바사
 234  마바사
 234  마바사

중복된 데이터 중 한 개만 남기고 다른 데이터를 삭제하려고 한다.


해결책:
음... id 를 기준으로 그룹별로 번호를 매겨서 번호가 1번이 아닌 데이터를 삭제하면 어떨까?
이런 식으로 말이다.

SELECT
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS num,
    id, content
FROM if_temp

(ROW_NUMBER() 의 사용법은 그룹별 번호매기기에 대해 정리해둔 포스트를 참고하자)

결과는 아래와 같다.

num id
content
 1  123  가나다
 2  123  가나다
 1  234  마바사
 2  234  마바사
 3  234  마바사


num 값이 1보다 큰 값에 대한 조건을 추가해 중복되는 나머지 데이터를 조회할 수 있었다.

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS num,
        id, content
    FROM if_temp
)
WHERE num > 2


자, 이제 삭제만 하면 된다....
어허,.. 조회를 하긴 했는데.... 삭제를 하려니 데이터에 유니크한 정보가 없어 삭제 조건을 줄 수가 없다.

DELETE FROM if_temp
WHERE... ?

뭔가 선택된 로우에 유니크한 정보가 있어야 할 것 같다.


오라클의 ROWID 를 사용하기로 했다.
ROWID 는 테이블에 있는 로우를 찾기 위해 사용되는 논리적인 정보로 유니크한 값이다.
(자세한 사항은 rowid와 rownum의 정의와 사용법 포스트 참고)

위에서 조회한 값에 rowid 를 붙여서 조회해보기로 했다.

SELECT ROWID
FROM (
    SELECT *
    FROM (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS num,
            id, content
        FROM if_temp
    )
    WHERE num > 2
)

이렇게 해주니 row 정보의 유니크한 정보를 가져올 수 있게 됐다.

삭제를 위한 최종적인 쿼리는 아래와 같다.

DELETE FROM if_temp
WHERE ROWID IN (
    SELECT ROWID
    FROM (
        SELECT *
        FROM (
            SELECT
                ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS num,
                id, content
            FROM if_temp
        )
        WHERE num > 2
    )
)

저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG PK 없는 테이블 중복 데이터 처리, rowid, ROW_NUMBER(), 중복 데이터 삭제
Trackback 0 Comment 0
Daylogs/DB

DB Link 설정, 생성, 조회, 삭제

발생일: 2009.11.01

문제:
오늘 디비 서버 이관 작업이 있었다.
헌데 기존에 디비 링크로 연결하던 테이블을 조회할 수 없다....
서버 담당자에게 문의해보니, 링크를 새로 생성해야 한다고 한다.

디비 링크.. 한 번도 안해봤는데.... -_-a

해결책:
디비 링크에 대해 아주 깔끔하게 정리해 둔 포스트가 있다.

위 포스트가 간단 요약 정리라면, 아래 포스트는 친절한 예제와 함께 설명해준다.

그리고 마지막으로, 디비 링크 조회 쿼리.
1) USER ACCOUNT
    SELECT * FROM USER_DB_LINKS

2) DBA ACCOUNT
    SELECT * FROM DBA_DB_LINKS


이렇게 하면 되겠다.


*
실질적으로 문제는 tnsnames.ora 파일에 host 를 IP 로 적어야 하는 거였다.

*
기존에 존재하던 디비 링크를 삭제하고 같은 이름으로 새로 디비 링크를 생성하였을 경우,
기존 디비 링크를 사용하던 프로시저나 뷰는 다시 컴파일 해줘야 한다.

저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG DB link, 디비 링크
Trackback 0 Comment 0
Daylogs/DB

오라클에서 CLOB 를 VARCHAR2 로 바꾸기

발생일: 2009.06.03

문제:
타 시스템의 데이터를 가져와 쓰기 위해 오라클에서 Clob를 Varchar2 로 추출해내려고 한다.

해결책:
탐이 대답해준 Converting CLOBS to VARCHAR2 컬럼을 참고하자.

오라클 내장 함수인
dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );
를 사용하면 되겠다.

참고로 varchar2 의 최대 크기는 32k이다.
실질적으로 32k 보다 큰 CLOB 데이터를 VARCHAR로 변환해 쓰려면,
다른 함수를 정의해 써야하겠다~


저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG clob, DBMS_LOB.SUBSTR, oracle, varchar2
Trackback 0 Comment 0
Daylogs/DB

인덱스를 만들었는데도 검색 시 인덱스를 타지 못하는 경우

발생일: 2009.05.26

문제:
검색하고자 하는 컬럼에 인덱스를 추가하였는데도 뚜렷한 성능 향상이 보이지 않는다.
검색 시 인덱스를 타지 못하고 있는 건 아닐까?

해결책:
데브피아의 전문가 컬럼 'INDEX 과연 달면 빠른가?'에 명확하게 설명되어 있다. (IE에서만 보인다.)

요약하자면 아래와 같이 인덱스가 달린 컬럼에 변형이 있을 경우,
인덱스가 정상적으로 적용되지 않는다.
  • 인덱스 컬럼에 변형이 일어난 경우
    • WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
    • WHERE SALARY + 1000 > 100000;
  • 내부적인 변형이 일어난 경우 (EMP_ID를 Number로 인식한다)
    • WHERE EMP_ID = 200383;
  • NULL을 비교하였을 경우
    • WHERE JOB IS NULL;
  • 부정형으로 조건을 기술한 경우
    • WHERE JOB NOT IN ( 'INSTRUCTOR','STAFF');




저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG DB 튜닝, index, tuning, 튜닝
Trackback 0 Comment 0
Daylogs/DB

여러 개의 row를 한 줄로 가져오기 (GROUP_CONCAT)

발생일: 2009.05.09

문제:
게시판 전체 목록의 태그들을 각각 가져와 한 줄로 뿌려주려고 한다.
각 게시물에 해당하는 태그 목록을 각각 조회해 가져오자니 너무 비효율적이라서,
게시물에 해당하는 태그(여러 개의 row)의 값을 공백으로 구분으로 넣으려고 한다.
데이터베이스는 mysql을 사용한다.

해결책:
mysql의 GROUP_CONCAT 함수를 사용하면 된다.

GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')


와 같이 사용한다.

mysql reference에 잘 설명되어 있다. 참고하자.



저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG group_concat, MySQL
Trackback 0 Comment 0
Daylogs/DB

mysql datetime

발생일: 2009.04.26

문제:
mysql 을 이용하는 시스템에서
게시물의 생성 시간을 추가하기 위해 컬럼을 하나 추가했다.
컬럼명: createddate   데이터 타입: date

게시물 작성 후, createddate 로 정렬하려 했는데 정상적으로 정렬되지 않는다.

해결책:
mysql 의 date 는 오라클과 달리 일자까지만 저장한다. (yyyy-mm-dd)
초단위 시간까지 포함하려 한다면 datetime 타입으로 컬럼을 생성해야 한다.

mysql 의 데이터 타입에 대해 잘 정리해놓은 블로그가 있다.

날짜와 시간 함수에 대해 정리해놓은 블로그도 참고하자.
저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG date, MySQL
Trackback 0 Comment 0
Daylogs/DB

오라클 날짜 출력

발생일: 2009.03.31

문제:
날짜 A로부터 날짜 B까지 쭉~ 뿌리고 싶다.

해결책:
LEVEL 과 CONNECT BY 구문을 사용해보자.

아래와 같이 사용하면 된다.

SELECT
    TO_CHAR(TO_DATE('2009.03.31', 'yyyy.mm.dd') + LEVEL - 1, 'yyyy.mm.dd')
FROM DUAL
CONNECT BY
    TO_DATE('2009.03.31', 'yyyy.mm.dd') + LEVEL - 1 < TO_DATE('2009.04.03', 'yyyy.mm.dd')





저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG connect by, Level, 날짜
Trackback 0 Comment 0
Daylogs/DB

SQL Formatter

발생일: 2009.03.31

문제:
SQL이 convention 에 맞지 않게 코딩되어 있어 수정하려면 매우 골치 아픈 경우,..
게다가 SQL Formatter를 지원하는 DB Tool도 라이센스 때문에 쓸 수 없을 경우,..
시스템 로그에서 쿼리가 줄바꿈 없이 쭉쭉 나오는 경우,..
...스트레스를 매우 받는다...

해결책:
더 스트레스 받기 전에 Online SQL Formatter 를 사용해보자. 강추!

마음에 들지 않는다면 sql formatter 로 구글링 해도 좋다.



저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG convention, formatter, SQL
Trackback 0 Comment 0
Daylogs/DB

오라클에서 이전 / 이후의 게시물 아이디 얻기

발생일: 2009.03.19

문제:
오라클에서 이전 / 이후의 게시물 아이디를 얻어오고자 할 때

해결책:
오라클에서 제공하는 lag / lead 함수를 이용하여 목록에서 이전 / 이후의 아이디를 가져온다.

전체 게시물을 대상으로 id, lag(id), lead(id)를 조회한 후,
해당 게시물에서 id 값을 찾아내도록 한다.

ex)
SELECT previous, next
FROM (
SELECT deptno
lag(deptno) over (order by deptno) as previous
lead(deptno) over (order by deptno) as next
FROM scott.dept)
)
WHERE deptno = #deptno#
저작자 표시 비영리 변경 금지
Posted by ohgyun
TAG db, Lag, lead, 이전, 이후
Trackback 0 Comment 0