Re: Is this a good use for an IOT type table or are there better solutions.

Colin,

First off, keep in mind that your DBA may have knowledge about how your application accesses this table that he has not explained. Normally, testing is the best option if you have the luxury of setting this up.

With that said...

I personally have found that if you are looking to do *range scans* against the data, and all of the columns of your resultset will fit nicely into an IOT page, then this is probably the best option. (This is only true if you will not be constantly updating the columns of the primary key.) Given the table description that you posted, it looks like it might work okay for you if this fits your scenario.

For primary key access via nested loops or single key lookups, I've found that that the heap table with an index performs better.

/* works good for iot */
SELECT * FROM TABLE ID=100 AND DATESTAMP BETWEEN SYSDATE AND SYSDATE+30; /* works good for heap */
SELECT * FROM TABLE ID=100 AND DATESTAMP=SYSDATE AND HOUR=4; Testing always reveals the truth.

Others here may have diffent input, but that's what I've seen.

Again, I'd check with your DBA before pushing for this because he/she may have information as to why an IOT will not work well in your scenario.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

colin_lyse_at_yahoo.com (colin_lyse) wrote in message news:<4060c496$0$3719$45beb828_at_newscene.com>...
> Using Oracle 9i R2 on a Sun SPARC 10k with 8 CPUS, 2 TB disk, 32 gig ram
> running SUN OS 5
>
> asking question because our DBA says current structure is best and only
> option, wanted to know if that is true
>
> we have a table with following structure
>
> ID NUMBER NOT NULL,
> DATESTAMP DATE NOT NULL,
> HOUR NUMBER NOT NULL,
> AAMP NUMBER,
> BAMP NUMBER,
> CAMP NUMBER,
> CAT NUMBER
>
> it has a unique index on ID, DATESTAMP, HOUR. it is used for access and
> aslo enforces uniqueness
>
> The table currently has 29 million rows and grows by 100,000 a day. the table
> is 1.3 gigs the index is 1.1 gigs. There is currently 287 days of data. There
> are 287 distinct datestamps (1 per day), 21 distinct hour values (21 hours
> each day), 7000 disitnct ID values, 3 distinct CAT values (99.9% are a single
> value) and 24,000 distinct values in the AAMP-CAMP.
>
> The table is always accessed by the index (it is via a web page and it uses
> the same query).
>
> Do we really need to have both table and index. Would it be better to have an
> IOT since we access data via index 100% of the time. Would there be a problem
> with the daily inseration of 100k rows? would it require rebuilding?
>
> Are there other options? What are the options when u have large tables with
> large indexes? is the table + index really the best option.
Received on Wed Mar 24 2004 - 10:41:38 CST