My question is related to usage of sequences in insert queries.
Following are the two methods that i have used for sequences
1. To get the sequence in a seperate query and then pass it to the insert query using PreparedStatement
2. Directly pass the SequenceName.NEXTVAL in the insert query.
I have seen in the projects that i have worked, Method 1 is used mostly. As far as i visualize getting sequence in a seperate query will increase
a round of I/O which would be 1 in second method.
Please help in identifying the pro's and cons of both the methods.
Awaiting your reply.
Fatih Keles wrote:If you need to use the generated key from sequence then you should either select seq.nextval before executing insert or use insert into returning clause.
No i dont need to use that key. Since it is a Primary Key for the table therefore i need to fetch the sequence while inserting the record.
One more point i would like to add in my above mentioned question.
I work in a tightly integrated system where several applications fire queries on a common database,
so what effect would each of the methods have on performance and execution time of the query.
Fatih Keles wrote:Executing a query from dual to fetch a value from a sequence is almost costless for Oracle database, but for the senario you have execute 2 queries consequently and total time is effected from many things like network, AS utilization etc. I would prefer to execute one query where possible.
That was really helpful.
It would have been great if some other people could give their comments on it.
Thanks in Advance