Enhancement in Direct Path Insert - Oracle 23ai Feature

This is one another nice feature introduced in 23ai.

Oracle 23ai enhanced this feature with follow up DMLs after the direct path insert. 

When we are loading lots of data into the Datawarehouse , we typically use direct path insert , as the data load is faster ; as oracle directly writes into the data file , bypassing the buffer cache and above the high water mark.

But , we can't do any data manipulation on the underlying table without issuing COMMIT / ROLLBACK.

Now , in Oracle 23ai ,we can use DMLs to view the table contents. This features enables us to do multiple checks before committing the data into the database. 

Comments Welcome!

Here is an example. 

SQL> select banner from v$version;

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL> DROP TABLE IF EXISTS emp;     

Table dropped.

SQL> create table emp 

  2  as 

  3  select object_name as emp_name , 

  4  object_type as emp_dept 

  5  from all_objects

  6  where 1 = 2;

Table created.

SQL> select count(*) from emp;

  COUNT(*)

----------

0

SQL> insert /*+ APPEND */ into emp

  2  select object_name , object_type 

  3  from all_objects;

52749 rows created.

SQL> select count(*) from emp;

  COUNT(*)

----------

     52749

SQL> update emp 

  2  set emp_dept ='HR'

  3  Where emp_dept = 'TABLE';

149 rows updated.

SQL> commit;

Commit complete.