Re: Stats on Huge Partitioned Table and ORA-01555

From: MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
Date: Wed, 27 May 2015 14:53:44 -0400
Message-ID: <CAAaXtLBuznVFB6vrG8MCsFQb0KrGz_=2msOWf6B+t6im-TYysA_at_mail.gmail.com>

How to handle large/aging data depends completely on your application and your business needs.

What Cherif and Mladen are probably suggesting is that a "pure" OLTP system usually has little or no need for historical data.

Consider a flight reservation system as an example. It certainly needs to know whether I have booked a flight from Chicago to Austin for next Friday, but is there a reason that it would need to know that I booked a similar flight three years ago? Most probably not.

An airline might be intensely interested in my historical behavior, but that historical data does not belong in the flight reservations database. It might belong in a data warehouse, though, or a reporting database. Or maybe in the customer billing system (but probably only as far back in time as the business is willing to offer me a refund.)

Accumulating large bodies of data that you never access can be a surprising burden on your database operations. You have just found a good example -- eventually, the unaccessed "historical" data might become so large that you can no longer keep table statistics current without going to extreme measures. If your application is really an "OLTP" application and most of your 7TB table is historical, then perhaps it is time to review your data's lifecycle, and see whether there may be a better way to manage it.

On Wed, May 27, 2015 at 1:59 PM, Michael Cunningham < napacunningham_at_gmail.com> wrote:

> All the suggestions have been great and I obviously have some reading to
> do. Thanks for all the input.
>
> A question for Cherif and Mladen about "Such table has no place in an
> OLTP system". I will bring this up to the team, but, out of curiosity, how
> would you archive the table? Would you just move the partitions to a
> different table?
>
> Thanks again,
> Michael
>
> On Wed, May 27, 2015 at 7:48 AM, Mladen Gogala <
> dmarc-noreply_at_freelists.org> wrote:
>
>> On 05/27/2015 05:33 AM, Cherif Ben Henda wrote:
>>
>> Hi Michael,
>>
>> Why having "Table has 7.1 billion+ rows, 503 partitions, 3TB" ?
>>
>> This is a great question. Such table has no place in an OLTP system. That
>> OLTP system is, in my humble opinion, in dire need of an archiving solution.
>>
>> --
>> Mladen Gogala
>> Oracle DBAhttp://mgogala.freehostia.com
>>
>>
>
>
> --
> Michael Cunningham
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 27 2015 - 20:53:44 CEST