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

From your description a partitioned IOT
looks like an obvious choice.

If you want to think about dropping
old data very efficiently after a few years, then partitioning by date could be beneficial.

If you want to minimise contention then
partitioning by ID would be beneficial if a typical query is:

    select
    where ID = {const}
    and datestamp between {} and {}
This would probably minimise the cost
of data loading as well.

Before you make any changes, though,
try to get a picture of the full life-cycle of the data; and consider what problems
different solutions might give you at different points in that life-cycle.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar


"colin_lyse" <colin_lyse_at_yahoo.com> 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.

>