Database/SQL2009. 1. 22. 10:05

인덱스를 안타요... ㅠ.ㅠ

이런경험 있지 않던가?
분명 DB테이블에 Index 를 설정해놓은 컬럼인데...
어느순간 보니.. Index를 타지 않고..  테이블을 Full Scan 해버리는 상황...
뭐가 문제야 뭐가!!!  하며 hint를 줘보고, 별짓을 다해보는뎅....
쿼리 실행하면 4~5초는 기본이요~ 10~20초 까지 가게되다도면.. 뭐가 문제인지..
답답할때가 많다.

그런데.. 대부분의 경우 Index 설정이 되어있다면...  안탈리가없다...
문제는 바로 조건문쪽에 있을 확률이 높다.

조건에서 혹시 인덱스 설정한 값을 함수를 통해 변환하지는 않았는가?

내가 겪은 상황의 대부분은 이러했다..
특수한 경우를 제외하고는 인덱스로 설정한 값을 SELECT 조건문에서 변형을 하게되면
해당되는 Index를 타지 못하고 Full Scan 하게되는거다..

예를 들어 날짜 형식으로 지정된 Index가있는데.. 내가 비교하기 편하려고
지정된 컬럼을 TO_CHAR() 함수로 바꾸어서 값을 비교하게되면..
Index의 지정은 date형식인데.. 비교가 char 형식으로 바뀌게되면서
Full Scan을 하게 된다.

인덱스를 타지 않는경우!!
여러분들의 조건문을 한번 다시 살펴보길 바란다...
어딘가 변형을 가하지 않았는가??
Posted by 햇 님
Database/SQL2009. 1. 22. 09:57

ROUND, TRUNC 함수를 날짜에 사용할때..

머 어제 오늘 삽질이 아니다보니...
자주 글을 올리게 된다.

이번에 문제가 된건 바로 ROUND 함수다.
특히나 저장된 시간 혹은 현재시간(SYSDATE) 값을 ROUND 함수를 사용하는경우에
문제점이 되었다.

테스트를 해보자면.. 뭐 오전/오후로 나눠서 해보면 알수있는지라...
아니면 테스트로 데이터들중에 오전/오후로 나뉘어진 데이터를 SELECT 해보자

SELECT ROUND(SYSDATE) FROM DUAL

오늘이 22일 오전이라면

2009-01-22

위와 같은 결과값이 나올것이다..  오후가 된다면

2009-01-23

위와 같은 결과값이 나올것이라고 예상해본다...  -_-ㅋ    아마도 맞을것이다..

내가 원하던값은 22일을 원했다...
그런데 23일 나온건 ROUND 라는 함수가 반올림을 해주는 함수인데.. 즉  24시간기준으로...
12시간이 지나면 반올림되어 22일에서 23일로 바뀌게 되는것이었다.

물론 함수를 잘 꿰뚫고 계신분들은 멀 저런걸로 해매냐!   할지도 모르지만..
의외로 ROUND 함수를 많이 사용하고, 보이는 SQL에 ROUND가 많다보면 자신도 모르게.. 막 쓰게된다..
Ctrl + C , V 가 판치지 않던가??

대안은 간단하게도 TRUNC 함수로 쓰면.. 시간에 해당되는 부분을 버려주고 원하는 날짜만 바로바로 가져올수 있다.

SELECT TRUNC(SYSDATE) FROM DUAL

오전/오후 상관없이 원하는 날짜를 가져올수있당!! ㅎㅎ
쩝... 클날뻔했네..  팀장님이 확인안해주셨음...  -_-;;    아직 멀고도 험한 나의 개발인생...

Posted by 햇 님
Database/SQL2008. 12. 22. 14:05

ASP 사용시 SQL 사용시 위험성 :: 해킹

몇일전 아는 형님 한분이 저에게 고민을 털어놨습니다.
다름아닌 형님이 운영중이시던 사이트가
ASP로 되어있는데..

어떤 해커(뭐 그닥 실력이 뛰어나진 않을듯;)가 계속해서 SQL을 이용해서
해킹을 시도한다는것이었습니다.

그 방법이란것이 참으로 고전적인 수법인
HTML 에 text 박스 (로그인창이라던가 검색창을 통해..) 에 SQL문을 직접적어서
해킹을 시도하는거죠.. (- sql injection 이라고 하죠 ^^:)

이 문제는 예전부터 ASP 해킹에 가장 쉬운방법으로
많은 이슈와.. 문제점 해결방안에 대해 이야기가 나왔죠..
뭐 솔직히 좀 짜증나는 겁니다.. 특히나 라이브된 사이트의 경우는
SQL 관련된 내용을 통채로 바꺼줄라면 유지/보수하는 개발자는 죽어라
코딩해야할겁니다.. (아래를 보시면 알겠지만.. 손이 많이가는 작업임)

저도 한떄 ASP 로 개발을 하던 시절이 있었던지라..
이런부분을 모르고 있다가 회사내의 선배로부터 배울수 있었고..
SP..  (Stored Procedure) 라는걸 배우게 되었습니다.

