Oracle 通过 JDBC 直接加载 INSERT？

Question

是否可以通过 JDBC 在 Oracle 中进行直接加载 INSERT？

我目前使用批量准备好的语句（通过 Spring JDBC），有什么方法可以使这些语句绕过 NOLOGGING 表上的重做日志？

这是 Oracle 11g 的情况。

Answer 1

直接路径插入仅适用于 insert into x as select * from y 场景。这可以使用jdbc来完成，没问题。这不能通过插入和值来完成。当数据库处于强制日志记录模式时，也无法执行此操作。大多数时候，当备用数据库连接时，主数据库将处于强制日志记录模式。

正如 Gary Myers 提到的，从 11gR2 开始，就有了 APPEND_VALUES 提示。与“旧”附加提示一样，它只能用于批量插入。

我希望这有帮助，
罗纳德.

Answer 2

11gR2 中引入了一个 APPEND_VALUES 提示，用于使用 INSERT 进行直接路径插入。 ..价值观。

没有 11gR2 实例可用于测试它是否适用于 JDBC 批量插入。不过值得一试。

Answer 3

我能够通过 JDBC 批处理将 APPEND_VALUES 提示与 Oracle 12c 一起使用。我验证了通过 Oracle Enterprise Manager 发生的直接路径插入，其中解释计划显示加载为选择

编辑：我不再参与该项目，但我尝试提供更多详细信息：
代码如下：

    prepareTableForLargeInsert("TABLE_X")
    preparedStatement = conn.prepareStatement("INSERT /*+ APPEND_VALUES */ INTO TABLE_X(A, B) VALUES(?,?)");
    while(thereIsStuffToInsert()) {
      for (ThingToWrite entity : getBatch()) {
         int i = 1;
         preparedStatement.setLong(i++, entity.getA());
         preparedStatement.setString(i++, entity.getB());
         ...
       }
       preparedStatement.executeBatch();
       preparedStatement.clearParameters();
    }
  repairTableAfterLargeInsert("TABLE_X")

需要验证是否真正使用了直接路径（表被锁定/同一交易中的常规插入失败/实际执行计划显示加载为选择）

