Very slow query when using CONNECT BY LEVEL

NEEDS

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.

===

QUERY BLOCK

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 ISSUE

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.

Answer

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.