Re: Case for Index Organized table?

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 25 Apr 2007 11:56:26 -0700
Message-ID: <bf46380704251156j5856acadxb6de6b6f91351036@mail.gmail.com>

On 4/23/07, Elliott, Patrick <patrick.elliott_at_medtronic.com> wrote:
>
> I would make it a regular table. If you make it an IOT, then you are
> forcing twice the I/O on it. You have to ask yourself the question, "Do I
> always want to use an index when referencing this table?" If the answer is,
> "Yes" then make it an IOT. If this is a data warehousing type application,
> or reporting database scenario, then you may want to reference many columns
> in this table at once or even all of the rows in the entire table for a
> query that needs a whole day. If this is true, then maybe an IOT is not a
> good option.
>
>

Twice the IO? Why do you say that?

Here's a test using Tom Kytes test harness to compare 2 SQL statements:

CREATE TEST DATA

drop table t_control purge;
drop table t_test_heap purge;
drop table t_test_iot purge;

create table t_control ( sec number(5)) pctfree 0; create table t_test_iot ( sec number(5), primary key(sec)) organization index pctfree 0;
create table t_test_heap ( sec number(5)) pctfree 0;

create index t_test_heap_idx on t_test_heap(sec) pctfree 0;

alter table t_test_heap add constraint t_test_heap_pk primary key(sec); begin

   for i in 1..86400
   loop

      insert into t_control(sec) values(i);    end loop;
end;
/

insert into t_test_heap
select sec
from t_control
/

insert into t_test_iot
select sec
from t_control
/

commit;

RUN TESTS The first test runs each of these statements 10 time and compares the stats:

      select count(*) into x
      from (
         select 1
         from t_control tc, t_test_heap t
         where t.sec = tc.sec
      );

      select count(*) into x
      from (
         select 1
         from t_control tc, t_test_iot t
         where t.sec = tc.sec
      );

   end loop;

Results:

Notice that the IOT is twice as fast

11:50:53 SQL>@t1
320 hsecs
146 hsecs

PL/SQL procedure successfully completed.

The query with T_TEST_HEAP is the first column

11:51:00 SQL>@run_stats

NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
...
STAT...session logical reads                 868214     865277      -2937
STAT...consistent gets                       868176     865236      -2940
STAT...consistent gets from cache            868176     865236      -2940
STAT...session uga memory                     72952      -7488     -80440
STAT...session pga memory                    131072          0    -131072
STAT...consistent gets - examination             11     864010     863999
STAT...index fetch by key                         0     864000     864000
STAT...index scans kdiixs1                   864000          0    -864000
STAT...no work - consistent read gets        868130       1190    -866940
LATCH.cache buffers chains                  1736493     866606    -869887

53 rows selected.

The query against the IOT seems to be the better choice

Here is the SQL for the 2nd test:

   for i in 1 .. 2
   loop

      for srec in (select sec from t_control)
      loop
         select sec into x
         from t_test_heap t
         where t.sec = srec.sec;
      end loop;

   end loop;

   for i in 1 .. 2
   loop

      for srec in (select sec from t_control)
      loop
         select sec into x
         from t_test_iot t
         where t.sec = srec.sec;
      end loop;

   end loop;

Results:

The IOT query is a bit quicker:

11:53:58 SQL>@t2
672 hsecs
623 hsecs

PL/SQL procedure successfully completed.

11:54:12 SQL>@run_stats

NAME                                           RUN1       RUN2       DIFF
---------------------------------------- ---------- ---------- ----------
...
STAT...session logical reads                 348195     347614       -581
STAT...consistent gets from cache            348159     347573       -586
STAT...consistent gets                       348159     347573       -586
STAT...redo size                              41228      42236       1008
STAT...session uga memory                     80536         96     -80440
STAT...session pga memory                    131072          0    -131072
STAT...index scans kdiixs1                   172800          0    -172800
STAT...index fetch by key                         0     172800     172800
STAT...consistent gets - examination         172800     345601     172801
STAT...no work - consistent read gets        175348       1960    -173388
LATCH.cache buffers chains                   523674     349703    -173971

48 rows selected.

The distinction between using a HEAP table and an IOT is not quite as clear in this one as far as timing goes, but the IOT is clearly more latch friendly.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 25 2007 - 13:56:26 CDT