I want to create a table like the following in 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 ... ...
Every warehouse in WMWHSE_ID will be printed with a DATE column for today’s date, and this will be repeated for DATE = yesterday, the day before yesterday, the day before that…etc until exactly 1 week ago. I have 124 warehouses total, so 124*7 = 868 rows total.
The following is my query. It uses the CONNECT BY LEVEL <= to achieve the 7 dates (in theory)
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
(The GROUP BY is needed, because without it the table looks like:)
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 ... ...
The query time seems to grow exponentially with n in CONNECT BY LEVEL <= n. I ran some testing and got the following:
CONNECT BY LEVEL <= n ROWS SECONDS 1 124 2-6 2 248 10+? 3 372 110
Anything n=4 and up seems to just hang sqldeveloper completely. When n=7, I left the computer running for 30+ minutes and the query was still running.
What is causing this slowness? Is there a better way to go about implementing my table? Thanks for your time.
Why is query so slow? Because each iteration connects each row with 124 new rows. So at second level it is 124 * 124 rows, at fourth level you get 236421376 rows, at seventh level 450766669594624 rows. This is why you get duplicates. Also join with dual has no impact.
The solution is to correct
connect by part, add condition
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
It is even easier with recursive CTE, which is standard in most databases:
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;
But simplest is to make cross join with 7 numbers, doesn’t matter how you generate them:
select country, name, wmwhse_id, trunc(sysdate) - trim(column_value) + 1 dt from pl_db cross join xmltable('1 to 7')
dbfiddle containing all three queries.