方法prepareTableForLargeInsert和repairTableAfterLargeInsert 正在调用存储过程。它们可能会有所帮助：

 PROCEDURE sp_before_large_insert (in_table_name   IN       VARCHAR2) AS
   BEGIN

          -- force parallel processing
          EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DML';
          EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL QUERY';
          EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DDL';

          -- set table to NOLOGGING
          EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' NOLOGGING';

          -- disable all FK constraints referencing the table. all but those used for Partition by reference
          FOR cur IN (SELECT a.owner, a.constraint_name, a.table_name
                                  FROM all_cons_columns a
                                  JOIN all_constraints c ON a.owner = c.owner
                                                        AND a.constraint_name = c.constraint_name
                                  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                                                           AND C.R_CONSTRAINT_NAME = C_PK.CONSTRAINT_NAME
                                 LEFT JOIN user_part_tables pt on pt.ref_ptn_constraint_name = c.constraint_name
                                 WHERE C.CONSTRAINT_TYPE = 'R'
                                 AND pt.ref_ptn_constraint_name IS NULL
                                 AND A.OWNER LIKE '%_OWNER'
                                 AND c_pk.table_name = in_table_name)
          LOOP
              execute immediate 'ALTER TABLE "'||cur.owner||'"."'||cur.table_name||'" MODIFY CONSTRAINT "'||cur.constraint_name||'" DISABLE';
          END LOOP;

          -- disable FKs (but one used for Partition by reference), PK (unless referenced by enabled FK for partition reference) and UCs on table
          FOR c IN (select distinct rc.CONSTRAINT_NAME FROM user_constraints rc
                                    LEFT JOIN user_part_tables pt on pt.ref_ptn_constraint_name = rc.constraint_name
                                    LEFT JOIN user_constraints c_fk ON c_fk.R_CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND c_fk.status = 'ENABLED'
                                    WHERE rc.owner like '%OWNER'
                                    AND pt.ref_ptn_constraint_name IS NULL
                                    AND c_fk.R_CONSTRAINT_NAME IS NULL
                                    AND rc.CONSTRAINT_TYPE IN ('R', 'U', 'P')
                                    AND rc.TABLE_NAME = in_table_name)
          LOOP
                EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' DISABLE CONSTRAINT ' || c.CONSTRAINT_NAME;
          END LOOP;


          -- set unusable non-local non-unique indexes on table
          FOR c IN (select INDEX_NAME from all_indexes
                      where table_owner LIKE '%_OWNER'
                      and PARTITIONED = 'NO'
                      and UNIQUENESS = 'NONUNIQUE'
                      and STATUS = 'VALID'
                      and TABLE_NAME = in_table_name)
          LOOP
                EXECUTE IMMEDIATE 'ALTER INDEX ' || c.index_name || ' UNUSABLE';
          END LOOP;
   END sp_before_large_insert;

 PROCEDURE sp_after_large_insert (in_table_name   IN       VARCHAR2) AS
   BEGIN
          -- rebuild disabled indexes on table
          FOR c IN (select INDEX_NAME from all_indexes
                                          where table_owner LIKE '%_OWNER'
                                          and STATUS = 'UNUSABLE'
                                          and TABLE_NAME = in_table_name)
          LOOP
                EXECUTE IMMEDIATE 'ALTER INDEX ' || c.index_name || ' REBUILD PARALLEL NOLOGGING';
          END LOOP;

          -- enable FKs, PK and UCs on table
          FOR c IN (select CONSTRAINT_NAME, CONSTRAINT_TYPE
                   FROM user_constraints
                   WHERE owner like '%OWNER'
                   AND CONSTRAINT_TYPE IN ('R', 'U', 'P')
                   AND TABLE_NAME = in_table_name)
          LOOP
                 IF c.CONSTRAINT_TYPE = 'P' THEN
                       EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' ENABLE CONSTRAINT ' || c.CONSTRAINT_NAME || ' USING INDEX REVERSE';
                 ELSE
                       EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' ENABLE CONSTRAINT ' || c.CONSTRAINT_NAME;
                 END IF;
          END LOOP;

          -- enable FKs constraints on related tables
          FOR cur IN (select fk.owner, fk.constraint_name , fk.table_name
            from all_constraints fk, all_constraints pk
             where fk.CONSTRAINT_TYPE = 'R' and
                   pk.owner LIKE '%_OWNER' and
                   fk.r_owner = pk.owner and
                   fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME and
                   pk.TABLE_NAME = in_table_name)
          LOOP
             execute immediate 'ALTER TABLE "'||cur.owner||'"."'||cur.table_name||'" MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE';
          END LOOP;

          -- set table to LOGGING
          EXECUTE IMMEDIATE 'ALTER TABLE ' || in_table_name || ' LOGGING';

          -- disable parallel processing
          EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';
          EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL QUERY';
          EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DDL';

          -- clean up indexes i.e. set logging and noparallel again
          FOR c IN (SELECT INDEX_NAME FROM ALL_INDEXES
                      WHERE (TRIM(DEGREE) > TO_CHAR(1) OR LOGGING = 'NO')
                      AND OWNER LIKE '%_OWNER'
                      AND TABLE_NAME = in_table_name)
          LOOP
                EXECUTE IMMEDIATE 'ALTER INDEX ' || c.index_name || ' NOPARALLEL LOGGING';
          END LOOP;
   END sp_after_large_insert;

我记得为禁用的 UC 重新创建定制索引存在问题，例如，因为丢失了它们如何分区的信息（全局哈希分区）（使索引无法使用不起作用）

注意：

• 并行到不同线程时一个人不会获得太多，因为这些线程中的每一个最终都会在
• 我们的表分区的表锁上序列化。如果批量写入不同的分区，则会观察到插入速度变慢 - 好的是每批写入尽可能少的分区，
• 如果每个线程写入自己的普通（临时？）表并在最后这些表，则可能会实现主要加速被“合并”到主表中 - 但这从未被尝试过

Answer 4

？

insert /*+ append */ into desttab select * from srctab 

在 JDBC 中不起作用

Answer 5

使用：

INSERT /*+ APPEND_VALUES */ INTO table_name (column1, column2) values (?,?);