Re: Big Tables? Real Life Examples.....

Comments in-line.

Ian Turner wrote:
> Hi,
>
> Is there a practical limit on the size of database tables in Oracle?

No. I've built single tables in the TB range. A table is a logical set of data and should be as large as required to perform that purpose.

> Ideally I'd like some examples of systems that have tables containing many
> millions (or even billions of records), an indication of the size of server
> being used to run the database, the type of queries being run against the
> table (index lookup's, table scan's, etc.) and typical times for execution.

Bank of America, Washington Mutual Bank, Amazon.com, eBay, Homeland Security, FBI, NSA, American Express. At AT&T Wireless, now Cingular, we had tables that added millions of records per hour.

As to the size of the server that is proprietary but most are either using the larger *NIX boxes sold by Sun, HP, and IBM or building RAC clusters. Amazon.com, for example, has a 16 node cluster (64 CPUs).

Types of transactions are generally inserts, very few updates, with data moved to a data warehouse, and often aggregated, for reporting.

> The smaller the systems - in terms of server size the better!

No.

But you need to define your terms. What is "better"? Loose questions beget answers without context or meaning.

But I think the answer here is clearly no.

> Reason I ask is I'm getting told that it is impossible to hold all the
> values I want - around 150million records - for performance reasons.

Whoever told you that should be put out to pasture. That is barely a days worth of records at some banks or phone companies (and yes in one table).

> I'd
> have thought Oracle wouldn't have a problem performing index lookups and
> maybe index scans, but would not want to be running frequent table scans.

And I'd have thought people wouldn't give out such ridiculous advice as you received.

> I've asked our DBA to create some test tables and fill them full of sample
> data to see if we can get some example query times - but it would be useful
> to have some real world examples.
>
> Version of Oracle is 10g on Solaris. We are probably going to use commodity
> servers SUN V240's in a RAC configuration.
>
> regards
>
> Ian

Bad choice of hardware for a large database. But by Oracle's reckoning 150 million rows is small.

My impression from the information you have presented, its high level of inaccuracy, and the questions you have asked, that you desparately need to hire at least one senior person and/or a consultant with provable experience.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)