使用JDBC从Oracle 11g删除记录 [英] Deleting records from Oracle 11g with JDBC

问题描述

我需要从包含900万条记录的表中删除10000条记录.从复杂的查询中提取要删除的ID，并将其存储在Java集合中.

I need to delete 10 000 records from a table containing 9 million records. The IDs which are to be deleted will be fetched from a complex query and stored in a Java collection.

我有3种方法来实现这一目标

I have 3 approaches to implement this

1)创建一个准备好的语句，并将10000条语句添加到批处理中并执行它.

1) Create a prepared statement and add 10000 statements to the batch and execute it.

声明将如下所示

Delete from <table_name> where id=?;

2)编写"in"查询，而不是批量使用"=". 喜欢

2) Write a 'in' query rather than using '=' in a batch. Like

在此之下，可以在Java代码中以逗号分隔的值创建10000个ID，并将其添加到查询中.或者，将10000个ID插入到临时表中，并在子查询中从该表中进行选择.

Under this, the 10 000 IDs can be created as comma separated values in Java code and added to the query. Or, 10000 IDs are inserted in to a temporary table and make a select from that table in the sub query.

Delete from <table_name> where id in (<CSV>);
                 or
Delete from <table_name> where id in (select id from <temp_table>);

表中没有约束和索引.而且我无法添加一个，因为我正在处理现有表.

There are no constraints and indexes in the table. And I cannot add one, because I'm working on a existing table.

第一个选择是花一些时间才能完成.它已经运行了15个小时，但仍未完成.

First option is taking ages to complete. It was running for 15hrs and still not completed.

推荐答案

您的第一个版本限制为1000个值，并且运行情况往往不理想.第二种方法可能会更好，但是您必须有一个全局临时表，并填充它是一个额外的步骤.

You first version has a limit of 1000 values and tends to not perform well. The second approach may perform better but you have to have a global temporary table and populating it is an extra step.

您可以将Java集合转换为Oracle集合.您可以为此创建自己的表类型，但是可以在其中使用诸如ODCINUMBERLIST之类的内置表类型.您可以将其视为表集合表达式.

You can convert your Java collection to an Oracle collection. You can create your own table type for this, but there are built-in ones like ODCINUMBERLIST which you can use here. You can the treat that as a table collection expression.

详细信息可能会有所不同，具体取决于您的Java集合类型，但其轮廓类似于:

The details may vary slightly depending on your Java collection type, but the outline is something like:

ArrayDescriptor aDesc = ArrayDescriptor.createDescriptor("SYS.ODCINUMBERLIST",
  conn);
oracle.sql.ARRAY oraIDs = new oracle.sql.ARRAY(aDesc, conn, yourJavaCollectinOfIDs);

cStmt = (OracleCallableStatement) conn.prepareCall(
  "Delete from <table_name> 
   where id in (select column_value from table(?))");
cStmt.setArray(1, oraIDs);
cStmt.execute();

除非它已经是一个简单的数组，否则您将需要在调用中将Java集合转换为数组；例如如果您使用的是名为yourArrayList的ArrayList，则可以执行以下操作:

Unless it is already a simple array, You will need to convert your Java collection to an array in the call; e.g. if you're using an ArrayList called yourArrayList, you would do:

oracle.sql.ARRAY oraIDs = new oracle.sql.ARRAY(aDesc, conn, yourArrayList.toArray());

您仍然会缺少主键或索引，但是与CSV列表(或多个CSV列表或在一起，因为您有1000多个ID在一起)相比，Oracle将为Oracle提供更好的机会来优化它.

You will still suffer from the lack of a primary key or index but it will give Oracle a better chance to optimise it than the CSV list (or multiple CSV lists OR'd together as you have more than 1000 IDs).

这篇关于使用JDBC从Oracle 11g删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！