Re: Case for Index Organized table?
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: veeeraman@xxxxxxxxx
- Date: Mon, 23 Apr 2007 22:45:06 +0200
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@xxxxxxxxx> 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"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
