Re: Performace with 500,000 tables

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:atuk44$rbc$1_at_babylon.agtel.net...
> > I'd have to question any design for a database that requires half a
million
> > tables. Point of fact, I can't imagine any circumstances when that
number of
> > tables would be required.
> >
>
> One possible case could be if they are porting an SQL Server app
> that (most certainly) creates a lot of temporary tables during the
> normal course of operation. If the app does, and they expect a lot
> of concurrent users, 500,000 tables doesn't sound all that
> unimaginable... Having, say, 50 temp tables created dynamically
> per session, they only need 10,000 concurrent users to achieve their
> goal (that's not counting permanent tables they will surely have.) :)

I'm probably missing something here but I'm not sure that this would hold up. The likely approaches to port this would be

1. Just dynamically create the tables *and drop them* in the procedures without changing a single bit of the SQL server logic. (I'd expect this to be what was done for the sake of "database independence"). In this case wouldn't you be likely to have a far lower number of tables than the 1/2 million mark *at any one time*. FWIW this approach is exactly that taken by one of our apps (which is why of the ten highest impact sql statements 7 are along the lines of 'insert into COL$.....'. Thus although we generate tables at a rate of approximately a 1/4 million per annum typically we have only between 10-50 of these extra objects at anyone time.
2. Actually learn the Oracle approach and use GTT's, then you'd only get 50 tables and temporary segments at that.

Of course you could come up with a design with permanent objects for each user account that are then used on a temporary basis, but no-one would do this in the real world now would they <VBG>.

The other issue to consider for this sort of design is that the problems are underestimated by considering the 500,000 tables figure. If you assume say 1.5 indices per table then you'd get 1.25 million segments to deal with rather than the 1/2million figure.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************