database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 38685 게시물 읽기
No. 38685
그룹핑한 테이블과 타 테이블 조인시 row 갯수 제한이 있나요 ????
작성자
HELP
작성일
2011-07-12 11:07
조회수
4,551

현재 oracle 8i로 작업중에 있습니다.

통계 페이지 추출을 위해 group 처리 하고 실제 테이블과 조인 하는데 이게 갯수가 많아 지면 에러가 발생합니다.

-- tmp 갯수가 많아서 ORA-24344 에러 발생

WITH tmp AS(
select 123 ms_id FROM dual UNION ALL
select 129 ms_id FROM dual UNION ALL
select 100 ms_id FROM dual UNION ALL
select 121 ms_id FROM dual UNION ALL
select 128 ms_id FROM dual UNION ALL
select 120 ms_id FROM dual UNION ALL
select 132 ms_id FROM dual UNION ALL
select 108 ms_id FROM dual UNION ALL
select 102 ms_id FROM dual UNION ALL
select 119 ms_id FROM dual UNION ALL
select 117 ms_id FROM dual UNION ALL
select 125 ms_id FROM dual UNION ALL
select 101 ms_id FROM dual UNION ALL
select 107 ms_id FROM dual UNION ALL
select 130 ms_id FROM dual UNION ALL
select 131 ms_id FROM dual UNION ALL
select 104 ms_id FROM dual UNION ALL
select 105 ms_id FROM dual UNION ALL
select 109 ms_id FROM dual UNION ALL
select 124 ms_id FROM dual UNION ALL
select 122 ms_id FROM dual UNION ALL
select 106 ms_id FROM dual UNION ALL
select 118 ms_id FROM dual UNION ALL
select 111 ms_id FROM dual UNION ALL
select 103 ms_id FROM dual UNION ALL
select 127 ms_id FROM dual UNION ALL
select 1 ms_id FROM dual UNION ALL
select 2 ms_id FROM dual UNION ALL
select 3 ms_id FROM dual UNION ALL
select 4 ms_id FROM dual UNION ALL
select 5 ms_id FROM dual UNION ALL
select 6 ms_id FROM dual UNION ALL
select 7 ms_id FROM dual UNION ALL
select 126 ms_id FROM dual
), tmp2 AS(
select 121 sectors_main_id, 1 emp_id, 10000 base_salary from dual union all
select 109 sectors_main_id, 2 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 3 emp_id, 10000 base_salary from dual union all
select 109 sectors_main_id, 4 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 5 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 6 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 7 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 8 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 9 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 10 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 11 emp_id, 10000 base_salary from dual
)
SELECT
ms_id, COUNT(*) cnt
,NVL(ROUND(percentile_cont(0.25) within group (order by base_salary),0),0) bs_q1
,NVL(ROUND(percentile_cont(0.5) within group (order by base_salary),0),0) bs_median
,NVL(ROUND(percentile_cont(0.5) within group (order by base_salary),0),0) bs_median
FROM tmp2 e, tmp s
where s.ms_id = e.SECTORS_MAIN_ID(+)
GROUP BY ms_id

-- tmp row 수가 적어서 정상

WITH tmp AS(
select 123 ms_id FROM dual UNION ALL
select 129 ms_id FROM dual UNION ALL
select 100 ms_id FROM dual UNION ALL
select 121 ms_id FROM dual UNION ALL
select 128 ms_id FROM dual UNION ALL
select 120 ms_id FROM dual UNION ALL
select 132 ms_id FROM dual UNION ALL
select 108 ms_id FROM dual UNION ALL
select 126 ms_id FROM dual
), tmp2 AS(
select 121 sectors_main_id, 1 emp_id, 10000 base_salary from dual union all
select 109 sectors_main_id, 2 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 3 emp_id, 10000 base_salary from dual union all
select 109 sectors_main_id, 4 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 5 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 6 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 7 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 8 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 9 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 10 emp_id, 10000 base_salary from dual union all
select 121 sectors_main_id, 11 emp_id, 10000 base_salary from dual
)
SELECT
ms_id, COUNT(*) cnt
,NVL(ROUND(percentile_cont(0.25) within group (order by base_salary),0),0) bs_q1
,NVL(ROUND(percentile_cont(0.5) within group (order by base_salary),0),0) bs_median
,NVL(ROUND(percentile_cont(0.5) within group (order by base_salary),0),0) bs_median
FROM tmp2 e, tmp s
where s.ms_id = e.SECTORS_MAIN_ID(+)
GROUP BY ms_id

 

