Big Tables? Real Life Examples.....
Ian Turner
Is there a practical limit on the size of database tables in Oracle?
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.
The smaller the systems - in terms of server size the better!
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. 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.
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
Noons
> Is there a practical limit on the size of database tables in Oracle?
The doco in tahiti.oracle.com has the limits in the "Reference" manual
> 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.
120 million rows in quad-PIII 1.2Ghz servers here, with mix of Apple
Xserver RAID and controller-based RAID disks, around 1.5Tb total.
RHAS3-smp. We have three major dbs plus a few more sprinkled
here and there.
>
> The smaller the systems - in terms of server size the better!
Well, can't get much smaller than PC-class systems...
> 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. I'd
Excuse the childishness, but: BWAHAHAHAHA!
> 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.
Well, we run hundreds of table scans/day on our 120million rows...
Last time I looked it was still working fine with 40% spare capacity.
> Version of Oracle is 10g on Solaris. We are probably going to use commodity
> servers SUN V240's in a RAC configuration.
That, I don't know: I've stopped believing a
long time ago Sun has any useful machinery...
--
Cheers
Nuno Souto
in sunny Sydney, Australia
wizo...@yahoo.com.au.nospam
DA Morgan
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
damo...@x.washington.edu
(replace x with u to respond)
Billy
> Is there a practical limit on the size of database tables in Oracle?
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.
Bullshit.
> 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.
>
This is what is typically possible when using partitioning and local
bitmap indexes (typical warehouse fact table design in Oracle).
An actual snippet from a SQL*Plus session:
==
SQL> select count(*) from x25_calls;
COUNT(*)
----------
813696570
Elapsed: 00:00:36.91
==
Note that Oracle does not cache the number of rows in a table
somewhere. It is all dynamic - i.e. the actual row count of committed
rows in the table as this specific point in time.
Why a SELECT COUNT(*) ? - because it illustrates that CBO is not stupid
as a select count usually hits every single "row" in the database to do
the count. (except in this case the CBO uses the local bitmap indexes
instead and PQ the count processes using fast full index scans)
Performance in Oracle is an inverse function of stupidity and
ignorance. Not so nice to say it maybe, but it is a fact. A correctly
configured Oracle instance with a sound logical db design, correctly
implemented physically, with a correctly coded front-end - those are
the 3 essential requirements for a performant and very scalable Oracle
instance.
But then designers know nothing about 3NF these days design the logical
db and architecture, and developers that know jack nothing about Oracle
implement the physical database and proceed to turn on the fan and
shovel manure, calling it writing code.
--
Billy
Ian Turner
Thanks for that.....
I wanted a second opinion first from someone who hopefully had a better
grip/more up to date view on the technology as my Oracle skills are a little
dated - when it comes to the sharp end of development/DBA activities.
I felt sure that I was being told porkies, but needed evidence as well as my
outdated opinion,
thanks
Ian
"Billy" <vsl...@onwe.co.za> wrote in message
news:1120485163.3...@f14g2000cwb.googlegroups.com...
Ian Turner
I was referring to examples. If someone has an example of a 2 billion row
table running on a 64 CPU database cluster my colleague who suggested
150million is going to say it's down to server size. However, if someone is
running personal oracle on a laptop they can't blame that can they.
clearer?
thanks
Ian
"DA Morgan" <damo...@psoug.org> wrote in message
news:1120483687.920448@yasure...
bch...@yahoo.com
We used to have a table of 2.5 billion rows. If you design it right
and give each row a unique id and index it right, there is no problem
with it.
If you are worried about full table scans, then you don't need that
size to kill your system. You should not allow FTS to happen when table
gets that big. If you do, because typically the big tables have very
high insertion rate, you may get snapshot too old anyway.
You can use partitions too. It will cut down your index depth and
reduce query cost.
Paul
"Ian Turner" <itur...@yahoo.com> wrote:
>Thanks for the response... to clarify: "The smaller the systems - in terms
>of server size the better!"
Please don't top-post.
Please trim your replies.
Paul...
--
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.
DA Morgan
> Thanks for the response... to clarify: "The smaller the systems - in terms
> of server size the better!"
>
> I was referring to examples. If someone has an example of a 2 billion row
> table running on a 64 CPU database cluster my colleague who suggested
> 150million is going to say it's down to server size. However, if someone is
> running personal oracle on a laptop they can't blame that can they.
>
> clearer?
>
> thanks
>
> Ian
Why on earth 64 CPUs? You are a hardware salesperson's sweetest dream.
First of all the number of CPUs has nothing to do with the quantity of
data stored. Zip. Zero. Nada. Nyet. Zilch.
Given that 2 billion rows is small by any Oracle standard I would be
thinking of a quad box or 2 node RAC cluster at most until someone told
me there was some reason to believe that anything happening was CPU
intensive. And even these suggestions might be overkill as I've put that
many rows on my ThinkPad portable for demo purposes.
Matthias Hoys
... and management buys extra hardware instead ;-)
Billy
<snipped>
> You can use partitions too. It will cut down your index depth and
> reduce query cost.
Beg to differ. Largish tables? You *MUST* use partitions.
Not partitioning "large" tables is performance suicide as it leaves you
without any room for scalability and without proper data management.
"Large" is of course in the eyes of the beholder. I have partitioned
tables that only contains 1000's of rows - as part of a VPDB. Allows
me to treat each virtual private table as a physical entity via its
associated partition. I have partitioning on very dynamic tables
(millions of transactions per day), with partitioning providing a
hourly sliding window. I have partitioned tables on very large tables.
Without partitioning.. I would have had serious problems all around in
managing the data volumes and providing scalability and performance I
have.
IMO partitioning plays such a major role, that every table that you
design for a physical db implementation, must be considered for
partitioning too as part of the indexing and data management
requirements of that table.
--
Billy
Noons
> IMO partitioning plays such a major role, that every table that you
> design for a physical db implementation, must be considered for
> partitioning too as part of the indexing and data management
> requirements of that table.
>
Unless you're running Standard Oracle and
partitioning is simply not an option...
Bernd Haug
So, how's your .sig these days?
(Not that I don't agree regarding the OP's quoting)
lg, Bernd
--
When emailing me, excuse my annoing spamfilter - it works for me.
Billy
>
> Unless you're running Standard Oracle and
> partitioning is simply not an option...
And don't I know that.. Still have to figure out how I'm going to port
my design and code for ES to SE and run it without making use of
partitioning. Dealing with sliding windows (hourly, daily, monthly) and
partitioning is key to make it work smoothly and tranparently without
any impact on the collection processes. Dynamic DDL creating tables on
the fly.. me no like.
--
Billy
Paul
Bernd Haug <ha...@berndhaug.net> wrote:
>> Please don't top-post.
>> Please trim your replies.
>So, how's your .sig these days?
Just fine, thanks. I'm thinking of including an entire netiquette FAQ
as well as several images of <insert your favourite cutie Russian
tennis player here...>, any thoughts?
>(Not that I don't agree regarding the OP's quoting)
Thanks. As regards my .sig, it gives as much information as anybody
who would want to help me needs, and as much information is requested
from those who seek help from me.
I know that it's a bit above the recommended level, but database
systems are complex things - AFAIC, it's as short as it can be without
being cryptic.
Paul...
>lg, Bernd
Marc Blum
1.000.000.000+ rows in a partitioned (month) table
Ora 8.1.7.4 *sigh*
out-of the-box dual-CPU PC-hardware
RAID 5 *very big sigh*
unpredictable queries
no performance problems...
--
Marc Blum
mailto:blumXXX...@marcblum.de
http://www.marcblum.de