이에 대한 자세한 설명은
http://sqler.pe.kr/sql2k/711.asp
에 가보시면 잘 나와있습니다.  (제가 예전에 유용하게 보던 사이트입니다. ^^)

뭐 오라클에도 SP 가 있지만..
다른곳은 모르지만 저희 회사에선 그닥 비추하는거 같더군요.
일단은 ASP 가 아닌 JSP 를 사용하는데.. ASP에서의 SQL 쿼리를 붙이는 형식이 (Statement 객체활용)
아닌 JSP에서는 주로 PrepareStatement 객체를 사용해 request 로 넘겨받은 값들을
set 하는 방식으로 사용하기 때문에 ASP 와 같은 문제를 일으킬 염려가 없다고 하더군요.

뭐 이래저래 말이 많았지만.. ASP 는 솔직히 현재 거의 뭍혀진 언어가 되가는거 같습니다.
(저 ASP 공부할때는 언어라고도 쳐주지도 않았습니다. - 그당시 웹은 천대받았죠..)

새롭게 리뉴얼해서 바꾸려고하면 회사내에서도 상당한 money가 투자되야하니..
기존의 asp로 개발된 웹APP를 쉽게 버릴순없겠지만.. 고객정보라던가.. 중요정보가
노출될 염려가 큰 회사라면.. money를 들여서라도 바꺼야 겠죠...


Posted by 햇 님
Database/Oracle2008. 5. 29. 15:27

ORACLE 에서의 TRUNC() 함수

오늘 소스분석을 하면서 TRUNC() 함수를 보게 되었다.

truncate 의 줄임말인듯하다.
뜻은 절삭? 이라고 해야하나... 사전의 뒤져보는 치밀함까지는 나에게 없다;
대충 짐작하기에 절삭.. 혹은 내림? 정도로 생각하려고 한다.

SELECT SYSDATE FROM DUAL;

위 문장을 실행해보면 현재 날짜를 출력하여준다.

사용자 삽입 이미지


SYSDATE 는 DATE 형식으로 날짜를 출력해주는데..

TRUNC() 함수는 날짜 형식 또한 잘라낼수 있다.
내가 아는 바에 의하면 숫자와 날짜에 한해서만 동작하는것으로 알고 있다.

그럼 TRUNC() 함수를 사용해서 현재날짜를 절삭해보면..

SELECT TRUNC(SYSDATE) FROM DUAL;

위의 문장을 실행하면 된다.
결과값은

사용자 삽입 이미지

자동으로 오늘 날짜에서 시간이 떨어져 나가고 년-월-일 의 값으로 나온다.
조금더 명시적으로 표현해주자면 아래와 같이 써주어도 된다.

SELECT TRUNC(SYSDATE , 'DD') FROM DUAL;

위의 SQL 명령도 같은 결과값을 가진다.


그럼 이어서 여기에 날짜를 더하거나 빼거나 하고.. 절삭한후에 시간을 지정해주는 부분을 보자
조금 더 살펴봐야 알겠지만.. 너무나 쉽게도.. 그냥 더하고 빼면 된다 -_-;

SELECT TRUNC(SYSDATE+1) FROM DUAL;

위의 문장을 실행하게되면..

사용자 삽입 이미지

위의 결과값인 2008-05-29 에서 하루가 추가되는것을 볼 수 있습니다.
같은 방식으로 7을 더하면 일주일 후의 날짜가 나오겠죠?


SELECT TRUNC(SYSDATE+1)+1/24 FROM DUAL;

이번에 SQL 문을 실행하면 절삭하였던 시간을 다시 붙여줄수 있습니다.

사용자 삽입 이미지
기본적이 구문을 시작으로 간단한 응용까지 해봤습니다.
좀더 응용해보면 시간아래에  분/초 도 달수 있겠죠?

Posted by 햇 님
Database/Oracle2008. 5. 14. 10:35

오라클 함수 DECODE

회사에서 소스를 파악하다가 쿼리문중에 decode 라는 함수를 보았다.
언젠가 본적이 있는거 같은데 생각이 안나서 열심히 뒤져보고 찾아보았다.

- 구문
DECODE({column | expression}, search1, result1 [,search2,result2] ... [,default] )

이와 같이 사용하면 된다.

설명을 하자면 일단은 맨처음에 decode 하고자하는 column을 적고 그 뒤에는
coulumn에 존재하는 찾을 값들을 적고, 그 뒤에 새롭게 쓰고자하는 값을 적어넣으면 된다.

emp 테이블로 간단하게 예를 들자면

select ename, job,
    decode(job, 'ANALYST', '분석가',
                'CLERK', '점원',
                'MANAGER', '관리자',
                'SALESMAN', '영업사원',
                'PRESIDENT', '사장님') as new_job
from emp;

위의 쿼리문을 실해하면 job 의 값에 따라서 new_job 컬럼에 대응되는 값들이
나타나게 된다.

참고글
 - http://luckys.tistory.com/35

원문
 - http://blog.naver.com/bleu_ciel/140037398850

Posted by 햇 님

티스토리툴바