Maybe that's your hardware, operating system or JVM version?
I was using Derby 10.4.1.3 on Solaris 10 and Java SE 6 on a dual CPU (AMD 2.4 GHz) machine.

I checked Derby 10.4.1.3 with JDK 5u15, JDK 6u6 and JDK 7 build 28 (latest now).
usage 10.4.1.3 seems to be 1 second faster, but 9,5 is the best time.

switching between JDK 5 -> 6 -> 7 did not increase speed.
 

But if 6 seconds is still too much, and you can't use another algorithm, I don't think I have anything more that can help you.

there are 100 algorithms, and they can not be written in pure-SQL. That is the main idea why i write SP instead of pure-SQL.


I don't think it is correct to blame this on Java. There are other database products using Java, and for all I know they might be faster for the specific case we are discussing.
I thought that if Java take 2 sec instead of 1 PL/SQL - java can scale up without license payments. So Java TCO can become cheaper than Oracle.


Also, I'm not sure how comparable the Java stored procedure code and the PL/SQL are.
Could you post the latter?
pl/SQL code + timing.

set term off
CREATE OR REPLACE PROCEDURE test1 AS
iTotalAmt NUMBER;
/*
можно было просто NUMBER написать, но захотелось показать
тесную интеграцию процедурного кода и структуры данных
*/

iLastClient testtotals.client%TYPE;
BEGIN
FOR cc IN ( SELECT client, order_amount
FROM testbig
ORDER BY client)
LOOP
If iLastClient IS NULL Then
iLastClient := cc.Client;
iTotalAmt := 0;
End If;

If iLastClient != cc.Client Then
UPDATE testtotals SET
client_total = iTotalAmt
WHERE client = iLastClient;
If SQL%ROWCOUNT = 0 Then
INSERT INTO testtotals
(client, client_total)
VALUES (iLastClient, iTotalAmt);
End If;
iLastClient := cc.Client;
iTotalAmt := 0;
Else
iTotalAmt := iTotalAmt + cc.order_amount;
End If;
END LOOP;
COMMIT;
END;
/
-- Непосредственно тестирование, запускаем три раза
DELETE FROM testtotals WHERE client < 64
/
COMMIT
/
set timing on term on
PROMPT Start
EXECUTE test1

set timing off term off
DELETE FROM testtotals WHERE client < 64
/
COMMIT
/
set timing on term on
EXECUTE test1

set timing off term off
DELETE FROM testtotals WHERE client < 64
/
COMMIT
/
set timing on term on
EXECUTE test1

set timing off term off
DROP PROCEDURE test1

/
set term on
PROMPT Done
 Code not exactly the same and it have ligical errors. But that error are not change time.

Does anyone know if (or rather how well maybe?) Oracle is capable of optimizing the PL/SQL?
I think tt PL/SQL are working with data on more low level (level near storage) than Debry.