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

colin_lyse wrote:

> 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.

Are the ID numbers sequential growing in a manner where all inserts would be at the 'end' of the table and not be into existing blocks? Are there ever updates or deletes?

But the question on my mind is why? This table is so small that space the little bit of space used by the index is inconsequential. What is the business problem you are attempting to solve. Also the number of inserts is very small ... only 70 per minute on average. Hardly seems worth even thinking about.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)