Posted by An AskTom contributor brought to my attention, that direct mode insert on index organized tables now appears possible in 12c. We can see the difference by running a simple script in both v11 and v12
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
5 rows selected.
SQL> drop table t purge;
Table dropped.
SQL> create table t(x int primary key, y int) organization index;
Table created.
SQL> insert /*+ append */ into t select 1 x, 1 y from dual;
1 row created.
SQL> select * from t; -- no error, so no direct mode was done
X Y
---------- ----------
1 1
1 row selected.
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
5 rows selected.
SQL> drop table t purge;
Table dropped.
SQL> create table t(x int primary key, y int) organization index;
Table created.
SQL> insert /*+ append */ into t select 1 x, 1 y from dual;
1 row created.
SQL> select * from t; -- now we do get the error, so append hint was respected
select * from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
So that looks pretty cool. People may recall an older blog post of mine where it was not possible to do a large scale IOT population without a large underlying sort operation. So I thought I’d revisit that, and see what we can discover.
In this case, we are using table T_SOURCE as the source of our data. It was created simply as:
SQL> create table T_SOURCE ( n , r , constraint T_SOURCE_PK primary key( n ))
2 organization index
3 as select rownum, rpad(rownum,128)
4 from dual
5 connect by level <= 1000000
6 /
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','t_source')
PL/SQL procedure successfully completed.
So we are going to copy those 1million rows from T_SOURCE to a new table, and try to avoid having to sort the rows by asking the optimizer to “walk” the source data in index order.
So let’s try direct mode first. You’ll references to a script called “mystat”, that’s simply a query to v$mystat to pick up session level statistics that might be relevant.
We’ll run these before and after to see what deltas pop out.
SQL> truncate table t_tgt;
Table truncated.
SQL> alter table T_TGT nologging;
Table altered.
SQL> @mystat
Enter value for statname: redo size
NAME VALUE
-------------------------------------------------- ----------
redo size 135736
redo size for lost write detection 0
redo size for direct writes 0
3 rows selected.
SQL> @mystat
Enter value for statname: sorts
NAME VALUE
-------------------------------------------------- ----------
sorts (memory) 117
sorts (disk) 0
sorts (rows) 359
3 rows selected.
SQL> set timing on
SQL> insert /*+ APPEND */ into T_TGT select /*+ index_asc(t_source) */ * from T_SOURCE order by 1 ;
1000000 rows created.
Elapsed: 00:00:46.30
SQL> select count(*) from t_tgt where rownum < 10;
select count(*) from t_tgt where rownum < 10
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SQL> commit;
Commit complete.
SQL> @mystat
Enter value for statname: redo size
NAME VALUE
-------------------------------------------------- ----------
redo size 497096676
redo size for lost write detection 0
redo size for direct writes 0
3 rows selected.
SQL> @mystat
Enter value for statname: sorts
NAME VALUE
-------------------------------------------------- ----------
sorts (memory) 122
sorts (disk) 1
sorts (rows) 1000436
3 rows selected.
SQL> @mystat
Enter value for statname: direct
NAME VALUE
-------------------------------------------------- ----------
db block gets direct 0
consistent gets direct 0
physical reads direct 19231
physical writes direct 19231
physical reads direct temporary tablespace 19231
physical writes direct temporary tablespace 19231
So we can make the following deductions
- Direct mode seems to have been activated, due to the ORA-12838 error.
- I didn’t avoid the sorting, because I sorted 1,000,000 rows
- The NOLOGGING attribute seems of no benefit here – still consumed 500megabytes of redo
So whether it was really a direct mode operation is perhaps up for debate, because all of the direct reads/writes recorded were all against temporary tablespace.
Let’s explore that last one a little.
I repeated the same population script, but before I did so, I set my workarea_size_policy to manual, and cranked up my sort_area_size to 1G.
I’ll save you all the output again, but here’s the stats output
SQL> @mystat
Enter value for statname: direct
NAME VALUE
-------------------------------------------------- ----------
db block gets direct 0
consistent gets direct 0
physical reads direct 0
physical writes direct 0
physical reads direct temporary tablespace 0
physical writes direct temporary tablespace 0
So…no direct operations here now. This would appear to confirm that the direct mode operations were simply sorting operations.
Now we’ll perform the same process without using the direct mode hint. We’ll set the table back to logging because we’ve seen that’s of no benefit, and use a fresh session to pick up fresh statistics
SQL> truncate table t_tgt;
Table truncated.
SQL> alter table T_TGT logging;
Table altered.
SQL> @mystat
Enter value for statname: redo size
NAME VALUE
-------------------------------------------------- ----------
redo size 137236
redo size for lost write detection 0
redo size for direct writes 0
3 rows selected.
SQL> @mystat
Enter value for statname: sorts
NAME VALUE
-------------------------------------------------- ----------
sorts (memory) 21
sorts (disk) 0
sorts (rows) 86
3 rows selected.
SQL> insert into T_TGT select /*+ index_asc(t_source) */ * from T_SOURCE order by 1 ;
1000000 rows created.
Elapsed: 00:00:13.96
SQL> set timing off
SQL> select count(*) from t_tgt where rownum < 10;
COUNT(*)
----------
9
1 row selected.
SQL> commit;
Commit complete.
SQL> @mystat
Enter value for statname: redo size
NAME VALUE
-------------------------------------------------- ----------
redo size 500630220
redo size for lost write detection 0
redo size for direct writes 0
3 rows selected.
SQL> @mystat
Enter value for statname: sorts
NAME VALUE
-------------------------------------------------- ----------
sorts (memory) 26
sorts (disk) 0
sorts (rows) 146
3 rows selected.
SQL> @mystat
Enter value for statname: direct
NAME VALUE
-------------------------------------------------- ----------
db block gets direct 0
consistent gets direct 0
physical reads direct 0
physical writes direct 0
physical reads direct temporary tablespace 0
physical writes direct temporary tablespace 0
So for the conventional mode we observe
- I didn’t have to sort the rows
- I consumed similar amount of redo
- It was three times faster than the case where we had to so all that sorting to disk
This just goes to show, that (like everything in software), the advice of “Do ‘X’ and in every case it will be faster” is never a good thing to adhere to.
Direct mode insert is a very cool facility, but it doesn’t mean that it’s going to be the best option in every situation.
Learning is not a spectator sport 
The NOLOGGING attribute seems of no benefit here – still consumed 500megabytes of redo.
but those REDO’s were not against the index, not against the table.
Hi Connor,
I guess the nologging clause for IOTs is only effective, to reduce redo, in the CTAS.
Connor,
Playing late catch-up thanks to today’s tweet linking to this post.
May be the potential benefit comes from cases where there’s a lot of data already in place; or when there’s an overflow segment. For an insert when data exists, insert without sorting probably has to take the “single row update” strategy, while /*+ append */ allows for sorting and multiple changes per block in the delayed insert.