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 40769 게시물 읽기
No. 40769
sql 또 하나 봐주세요. ^^
작성자
이현정
작성일
2015-04-10 08:19
조회수
7,361

PROD NO QTY
PPU 1111 4
PPU 5555 1
PPU 7777 3

PROD NO QTY RANK
PPU 1111 4 1
PPU 1111 4 2
PPU 1111 4 3
PPU 1111 4 4
PPU 5555 1 1
PPU 7777 3 1
PPU 7777 3 2
PPU 7777 3 3

이렇게 만들려고 하면 어떻게 해야 하나요?

qty가 4이면 1,2,3,4로 만들어 내는 sql 입니다.

이 글에 대한 댓글이 총 2건 있습니다.

WITH t AS
(
SELECT 'PPU' prod, 1111 no, 4 qty FROM dual
UNION ALL SELECT 'PPU', 5555, 1 FROM dual
UNION ALL SELECT 'PPU', 7777, 3 FROM dual
)
SELECT prod, no, qty, lv
  FROM t
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 99)
 WHERE lv <= qty
 ORDER BY prod, no, lv
;

마농(manon94)님이 2015-04-10 08:36에 작성한 댓글입니다.

 qty의 범위가 어떻게 되나요?

 

예를 들어 qty의 범위가 1~10 정도면 별 문제가 없을 것입니다.

또한 qty의 범위가 10000 ~ 15000 정도라면

역시 너무 많은 행이 생성되기 때문에 성능을 높일 방법이 어차피 없을 것입니다.(원본 테이블 데이타가 많다면..)

 

그런데

만약 qty가 대부분 1~10 정도 이지만 예외적으로 1000이 되는 경우도 있다면

위의 sql은 level < 1001 이상이 되어야 하는데요...

그러면 성능에 문제가 생길 수도 있습니다.

 

물론 대부분의 경우 위의 마농님 말씀대로 하시는 것이 상대적으로 가장 성능이 좋습니다.

그런데 질문자님의 질문이 앞의 연속된 질문의 연장선 상에 있다면 order 테이블이 500000건 정도이고

원본 쿼리의 시도가 level < 10000 으로 되어 있는 것으로 보아 qty 데이타가 취할 수 있는 범위에 대하여 고려를 하셔야 할 것 같습니다.

 

 

제가 알기 쉽게 아래에 예제를 첨부하였습니다.

 

먼저 다음과 같이 테이블을 만듭니다.

create table t_temp
as
select
    level id
    , round(dbms_random.VALUE(1,5),0) qty
from    dual
connect by
    level <= 10000
/
 
그러면 테이블에는 1 ~ 5의 범위를 갖는 qty가 생성됩니다.(10000 행)
 
 
그리고 다음과 같이 level <= 5로 수행합니다.
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
select
    a.id
    ,a.qty
    ,b.lvl rank
from    t_temp a
        , (
            select
                level lvl
            from    dual
            connect by
                level <= 5
        ) b
where   b.lvl <= a.QTY
/
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
2015-04-10 15:07:33 842000
2015-04-10 15:07:35 078000
 
수행 시간이 2초가 안걸립니다.
 
 
다음은  level <= 1000로 실행합니다.
 
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
select
    a.id
    ,a.qty
    ,b.lvl rank
from    t_temp a
        , (
            select
                level lvl
            from    dual
            connect by
                level <= 1000
        ) b
where   b.lvl <= a.QTY
/
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
2015-04-10 15:08:00 577000
2015-04-10 15:08:03 778000
 
대략 3초가량 걸립니다.
 
그 다음은 아예 level <= 100000 으로 실행합니다.
 
 
 
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
select
    a.id
    ,a.qty
    ,b.lvl rank
from    t_temp a
        , (
            select
                level lvl
            from    dual
            connect by
                level <= 100000
        ) b
where   b.lvl <= a.QTY
/
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
2015-04-10 15:09:49 484000
2015-04-10 15:13:36 775000
 
수행 시간이 4분 가까이 걸립니다.
 
 
다음은 max(qty)를 먼저 구합니다.
 
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
select
    a.id
    ,a.qty
    ,b.lvl rank
from    t_temp a
        , (
            select
                level lvl
            from    (
                select
                    max(qty) max_qty
                from    t_temp s
                    )
            connect by
                level <= max_qty
        ) b
where   b.lvl <= a.QTY
/
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
 
2015-04-10 15:14:39 324000
2015-04-10 15:14:40 782000
 
대략 2초가 안걸리지만 동일 대상에 두번 select 하는 것이 좀 꺼림직합니다.
만약 원본 테이블에 접근 속도가 현저히 느리다면 그리 좋은 방법이 아닐 수 있습니다.
 
 
다음은 collect 를 활용한 것입니다.
 
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
select
    a.id
    ,a.qty
    ,b.column_value rank
from t_temp a
    ,TABLE(select collect(level) from dual connect by level<= a.qty) b
/
select
    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ff')
from    dual
/
 
 
2015-04-10 15:18:14 463000
2015-04-10 15:18:17 019000
 
2초 좀 넘게 걸립니다.
 
collect 는 트레이스를 떠보면 내부적으로 pl-sql 호출을 사용하여 절차적으로 수행됩니다.
그러므로 느려질 수 있지만
필요한 만큼만 대가를 지불하는 장점이 있습니다.
 
 
대략 정리하면
1) 만약 qty의 범위가 분산이 작고 익히 알려진 값이면 level < :cnt 스타일을 사용
2) 그렇지 않다면 collect 와 max(qty) 중 테스트 해보고 결정
 
정도가 되지 않을까 싶습니다.
^^
 
김흥수(protokhs)님이 2015-04-10 10:52에 작성한 댓글입니다.
이 댓글은 2015-04-10 15:25에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
40772쿼리 질문입니다. [3]
은아수
2015-04-16
6685
40771c++에서 proc로 오라클 프로시저를 호출할 경우
mryan
2015-04-15
7772
40770sqlldr로 일부 칼럼만 선택 업로드, TEXT 파일 오라클 연결하기 문의 드립니다. [3]
쿼리OTL
2015-04-14
7508
40769sql 또 하나 봐주세요. ^^ [2]
이현정
2015-04-10
7361
40768또 하나 질문 드려요. [1]
이현정
2015-04-09
6456
40767sql 좀 봐주세요. [5]
이현정
2015-04-08
7455
40766oracle sql 로 각 분단위로 지정시간동안의 누적 합계를 구하려고 하는데 쿼리가 안짜지네요 [5]
버들고양이
2015-04-08
6840
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다