IOT’s and a nasty bite on TEMP

Recently, I was creating an IOT from an existing table via, and wanted to achieve it without logging.

You cannot do this in two steps (ie, create table, followed by insert-append) because it will still be logged (see the addenda at the end of this post)

So you need to do it with a CTAS, for example:

create table MY_IOT
(...)
tablespace MY_DATA
organization index
nologging
as select * from ANOTHER_IOT order by ANOTHER_COL;

In my case, I opted for the “order by ANOTHER_COL” because this yielded an execution plan of INDEX FULL SCAN (not FFS) on ANOTHER_IOT, with no sort operation. This sounded like a good thing to do, because this table was around a terabyte in size and I’d rather not be sorting that

So I set it running … and after a while…bang! It blows up running out of space in temporary tablespace.

Now if the execution plan suggests that no sorting was going on, why did it blow up on temp space.

Time for tracing…

Source table:

drop table T_SOURCE purge;

create table T_SOURCE ( n , r , constraint T_SOURCE_PK primary key( n ))
organization index
as select rownum, utl_raw.cast_to_raw(rpad(rownum,128))
from dual
connect by level <= 1000000
/

Target table:

create table T_TGT ( n , r , constraint T_TGT_PK primary key( n ))
organization index
nologging
as select * from T_SOURCE order by 1
/

which yields this plan:

-----------------------------------------------
| Id  | Operation              | Name        |
-----------------------------------------------
|   0 | CREATE TABLE STATEMENT |             |
|   1 |  LOAD AS SELECT        | T_TGT       |
|   2 |   INDEX FULL SCAN      | T_SOURCE_PK |
-----------------------------------------------

but when you trace it, you get this in the trace file

CREATE UNIQUE INDEX "T_TGT_PK" on "T_TGT"("N") INDEX ONLY TOPLEVEL TABLESPACE "USERS" STORAGE( BUFFER_POOL DEFAULT) NOLOGGING NOPARALLEL as select * from T_SOURCE order by 1

which if you run an explain on that, gives:

------------------------------------------------------
| Id  | Operation              | Name        | Rows  |
------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |             |  1000K|
|   1 |  INDEX BUILD UNIQUE    | T_TGT_PK    |       |
|   2 |   SORT CREATE INDEX    |             |  1000K|
|   3 |    SORT CREATE INDEX   |             |  1000K|
|   4 |     INDEX FULL SCAN    | T_SOURCE_PK |  1000K|
------------------------------------------------------

welcome to sorts-ville …. population me 😦

There was no real way around this via standard SQL. Ultimately, we grabbed some scratch storage, plonked it into TEMP and let it run to completion.

Jonathan Lewis posted an interesting possible workaround. Given that SQL Loader can do sorted index loads, you could unload the source table to a unix pipe, and use SQL Loader direct load to read from the pipe with direct-nosort load. Some more details on that here

“Appendix”

Proof that insert-append for an IOT, even if that IOT is marked as nologging, will indeed be logged:

SQL> alter table T_TGT nologging;

Table altered.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                                3924
redo size for lost write detection                          0
redo size for direct writes                                 0

SQL> insert /*+ APPEND */ into T_TGT select * from T_SOURCE order by 1
2  /

1000000 rows created.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           477491604
redo size for lost write detection                          0
redo size for direct writes                                 0