Re: Sybase/Oracle table size limitation / Largest production database

We have a few Sybase tables near two million rows each. The application is Data Warehouse. Operating System is Solaris 2.3 running on a Sun Space station 2000. No appreciable performance degration was noticed at any level once the data was not cached given that reasonable "where" selection criteria is applied. Even table scans seem to take about fifteen minutes. I have not looked lately but I may remember a small performance hit related to insert/update/delete operations. The indexes are not clustered. Perhaps clustering is the performance degradation factor - ???.

John Augustin.

In <yey1DD15w165w_at_bwalk.dm.com> dlv_at_bwalk.dm.com (Dr. Dimitri Vulis) writes:
>
>In article <478eto$epv_at_empire.texas.net>, smsi_at_texas.net (Robert
 Lesieur) writes:
>>Is there anyone out there that has implemented a Sybase/Oracle table
 within a
>>database instance that is greater than 1 million rows? How large was
 it
>>and what platform were you using?
>>
>>I have been told by knowledgable DBAs that both Oracle and Sybase
 performance
>>goes down the toilet after about 1 million rows due to the index
>>mechanism. This may apply to insert, delete, update and/or read.
>
>IN one of my previous jobs, I created a table with about 8 million
 rows
>on MS SQL Server under OS/2 2.x (486, 32MB, a bunch of SCSI hard
 disks).
>The performance was quite adequate. I did:
>
> sp_addtype cusip_type, "char(8)"
>
> sp_addtype date6, int /* yymmdd */
>
> create table dreturns(
> cusip cusip_type,
> ldate date6,
> dreturn float,
> source char(4)
> )
>
> create unique clustered index dreturns_ldate_cusip on
 dreturns(ldate,cusip)
> with ignore_dup_key
> create unique index dreturns_cusip_ldate on dreturns(cusip,ldate)
> with ignore_dup_key
>
>Every business day, about 6,000 new rows were inserted (from an online
 service)
>followed by 'update statistics'. Also sometimes cusips change, so
 someone
>would "update dreturns set cusip='new' where cusip='old'".
>
>The table was used to either get the returns for a given ldate and a
 few
>hundred cusips, or for a given cusip and a thousand ldates. In both
 cases
>it didn't feel any slower than getting data from a much smaller table.
>
>---
>
><a href="mailto:dlv_at_bwalk.dm.com">Dr. Dimitri Vulis</a>
>Brighton Beach Boardwalk BBS, Forest Hills, N.Y.: +1-718-261-2013,
14.4Kbps Received on Sat Nov 04 1995 - 00:00:00 CET