On 4/23/07, Elliott, Patrick <patrick.elliott@xxxxxxxxxxxxx> 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