Re: Indexed Organized Tables

Florian wrote:

> Hi,
>
> We have table that holds a lot of data, usually around 5 million rows.
> The data in the table is log data, that is rows are constantly being
> added and every other month old records are purged.
>
> Searches on this table are usually sorted by the time stamp, for example
> listing the most recent 100 records.
>
> The table looks like this:
>
> x_timestamp date,
> loginfo1 int,
> loginfo2 int,
> loginfo3 int,
> loginfo4 int,
> loginfo5 int,
> loginfo6 int,
> logtext1 varchar2(32),
> logtext2 varchar2(128)
>
> We have this same table on MSSQL server where we are using a clustered
> index on the timestamp field, which really improves performance.
>
> Now, we would like to do the same on Oracle, but are running into the
> primary key problem with the IOT.
>
> Sometimes multiple records are written at the same time (same second),
> so it is impossible for us to create a primary key on the x_timestamp
> field, since we might loose records that.
>
> Yet, we don't want to add any of the other fields to the timestamp since
> that doesn't seem to be logical. We tried it and the performance wasn't
> that great it seems.
>
> Is there no way to create an indexed organized table without using a
> primary key??
>
>
> Thanks ...

If you don't need a primary key then you don't need an IOT: Just go with a heap table.

If you do need a primary key then change the data type from DATE to TIMESTAMP(9).

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---