필요
Oracle SQL에서 다음과 같은 테이블을 만들고 싶습니다.
COUNTRY NAME WMWHSE_ID DATE
US CRD2 1 040620
GR WAZ 2 040620
CN KOL 3 040620
FR DEL 4 040620
... ...
US CRD2 1 030620
GR WAZ 2 030620
CN KOL 3 030620
FR DEL 4 030620
... ...
WMWHSE_ID의 모든 창고는 오늘 날짜에 대한 DATE 열과 함께 인쇄되며 정확히 1 주일 전까지 DATE = 어제, 어제 전날, 그 전날 ... 등에 대해 반복됩니다. 총 124 개의 창고가 있으므로 124 * 7 = 총 868 행입니다.
===
쿼리 블록
다음은 내 질문입니다. CONNECT BY LEVEL <=을 사용하여 7 개의 날짜를 달성합니다 (이론상).
select
SUBSTR(db_alias, 1, 2) AS COUNTRY,
db_alias as NAME,
To_Number(Regexp_Replace(Db_Logid, '[^0-9]', '')) As Wmwhse_Id,
to_char(sysdate, 'yyyyMMdd')+1-level as ACTDATE
from wmsadmin.pl_db, dual where db_alias not like '%BPV' and db_alias not like 'PRDO%' and db_alias not like 'ENTERPRISE'
connect by level <=7
order by ACTDATE desc, WMWHSE_ID asc
(그것이 없으면 테이블이 다음과 같기 때문에 GROUP BY가 필요합니다.)
COUNTRY NAME WMWHSE_ID DATE
US CRD2 1 040620
GR WAZ 2 040620
CN KOL 3 040620
FR DEL 4 040620
... ...
US CRD2 1 030620
US CRD2 1 030620
US CRD2 1 030620
US CRD2 1 030620
... ...
===
문제
쿼리 시간은 CONNECT BY LEVEL <= n에서 n으로 기하 급수적으로 증가하는 것 같습니다. 몇 가지 테스트를 실행하고 다음을 얻었습니다.
CONNECT BY LEVEL <= n ROWS SECONDS
1 124 2-6
2 248 10+?
3 372 110
n = 4 이상이면 sqldeveloper가 완전히 중단되는 것 같습니다. n = 7이면 컴퓨터를 30 분 이상 실행 한 채로두고 쿼리는 계속 실행되었습니다.
이 속도 저하의 원인은 무엇입니까? 내 테이블을 구현하는 더 좋은 방법이 있습니까? 시간 내 줘서 고마워.
쿼리가 왜 그렇게 느린가요? 각 반복은 각 행을 124 개의 새 행으로 연결하기 때문입니다. 따라서 두 번째 수준에서는 124 * 124 행이고 네 번째 수준에서는 236421376 행, 7 단계에서는 450766669594624 행이됩니다. 이것이 중복되는 이유입니다. 또한 이중으로 결합해도 영향이 없습니다.
해결책은 connect by부품 을 수정하고 조건을 추가하는 것입니다 wmwhse_id = prior wmwhse_id.
select country, name, wmwhse_id, trunc(sysdate) - level + 1 dt
from pl_db
connect by level <= 7 and prior wmwhse_id = wmwhse_id and prior sys_guid() is not null
order by dt desc, wmwhse_id
대부분의 데이터베이스에서 표준 인 재귀 CTE를 사용하면 훨씬 더 쉽습니다.
with r(country, name, wmwhse_id, dt, lvl) as (
select country, name, wmwhse_id, trunc(sysdate), 1 from pl_db union all
select country, name, wmwhse_id, trunc(sysdate) - lvl, lvl + 1 from r where lvl < 7)
select country, name, wmwhse_id, dt from r;
그러나 가장 간단한 방법은 7 개의 숫자로 교차 결합을 만드는 것입니다. 생성 방법은 중요하지 않습니다.
select country, name, wmwhse_id, trunc(sysdate) - trim(column_value) + 1 dt
from pl_db cross join xmltable('1 to 7')
세 가지 쿼리를 모두 포함하는 dbfiddle .
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다