Re: How to optimize sequence retrieval in Oracle and Java

Marko Hantula wrote:
>
> Let's say I have a table & sequence like these :
>
> CREATE TABLE student(id NUMBER PRIMARY KEY, name VARCHAR2(50));
> CREATE SEQUENCE student_sequence START WITH 1 INCREMENT BY 1;
>
> Now let's say I have a Java method that creates a bunch
> of Student objects at once as follows :
>
> public Student[] createStudents(Connection Conn,
> String[] NameArray) throws
> Exception
> {
> Student[] Students = new Student[NameArray.length];
> long[] laIds = new long[NameArray.length];
>
> // Create ids
> for(int iIndex = 0; iIndex < NameArray.length; iIndex++)
> {
> Statement Stmt = Conn.createStatement();
> ResultSet Set = Stmt.executeQuery("SELECT student_sequence.NEXTVAL
> FROM dual");
>
> while(Set.next())
> laIds[iIndex] = Set.getLong(1);
>
> Set.close();
> Stmt.close();
> }
>
> // Create student objects
> PreparedStatement Stmt = Conn.prepareStatement("INSERT INTO
> student_table VALUES (?, ?)");
>
> for(int iIndex = 0; iIndex < NameArray.length; iIndex++)
> {
> // Add a batch to the statement
> Stmt.setLong(1, laIds[iIndex]);
> Stmt.setString(2, NameArray[iIndex]);
> Stmt.addBatch();
>
> // Create object
> Students[iIndex] = new Student(laIds[iIndex],
> NameArray[iIndex]);
> }
>
> // Execute batch & close
> Stmt.executeBatch();
> Stmt.close();
>
> return Students;
> }
>
> Now my question is, if the size of the student name array is
> large (e.g. 1000), how do I optimize the first part so that
> I would get all the ids at once in a batch operation,
> like I do in the insert part.
> The implementation above basically does N separate select
> statements from the database (across network) which is not too
> efficient.
>
> What would be the best way to optimize this code, should I
> possibly use a stored procedure that would return an array of
> ids or is there a better way to do this?
> I cannot use a bigger increment value in the sequence since
> the size of the array is not constant. Also, I need the ids because
> I have to return an array of Student objects, so I cannot use the
> sequence in the insert statement itself.
>
> Thanks,
> Marko

Method 1:
I would put a PRE-INSERT trigger on this table to get the sequence number and use SQL*Loader to load the data

Method 2:
I would take the query that fetches the next sequence out of the for loop (it get parsed each iteration) and use an array fetch to retrieve the desired number of sequence id's. Then I would use and an array insert to load the data.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.