오라클에서 숫자로된 데이터를 to_number로 변경하여 where절에서 숫자랑 비교하였을때 에러가납니다. 0 1 89

by 지구촌아이 [2017.11.14 18:49:22]


오라클에서 문자로된 숫자를 형변환 하여 where절에서 숫자와 비교시 ORA-01722: 수치가 부적합합니다 

에러가 발생합니다.

데이터는 문자가 섞여있지만 한번 필터링 숫자타입 문자만 나오도록 하였고

where 절에서 to_number로 감싼후  숫자 1과 비교하였습니다.

    select
        t.*
    from(
        SELECT
            distinct user_group
        FROM T_CMS_USERINFO
        where is_number(user_group)=1 and user_group is not null
    )t
    where to_number(t.user_group)=6
    
    서비쿼리 돌렸을시 나오는 데이터

6
24
2
4
3573024
3
5

도저히 뭐가 잘못된건지 모르겠습니다. 도와주세요

by 마농 [2017.11.14 19:43:16]

1. 에러의 원인
  인라인뷰를 사용햇다고 해서 인라인뷰가 먼저 수행되지만은 않습니다.
  쿼리변환을 통해 뷰머징이 되어 인라인 뷰가 풀려서 메인과 합쳐지거나.
  뷰머징이 불가할 경우엔 메인의 조건이 서브로 침투할 것으로 예상됩니다.
  이렇게 되면 동일 레벨에서의 여러 조건 중 어느 조건이 먼저 수행될 지 모릅니다.
  즉, is_number 함수가 먼저 수행된다면 에러 안나지만
  to_number 함수가 먼저 수행된다면 에러가 나게 됩니다.
2. 실행계획의 제어
  - 뷰머징을 방지하는 힌트는 /*+ no_merge */
  - 조건 pushdown 를 방지하는 힌트는 /*+ no_push_pred */
  - 이 두가지 힌트를 이용하여 실행계획을 제어하려 테스트 해 보았으나 생각한대로 잘 안되네요.
  - /*+ no_merge(t) no_push_pred(t) */ 실패
3. ROWNUM 조건을 을 인라인뷰 안에 추가하시면 손쉽게 효과가 나타날 것입니다.
  - AND ROWNUM > 0
  - 하지만 적절한 주석을 달아 놓지 않는다면?
  - 다음 개발자가 왜 이런 쓸데 없는 조건이 달려있는지? 이해하기 어려울 것입니다.
4. 아예 조건절 수행 순서를 제어하는 힌트도 있습니다.
  - /*+ ordered_predicates */
5. 마지막으로
  - 수치 비교가 어차피 부등호(범위비교) 비교가 아닌 이퀄비교라면?
  - 굳이 to_number 을 사용하지 않는 것이 바람직합니다.
  - is_number 사용자 함수를 사용할 필요도 없고.
  - 위의 모든 고민이 한방에 해결..

-- 3. ROWNUM 조건 추가 --
SELECT *
  FROM (SELECT DISTINCT user_group
          FROM t_cms_userinfo
         WHERE user_group IS NOT NULL
           AND is_number(user_group) = 1
           AND ROWNUM >= 1
        ) t
 WHERE TO_NUMBER(user_group) = 6
;
-- 4. 조건절 순서 제어 --
SELECT /*+ ordered_predicates */
       DISTINCT user_group
  FROM t_cms_userinfo
 WHERE user_group IS NOT NULL
   AND is_number(user_group) = 1
   AND TO_NUMBER(user_group) = 6
;
-- 5. 아예 처음부터 조건을 문자로 주기 --
SELECT DISTINCT user_group
  FROM t_cms_userinfo
 WHERE user_group IS NOT NULL
   AND user_group = '6'
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입