ㅠ 돠주세요 ㅠ 0 4 2,439

by 이재현 [2009.12.17 17:05:42]


제가 만든건 속도가 늦어서리..

대가리가 돌이라.. 좀 도와주세요 ㅠ 또 급해서리 ㅠ

WITH T AS (
 SELECT '222.231.42.50' AS A FROM DUAL
 UNION ALL SELECT '113.30.100.58' AS A FROM DUAL
 UNION ALL SELECT '4.68.110.26' AS A FROM DUAL
 UNION ALL SELECT '113.30.100.34' AS A FROM DUAL
)
SELECT * FROM T

/***********************************
원하는 데이타

222231042050
113030100058
4068110026
113030100034
                                 
***********************************/

 

 

by finecomp [2009.12.17 17:35:22]
대강 물으셔서 정확한 답변이 어렵군요...저도 개념들만 대강 쫙~ 쓰겠습니다...;
REGEXP이나 INSTR과 SUBSTR의 조합으로 one-SQL로 처리하신 거라면 그 외엔 성능개선방법은 크게는 없을 듯 합니다만...;

작게는 수행세션의 db_file_multiblock_read_count 파라미터의 조정이나 parallel 처리 등이 있을 수는 있겠죠...;

by 마농 [2009.12.17 17:47:39]
WITH t AS
(
SELECT '222.231.42.50' a FROM dual
UNION ALL SELECT '113.30.100.58' FROM dual
UNION ALL SELECT '4.68.110.26' FROM dual
UNION ALL SELECT '113.30.100.34' FROM dual
)
SELECT a
, LPAD(REGEXP_SUBSTR(a,'([^.]+)',1,1),3,'0') || '.'
|| LPAD(REGEXP_SUBSTR(a,'([^.]+)',1,2),3,'0') || '.'
|| LPAD(REGEXP_SUBSTR(a,'([^.]+)',1,3),3,'0') || '.'
|| LPAD(REGEXP_SUBSTR(a,'([^.]+)',1,4),3,'0') b
FROM t
;

by 마농 [2009.12.17 23:44:58]
WITH t AS
(
SELECT '222.231.42.50' a FROM dual
UNION ALL SELECT '113.30.100.58' FROM dual
UNION ALL SELECT '4.68.110.26' FROM dual
UNION ALL SELECT '113.30.100.34' FROM dual
)
SELECT a
, RTRIM(REGEXP_REPLACE(REPLACE('00'||a,'.','.00')||'.','([^.]{3}\.)|.','\1'),'.') b
FROM t
;

by 빈이 [2009.12.18 13:42:16]
WITH T AS (
SELECT '222.231.42.50' AS A FROM DUAL
UNION ALL SELECT '113.30.100.58' AS A FROM DUAL
UNION ALL SELECT '4.68.110.26' AS A FROM DUAL
UNION ALL SELECT '113.30.100.34' AS A FROM DUAL
)

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