| write to the file from PL SQL [message #676153] |
Fri, 17 May 2019 11:02  |
ykozhevnikov
Messages: 59
Registered: November 2008
Location: USA
|
Member |
|
|
Hello , I have very short question.
there is procedure that takes long time to run. This procedure makes multiple inserts and commits during run.
Does it make any sense instead of inserting into table to open file from inside procedure and write to the file?
should it speed up execution ?
Thanks
[Updated on: Fri, 17 May 2019 11:04] Report message to a moderator |
|
|
|
|
|
| Re: write to the file from PL SQL [message #676157 is a reply to message #676153] |
Fri, 17 May 2019 11:50  |
John Watson
Messages: 8977
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Are you asking if it is possible to bypass the SQL interface and instead write directly to a disc based structure? If so, the answer is categorically NO. Ted Codd said, back in 1970, that a relational database cannot permit that.
|
|
|
|
| Re: write to the file from PL SQL [message #676159 is a reply to message #676157] |
Fri, 17 May 2019 14:00 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Using the SYS.UTL_FILE package you can write flat files to the database server instead to a table, however it will not be faster then writing into an oracle table. Some questions.
Is this a mass load with no other activity during the load. How many rows are going in and how many rows in the table that is being inserted. Pure sql insert will always be faster then a procedure that is looping through a cursor.
How many indexes and triggers are on the table(s) being inserted?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
