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.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
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 <> 'ENTERPRISE' CONNECT BY level <= 7 ORDER BY ACTDATE DESC, WMWHSE_ID ASC