When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?

by

Please note that what I say below is based on observations and inferences of Oracle in use, and is not based on any deep understanding of Oracle’s internals. None of it should be considered authoritative.

What ninesided said in their first paragraph is correct. However, beware the test suggested. Not all long-running oracle queries are the same. It seems that queries are evaluated over two phases, first a phase that combines up sufficient data to know how to return the rows in the right order, and second a phase that returns the rows filling in the gaps that it didn’t compute in the first phase. The division of work between the two phases is also affected by the settings of the cost-based optimizer. e.g. First-rows vs All-rows.

Now, if the query is in phase 1, the cancel request seems at best to be queued up to be applied at the end of phase 1, which means that the query continues operating.

In phase 2, rows are returned in bunches, and after each bunch, the cancel command can take effect, so assuming the driver supports the command, the cancel request will result in the query being killed.

The specification for the JDBC cancel command does not seem to say what should happen if the query does not stop running, and therefore the command may wait for confirmation of the kill, or may timeout and return with the query still running.

More Related Contents:

  1. The network adapter could not establish the connection – Oracle 11g
  2. How do I limit the number of rows returned by an Oracle query after ordering?
  3. When do I need to use a semicolon vs a slash in Oracle SQL?
  4. LISTAGG in Oracle to return distinct values
  5. Fetch the row which has the Max value for a column
  6. Oracle SQL: Update a table with data from another table
  7. Oracle SQL – How to Retrieve highest 5 values of a column [duplicate]
  8. Concatenate and group multiple rows in Oracle [duplicate]
  9. Difference between BYTE and CHAR in column datatypes
  10. SQL to generate a list of numbers from 1 to 100
  11. How to use BOOLEAN type in SELECT statement
  12. Call a stored procedure with another in Oracle
  13. How to show all privileges from a user in oracle?
  14. SQL – How to select a row having a column with max value [duplicate]
  15. create table with sequence.nextval in oracle [duplicate]
  16. Oracle: loading a large xml file?
  17. IF EXISTS condition not working with PLSQL
  18. Using pivot on multiple columns of an Oracle row
  19. Solution to “cannot perform a DML operation inside a query”?
  20. Why can’t I seem to force Oracle 11g to consume more CPUs for a single SQL query
  21. How to query a CLOB column in Oracle
  22. Oracle (Old?) Joins – A tool/script for conversion?
  23. update columns values with column of another table based on condition [duplicate]
  24. Split varchar into separate columns in Oracle
  25. Using Alias In When Portion of a Case Statement in Oracle SQL
  26. What does a (+) sign mean in an Oracle SQL WHERE clause? [duplicate]
  27. Connecting to Oracle Database using Sql Server Integration Services
  28. ORA-00933: SQL command not properly ended
  29. Update a table with data from another table
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment