CODEWITHSUNDEEP
oracle-databaseperformanceplsql
pOrinG
pOrinG

Reputation: 935

Oracle Connect By vs PL SQL Loop

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.

Upvotes: 1

Views: 425

Answers (1)

APC
APC

Reputation: 146329

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.

Upvotes: 2

Related Questions