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

I think you'll find that MANY Sybase and Oracle customers have tables larger than 1 million rows.

We have several 3 million+ row tables, and one that is 13 million rows long. The 13 million row table has a clustered index, and several nonclustered indexes. None of them are more than 3 index level deep. The first rule of thumb for Sybase indexes is, for performance reasons, to keep indexes less than 3 or 4 levels deep. As long as you can achieve this, you are good to go. The system stored procedure sp_estspace (System 10 only, not in 4.9.4) will give you lots of information about a table and its indexes.

I don't think that index performance is the greatest limiting factor in the implementation of large tables. Maintenance and revcoverability are the big issues.

Sybase requires a routine called "update statistics" to be run occasionally against user tables. It updates the index statistical page that the query optimizer refers to when it evaluates a query before it is run. As tables get larger, "update statistics" takes significantly longer.

Sybase also requires another tool -- dbcc (database consistency checker) to be run from time to time to ensure that tables are not corrupt. Again, dbcc takes time, and as tables get larger, so does the execution time of dbcc.

Also, using the row count of a table can be a little misleading. You're also going to be concerned with the total amount of space used by each row (row size). A 500,000 row table could take up much more space (thus requiring many more data pages and more internal housekeeping) than a 1 million row table, if the 500K row table has twice as much data in one row.

-- 

Guy Anderson                       |   Computer Language Research
Sr. Database Analyst               |   2395 Midway Road
E-mail:  Guy_Anderson_at_fasttax.com  |   Mail Stop 450
                                   |   Carrollton, TX  75006
" Opinions expressed here represent
  only the author - not his boss or 
  the company he works for..."