Skip to Main Content
  • Questions

  • direct path inserts with ined organized tables

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Kurt.

Asked: April 30, 2004 - 8:53 am UTC

Last updated: June 11, 2010 - 7:19 am UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

hi tom,
i have to convert a heap table with about 200 million lines into an iot.
my idea is to use direct path inserts to get this job quickly done.
(this approach is very efficient with heap organized tables.)

so i set up a small testcase with heap tables and iots:
(database: oracle 9.2.0.4 on sun solaris)

-- LOGGING
drop table tab_heap1;
create table tab_heap1 (
username varchar2(30) not null,
type varchar2(12) not null,
name varchar2(30) not null,
version number(13) not null,
line number(12) not null,
date_changed date not null
) logging;

set autotrace on
insert into tab_heap1
select owner,type,name,1,line,sysdate from dba_source where rownum < 100000;

timing: 00:00:05.08

statistics
----------------------------------------------------------
511 recursive calls
3846 db block gets
5267 consistent gets
48 physical reads
5456380 redo size
437 bytes sent via SQL*Net to client
619 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99999 rows processed




-- NOLOGGING
drop table tab_heap2;
create table tab_heap2 (
username varchar2(30) not null,
type varchar2(12) not null,
name varchar2(30) not null,
version number(13) not null,
line number(12) not null,
date_changed date not null
) nologging;

set autotrace on
insert /*+ append */ into tab_heap2
select owner,type,name,1,line,sysdate from dba_source where rownum < 100000;

timing: 00:00:05.03

statistics
----------------------------------------------------------
530 recursive calls
248 db block gets
4613 consistent gets
48 physical reads
27580 redo size
422 bytes sent via SQL*Net to client
633 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99999 rows processed



--
-- IOT-testcase
--

-- IOT LOGGING
drop table tab_iot1;
create table tab_iot1 (
username varchar2(30) not null,
type varchar2(12) not null,
name varchar2(30) not null,
version number(13) not null,
line number(12) not null,
date_changed date not null,
CONSTRAINT TIOT1_PK PRIMARY KEY (username, type, name, version, line)
)
ORGANIZATION INDEX COMPRESS 3 PCTFREE 0 LOGGING PCTTHRESHOLD 5;

set autotrace on
insert into tab_iot1
select owner,type,name,1,line,sysdate from dba_source where rownum < 100000;

timing: 00:00:21.00

statistics
----------------------------------------------------------
471 recursive calls
18123 db block gets
6012 consistent gets
48 physical reads
18370152 redo size
456 bytes sent via SQL*Net to client
618 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99999 rows processed


-- IOT NOLOGGING
drop table tab_iot2;
create table tab_iot2 (
username varchar2(30) not null,
type varchar2(12) not null,
name varchar2(30) not null,
version number(13) not null,
line number(12) not null,
date_changed date not null,
CONSTRAINT TIOT2_PK PRIMARY KEY (username, type, name, version, line)
)
ORGANIZATION INDEX COMPRESS 3 PCTFREE 0 NOLOGGING PCTTHRESHOLD 5;

set autotrace on
insert /*+ append */ into tab_iot2
select owner,type,name,1,line,sysdate from dba_source where rownum < 100000;

timing: 00:00:19.05

Statistiken
----------------------------------------------------------
407 recursive calls
18015 db block gets
5978 consistent gets
48 physical reads
18453524 redo size
464 bytes sent via SQL*Net to client
632 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
99999 rows processed


my testcases show that nologging and direct path inserts work fine for heap tables.
it seems that nologging and /*+ append */ do not speed up inserts into index organized tables (same amount of redo)?
are my testcases correct or do i have a fatal mistake in my iot-testcase?
if my testcase is correct, can you explain why inserts into iot's do not benefit from nologging and the append hint?

Thanks!


and Tom said...

append writes above the HWM of a heap table. data in a heap can go "anywhere"
through an index on that heap table and see what happens..


an IOT is a complex data structure, data has a PLACE that is needs to go. you cannot just write "above the HWM".

but...

ops$tkyte@ORA9IR2> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'

NAME VALUE
------------------------------ ----------
redo size 588

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table tab_iot2 (
2 username not null,
3 type not null,
4 name not null,
5 version not null,
6 line not null,
7 date_changed not null,
8 CONSTRAINT TIOT2_PK PRIMARY KEY (username, type, name, version, line)
9 )
10 ORGANIZATION INDEX COMPRESS 3 PCTFREE 0 NOLOGGING PCTTHRESHOLD 5
11 as
12 select owner,type,name,1,line,sysdate from dba_source where rownum < 100000;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 1: select a.name, b.value V, b.value-&V diff
new 1: select a.name, b.value V, b.value- 588 diff
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'

NAME V DIFF
------------------------------ ---------- ----------
redo size 112672 112084


use CTAS to do the initial build and you won't have the index merge at the end which is what is causing the redo to be generated.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent

A reader, April 30, 2004 - 7:57 pm UTC


IOT CTAS: implicit ordering of rows

