Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Case for Index Organized table?
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 );
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;
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;
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-lReceived on Wed Apr 25 2007 - 13:56:26 CDT