Hi,
I am trying to do below insert with append hint and expecting optimizer to use DIrect path load but it is still using conventional path and seeing below line in explain plan, can you please advise:
- Direct Load disabled because insert values with no append values hint used
explain plan for
insert /*+ append */ into employees_bkp(employee_id, first_name, last_name, email, job_id, hire_date)
values(310, 'SHIVANIhjhk' , 'GUPTAll', 'SHIVANI.CSE@GMAIL.COM', 'AD_PRES', '23-NOV-2015');
select * from
table(dbms_xplan.display());
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 71 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | EMPLOYEES_BKP | | | | |
------------------------------------------------------------------------------------------
Note
-----
- Direct Load disabled because insert values with no append values hint used
As the note implies, you need to use APPEND_VALUES, not APPEND to get direct path mode for single-row inserts:
create table t ( c1 int );
explain plan for
insert /*+ append */into t values ( 1 );
select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | T | | | | |
---------------------------------------------------------------------------------
Note
-----
- Direct Load disabled because insert values with no append values hint used
explain plan for
insert /*+ append_values */into t values ( 1 );
select * from dbms_xplan.display();
PLAN_TABLE_OUTPUT
Plan hash value: 3581094869
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 100 | 1 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | T | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | | | | |
| 3 | BULK BINDS GET | | | | | |
-----------------------------------------------------------------------------------------