Re: Oracle 8i Database with 18,000 Partitions

Jonathan,
Thank you for the prompt reply. I am in total agreement with your (and the others who have replied) assessment. While I am not an Oracle DBA, I am an architect with about 15 years experience. My gut feeling was "RUN!!!!". However, I did need to get some input from people who regularly make the impossible work in the Oracle arena. Again, thanks.

As for the "restore", from what I understand, you have the ability with Oracle to restore a backup of a partition back to that partition (or to another). The design, as pushed by the vendor, was to restore the data for the customer from the earlier backup as needed (due to unintended changes, incorrect data, etc). Sorry I can't go into more details on the particulars. Can you shed any light on this "restore" mechanism? Is there any way that it could handle this volume of actions? Or, do I simply misunderstand how or what this functionality is?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<aqorlq$ev0$1$8300dec7_at_news.demon.co.uk>...
> If the idea is to have a table of around 600,000,000 rows,
> split by customer into about 15,000 partitions, then at
> first sight, this looks like an accident waiting to happen.
>
> Key issues
> how are you going to define it - range or hash ?
>
> are you planning to index it - that's another 15,000 partitions
> per index to worry about.
>
> are all queries guaranteed to be structured to do
> maximum partition elimination ? If not, then 15,000
> searches to find a small amount of data will produce
> a massive processing overhead.
>
> One important point - can you clarify what you mean
> by 'restored'.
>
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____England______November 12/14
> ____USA__________November 19/21 (Dallas)
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> Tom Glover wrote in message
> <7a7ecd29.0211110955.456e6112_at_posting.google.com>...
> >First of all, I am not an Oracle DBA. Secondly, this DB design is
> not
> >my idea - so bashing the messager is not required (I'm just the poor
> >sap that has to make the application around the database work!).
> >
> >I have a third party vendor who is pushing a customer database design
> >that will contain over 15,000 partitions. Basically, each customer
> >will have a partition with approximately 30,000 - 50,000 records.
> The
> >partitions can be restored on a very regular basis (100 - 200
> >customers per day possible). So, I have a few questions as to the
> >feasibility of this design:
> >1. What are the performance impacts of this many partitions? I have
> >read numerous discussions about as many as 4,000 partitions and the
> >general consensus was: you can do it, but you better be very,very
> good
> >and careful.
> >2. Is Oracle even designed to handle this volume of restores
> (100-200
> >per day)? What are the pitfalls (maintainance???) to supporting this
> >many restores.
> >3. If the answers to the questions above lead to the point that this
> >system can be made to work, what level of DBA do I need to 1)Create
> >the database and stored procedures and 2) Maintain this monster of a
> >system once it is turned over.
> >
> >Any help is greatly appreciated.
Received on Mon Nov 11 2002 - 20:32:59 CST