Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Big Tables? Real Life Examples.....

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

From: Ian Turner <iturner1_at_yahoo.com>
Date: Mon, 4 Jul 2005 16:47:22 +0100
Message-ID: <dablk0$gi2$1@lore.csc.com>


Billy,

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" <vslabs_at_onwe.co.za> wrote in message news:1120485163.374901.136420_at_f14g2000cwb.googlegroups.com...
> Ian Turner wrote:
>
>> 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
>
Received on Mon Jul 04 2005 - 10:47:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US