python - 使用 Python 批量更新 SQL (Oracle)

Question

我正在寻求有关如何批量更新记录最多为 250,000 条的 Oralce SQL 表的帮助。

基本上，我有一个键列表，该列表被传递到一个函数，然后需要更新 Oracle 表。该列表最多可以有 250,000 行，我可以使用普通更新语句或使用“executemany”来完成此操作，但这两种方法效率都太低，所以我需要进行批量更新，但我不熟悉如何执行此操作。我已经搜索了几个小时，但我无法弄清楚!

todays_date = datetime.now().strftime("%d-%b-%Y")
status = str("DONE")

try:
  bind_values = {"status" : str(status),
                 "todays_date" : todays_date,
                 "keys_list" : list_of_keys}

  query = ("""FORALL i IN :keys_list.FIRST .. :keys_list.LAST
              UPDATE TABLE_NAME
              SET COLUMN1 = :status,
              UPDATE_DATE = :todays_date
              WHERE KEY = :i""")

  cursor.execute(query, bind_values)
  conn.commit()
  self.CloseConnection(conn)
except cx_Oracle.DatabaseError, e:
  error, = e.args
  print("  >> Database error: %s" % format(e))
  conn.rollback()
  return False

如有任何帮助，我们将不胜感激。

更新@abarnert - 非常感谢您的建议，您肯定在这里有所收获，我设法做到了这一点

cursor.execute("""CREATE GLOBAL TEMPORARY TABLE TodaysKeys
                 (key STRING PRIMARY KEY)
                 on commit delete rows
                 AS (INSERT INTO TodaysKeys VALUES (:i))
                 UPDATE TABLE_NAME
                 SET COLUMN1 = :status,
                 UPDATE_DATE = :todays_date
                 WHERE KEY IN (SELECT * FROM TodaysKeys)
                 TABLE TodaysKeys""", i=keys_list, 
                 status=str(updatestatus), 
                 todays_date=todays_date)

但现在我得到的只是一个错误:“ORA-01036:非法变量名/数字”。我确信这是非常明显的事情，但我已经检查了一遍又一遍，但我一辈子都看不出我哪里出了问题!

从对这种方法的所有研究来看，这似乎是正确的方法......如果我能让它进行测试的话!请帮忙。

Answer 1

您需要找到某种方法来跨 250K 行执行 1 次操作，而不是 250K 单独的 1 行操作，因为显然，考虑到您的数据模型设计(我猜您既不控制也不理解)，后者实在是太慢了。

那么，你是如何做到这一点的呢？

一种方法是创建一个极其简单的临时表，将今天的所有键转储到其中(使用 executemany 应该会更快，或者，如果不是，至少使用 会简单得多>LOAD DATA...)，然后执行引用该临时表中的键的 UPDATE 操作。像这样(伪代码，基于使用 sqlite3 进行测试，然后从远程内存转换为 Oracle...):

CREATE TEMPORARY TABLE TodaysKeys (key INT PRIMARY KEY)

INSERT INTO TodaysKeys VALUES (:i)

UPDATE TABLE_NAME
    SET COLUMN1 = :status,
    UPDATE_DATE = :todays_date
    WHERE KEY IN (SELECT * FROM TodaysKeys)

DROP TABLE TodaysKeys

如果这很慢，则意味着您在 KEY 列上没有索引，在这种情况下......确实没有办法加快速度解决这个问题。