데이터베이스 질문과 답변
안녕하세요.
TABLE1의 거래등급은 VARCHAR2 타입이고 모든 자료 상에는
'1', '2', '3', ... 'A', 'B', 'C' ... 같은 숫자변환 불가한 것도 있습니다.
하지만 COLUMN1 = 'M' 조건에서는 '1' 값 밖에 없는 상태입니다.
널 값도 없습니다.
WITH W_거래등급 AS
(SELECT DISTINCT 거래등급 FROM TABLE1 WHERE COLUMN1 = 'M')
SELECT *
FROM W_거래등급 A
WHERE
TO_NUMBER(A.거래등급) = 5
이렇게 하면 ORA-01722 에러가 납니다.
WITH W_거래등급 AS
(SELECT DISTINCT 거래등급 FROM TABLE1 WHERE COLUMN1 = 'M')
SELECT *
FROM W_거래등급 A
WHERE
TO_NUMBER(NVL(A.거래등급, 9999)) = 5
이렇게 NVL 처리 해주면 에러를 피해갑니다.
WITH W_거래등급 AS
(SELECT DISTINCT 거래등급 FROM TABLE1 WHERE COLUMN1 = 'M')
SELECT *
FROM W_거래등급 A
WHERE
TO_NUMBER(A.거래등급) = 1
'1' 밖에 없어서 그런지 위 조건도 에러를 피해갑니다.
하지만
WITH W_거래등급 AS
(SELECT '1' 거래등급, '2' D FROM DUAL)
SELECT *
FROM W_거래등급 A
WHERE
TO_NUMBER(A.거래등급) = 5
에러가 발생되야할 것 같은 듀얼로 만든 조건도 에러를 피해갑니다.
에러가 발생되는 이유가 무엇일까요?
감사합니다.
쿼리변환이 동작해서 그런 것 같습니다.
- 뷰 밖의 조건이 뷰 안으로 들어갔거나.
- 뷰 머징이 되어 원쿼리로 합쳐졌거나.
두개의 조건 중 'M' 조건이 먼저 처리되면 좋으련만,
5 조건이 먼저 수행된다면 에러가 날 수 있습니다.
두가지 방안이 있습니다.
1. WITH 문이 먼저 수행되도록 WITH 문 안에 힌트 추가
- /*+ materialize */
2. 힌트를 통해 조건절 처리 순서를 지정
- WITH 제거하여 원쿼리로 만들기
- 조건 두개 주면서 조건 처리순서 힌트 제어
- Distinct 대신 ROWNUM 사용.
3. 그런데...
굳이 숫자로 변환해서 비교할 필요가 있는지?
부등호 조건이면 몰라도 등호 조건이라면 5 대신 '5' 하면 되죠.
WITH w_거래등급 AS
(
SELECT /*+ materialize */
DISTINCT 거래등급
FROM table1
WHERE column1 = 'M'
)
SELECT *
FROM w_거래등급 a
WHERE TO_NUMBER(a.거래등급) = 5
;
SELECT /*+ ordered_predicates */
거래등급
FROM table1
WHERE column1 = 'M'
AND TO_NUMBER(거래등급) = 5
AND ROWNUM = 1
;
SELECT 거래등급
FROM table1
WHERE column1 = 'M'
AND 거래등급 = '5'
AND ROWNUM = 1
;
WITH 뷰 조건 보다
밖의 조건이 먼저 수행될 수 있는 거군요.
정말 감사드립니다.
숫자변환은 사실 사용자가 'N'이 아닌 '0N'으로 거래등급 코드를 별도로 등록할 수 있다는 가정때문에 했었습니다.
그럴 가능성이 없어서 말씀대로 캐릭터비교로
TO_NUMBER를 제거하는 방향으로 결정했습니다.
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.