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

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 Tue Mar 23 2004 - 17:16:14 CST