통계 리스트를 뿌려줘야 하는데 저렇게 나버리니 답이 없네요;;;

로우 불러와서 일일히 처리를 해야 할런지....미치겠네요...ㅋ

처리 방법이 없을 까요 ???
이 글에 대한 댓글이 총 1건 있습니다.

데이터 건수에 따라 에러가 난다면 버그로 봐야 할것 같구요.
버전에 따른 버그라면? 패치나 버전업이 근본 해결책이겠지요.
그게 안된다면 오류의 원인을 파악하고 피해가는 방법을 찾아야죠.
percentile_cont 부분이 원인이라면 해당 함수를 따로 구현해보시면 어떨까요?
percentile_cont 를 풀어서 구현해 봤습니다.


SELECT ms_id
     , ROUND(CASE WHEN FLOOR(cnt * 0.25 + 1) = CEIL(cnt * 0.25 + 1)
                  THEN MIN(DECODE(rn, cnt * 0.25 + 1, base_salary))
                  ELSE (CEIL(cnt * 0.25 + 1) - (cnt * 0.25 + 1))
                     * MIN(DECODE(rn, FLOOR(cnt * 0.25 + 1), base_salary))
                     + ((cnt * 0.25 + 1) - FLOOR(cnt * 0.25 + 1))
                     * MIN(DECODE(rn,  CEIL(cnt * 0.25 + 1), base_salary))
              END ) bs_25
     , ROUND(CASE WHEN FLOOR(cnt * 0.50 + 1) = CEIL(cnt * 0.50 + 1)
                  THEN MIN(DECODE(rn, cnt * 0.50 + 1, base_salary))
                  ELSE (CEIL(cnt * 0.50 + 1) - (cnt * 0.50 + 1))
                     * MIN(DECODE(rn, FLOOR(cnt * 0.50 + 1), base_salary))
                     + ((cnt * 0.50 + 1) - FLOOR(cnt * 0.50 + 1))
                     * MIN(DECODE(rn,  CEIL(cnt * 0.50 + 1), base_salary))
              END ) bs_50
  FROM (SELECT ms_id
             , base_salary
             , COUNT(*) OVER(PARTITION BY ms_id) - 1 cnt
             , ROW_NUMBER() OVER(PARTITION BY ms_id ORDER BY base_salary) rn
          FROM tmp2 e
             , tmp  s
         WHERE s.ms_id = e.sectors_main_id(+)
        )
 GROUP BY ms_id, cnt
;


참고 사이트
http://www.statwith.pe.kr/ORACLE/functions100.htm

마농(manon94)님이 2011-07-12 18:21에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
38688오라클에서 RTRIM에 대한 문의 [1]
gemini2k
2011-07-12
3920
38687오라클 쿼리 및 ORA-29275질문 입니다.
오라
2011-07-12
4507
38686쿼리 문의 [2]
레이첼
2011-07-12
3300
38685그룹핑한 테이블과 타 테이블 조인시 row 갯수 제한이 있나요 ???? [1]
HELP
2011-07-12
4551
38684ORA00704, ORA39700에러에 이어서 ORA-00942에러가 납니다.. [1]
송인혁
2011-07-12
3887
38683오라클 10 SGA 조정방법에 관해서
질문
2011-07-11
3246
38682chmod 775로 하면 안되는게 정상인가요? [2]
임서희
2011-07-11
3482
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.021초, 이곳 서비스는 PostgreSQL v16.4로 자료를 관리합니다