Alberto Dell'Era, May 01, 2004 - 2:40 pm UTC 

And interestingly the rows are ordered by pk before loading; which means that for an high-performance IOT CTAS one has to be sure to provide enough sort area.

Also, adding an ORDER BY to the select (as I do by instinct when CTASelecting) is only going to waste resources.

Proof (9.2.0.5):

SQL>
SQL> -- make a source table, with randomly ordered rows
SQL> create table s (x) as
  2  select rpad ( rownum, 10)
  3    from all_objects, all_objects
  4   where rownum <= 1000000
  5   order by dbms_random.random;

Table created.

SQL>
SQL> create or replace view sorts_probe as
  2   select value
  3    from v$mystat
  4   where statistic# =
  5         (
  6  select statistic#
  7    from v$statname
  8   where name = 'sorts (rows)'
  9         );

View created.

SQL>
SQL> -- store sorts counter
SQL> variable begin_sorts number
SQL> exec select value into :begin_sorts from sorts_probe;

PL/SQL procedure successfully completed.

SQL>
SQL> -- show that sorts_probe doesnt's sort
SQL> select value-:begin_sorts diff from sorts_probe;

      DIFF
----------
         0

SQL>
SQL> -- iot ctas without order by
SQL> create table iot (x, constraint iot_pk primary key(x))
  2  as select * from s;

Table created.

SQL>
SQL> select value-:begin_sorts diff from sorts_probe;

      DIFF
----------
   1000000

SQL> exec select value into :begin_sorts from sorts_probe;

PL/SQL procedure successfully completed.

SQL>
SQL> -- iot ctas with order by
SQL> create table iot_order_by (x, constraint iot_order_by_pk primary key(x))
  2  as select * from s ORDER BY X;

Table created.

SQL>
SQL> select value-:begin_sorts diff from sorts_probe;

      DIFF
----------
   2000000

SQL>
SQL> -- show that the tables are the same (no extra blocks etc)
SQL> exec dbms_stats.gather_table_stats (user,'iot', cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats (user,'iot_order_by', cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL> select table_name, leaf_blocks, blevel
  2    from user_indexes
  3   where table_name in ('IOT','IOT_ORDER_BY');

TABLE_NAME           LEAF_BLOCKS     BLEVEL
-------------------- ----------- ----------
IOT_ORDER_BY                2924          2
IOT                         2924          2

Great but what if the table is really large

Tom, June 09, 2010 - 4:48 pm UTC

I have a 200G partitioned IOT table that needs to be changed to have a different order of the PK columns. So I am trying to build a new table and copying the partitions one at a time. To do this as one huge CTAS does not seem very practical and would probably blow out my maintenance window. Would building seperate IOT tables for each partition and then swapping them into the new large IOT table work best to avoid redo generation?
Tom Kyte
June 10, 2010 - 12:25 pm UTC

.. To do this as one huge CTAS does not
seem very practical and would probably blow out my maintenance window. ...

and how would doing this partition by partition be more practical and faster?

With the CTAS you can bypass redo/undo generation and get it done as fast as possible.

200g is not that large in the year 2010.


A reader, June 10, 2010 - 5:55 pm UTC

Hello Tom,

"I have a 200G partitioned IOT table that needs to be changed to have a different order of the PK
columns."

What is a benefit of changing order of a PK compare to effort and time spend after recreate new IOT just to change order of PK?


Tom Kyte
June 11, 2010 - 7:19 am UTC

... What is a benefit of changing order of a PK compare to effort and time spend
after recreate new IOT just to change order of PK?
...


sorry, I could not parse that sentence.


But let me attempt to take a poke at it.

question what is the benefit of changing the order of the columns in a primary key, especially in an index organized table.

answer: it has to do with how the bits and bytes of data would be located on disk. Suppose you get stock quotes every day after the market closes. You are told the primary key of the data is THE_DATE, STOCK_SYM and you have data like open_value, close_value, volume that goes with it.

So, you create a table
stocks( the_date, stock_sym, ...., primary key(the_date,stock_sym) ) organization index;

and you load it up. It has hundreds of days of observations now. You find however that your most popular queries are:


select avg(close_val) from stocks where stock_sym = ? and the_date between ? and ?;


or a similar query:


select (close_val) from stocks where stock_sym in ( ?,?,?,?,?) and the_date between ? and ?;


the first gets an average for a given stock, the second is used to graph the differences between up to five stocks. They are executed a lot.

Is your data organized to efficient retrieve that data? Nope, you have - by putting the_date first in the primary key, made sure that every observation for a given stock symbol (say ORCL) is on a different block from every other ORCL observation. If you needed five days worth of ORCL stock quotes - you'd have to scan over ALL OF THE STOCK data for five days (thousands and thousands of rows).

If you used a primary key (stock_sym,the_date) - all of a sudden, the five rows you need are on the SAME block in the index (or at most, two blocks). You scan FIVE records, no more, no less in the index. Fast, efficient.

Likewise for the second query


reordering the primary key columns in an IOT has a dramatic effect on the location of data on disk.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions