Re: Case for Index Organized table?

I think that's the way to go - the IOT will be more compact than the index, since the former doesn't need to store the rowid (6 bytes) and the date is 7 bytes long, which makes for a nice saving of about 50% of space (not counting the row and block overhead, etc) and especially less buffer cache footprint (maybe you might be very lucky and save a branch level as well).

That is what I got for the script below, in 10.2.0.3 using ASSM; the index ffs took 213 consistent gets, the iot ffs took 137, 137/213=64%.

I'd also consider a partitioned iot (partitioned by range, each partition storing the 86400 rows for a day) in order to avoid the problem of having the table empty between the truncate and the next reload; but maybe this is just overkilling.

HTH
Alberto

create table t (x not null) pctfree 0
as
select trunc(sysdate) + rownum-1 as x
  from dual connect by level <= 86400;

create index t_idx on t(x) pctfree 0;

exec dbms_stats.gather_table_stats (user, 't', cascade=>true, method_opt=>'for all columns size 1', estimate_percent=>100);

create table t_iot (x not null, primary key (x)) organization index
pctfree 0
as
select trunc(sysdate) + rownum-1 as x
  from dual connect by level <= 86400;

exec dbms_stats.gather_table_stats (user, 't_iot', cascade=>true, method_opt=>'for all columns size 1', estimate_percent=>100);

select /*+ full */ count(*) from t;
select /*+ index_ffs(t t_idx) */ count(*) from t;
select /*+ full */ count(*) from t_iot;

set autotrace on
select /*+ full */ count(*) from t;

select /*+ index_ffs(t t_idx) */ count(*) from t;

select /*+ full */ count(*) from t_iot;
set autotrace off

On 4/23/07, Ram Raman <veeeraman_at_gmail.com> wrote:
> Hi all,
>
> I got a requirement from the developers to build a new table which has a
> column that will hold every second of every business day. That is the only
> column in the table. The table will be truncated everyday and loaded with
> the subsequent day's time. The developers told me that this table speeds up
> their queries, I also talked about using other ways but they said that they
> tested a few ways and found this one the best. They also want an index on
> that table. I am thinking of creating an index organized table for this
> purpose. Does anyone foresee any problem in this approach.
>
> Ram.
>

-- 
Alberto Dell'Era
"dulce bellum inexpertis"
--
http://www.freelists.org/webpage/oracle-l