Oracle Connect By vs PL SQL Loop

Question

I have a procedure in which we are to insert the last 365 days as date starting from X into a table.

Currently the procedure is using LOOP [365 iterations] and inserting the data in 365 DML statements.

From my understanding using connect by loop and inserting all at once would be a better option in terms of performance.

Please find my query below where X = 30-SEP-2017:

insert into Temp_Table_X as
select (To_Date('30-SEP-2017','DD-MON-RRRR')+1) - rownum 
from dual
connect by rownum <= 365

Please advise.

In each itterationdoes table changes ? first index into temp_table_30-SEP-2017, 2nd iteration into 31-SEP-2017 and so on? or all dml happens in 1 table — user2102266
– user2102266, Commented Jul 1, 2017 at 8:27

APC · Accepted Answer · 2017-07-01 09:01:16Z

2

A SQL statement is faster than a PL/SQL loop. But for 365 rows the difference is hardly worth worrying about. Unless you run this statement a lot, in which case definitely use SQL.

Incidentally, you've hard-coded 365. What about leap years? Maybe this variation would be better?

insert into Temp_Table_X    
with x as ( select date '2017-01-31' as st from dual )
select x.st - (level -1)
from x
connect by x.st - level >= add_months(x.st, -12)
/

"why use "with x as" instead of using the sub-query in the from clause?"

No reason. They're equivalent, so use whichever one fits your style.

edited Jul 1, 2017 at 9:01

answered Jul 1, 2017 at 8:30

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

pOrinG Over a year ago

Thanks for the input. However your query gives me 31-Jan-2016 also which is a issue for me. Another question I have is why use "with x as" instead of using the sub-query in the from clause? [from (select .. from dual) x]

pOrinG Over a year ago

We actually use this loop a lot and in multiple places. I will definitely change it to a single SQL. The requirement is last 365 days and your concern is certainly well placed but the leap year (extra 1 day) won't affect us much.

APC Over a year ago

why is 2016-01-31 an issue? It's just the day after the start date.

pOrinG Over a year ago

We use this query to display available business dates [in the system]. When the system is on 30-Jan-2016 the business date 31-Jan-2016 is not available. Hence it would be both misleading & incorrect if it comes up in the query.

APC Over a year ago

I think the confusion is that my solution rolled forward from the date one year ago. I have tweaked my answer to reflect your original logic of rolling back from the current business date. Happier now?

|

Collectives™ on Stack Overflow

Oracle Connect By vs PL SQL Loop

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related