Re: UNDO Tablespace, and how NOT to use

On Aug 6, 12:33 am, euan.gar..._at_gmail.com wrote:
> > Do you mean a global temporary table or an imitation of aSQL Server
> > trick that kills performance and scalability?
>
> > You mean aSQL Servertype temporary ... stop it. This is Oracle notSQL Serverand what you are doing is both unnecessary and bad practice.
> > Likely you need no intermediary table at all but should you need one
> > then you should use a global temporary table which is a permanent table
> > in which the data is temporary.
>
> > To stop duplicatingSQL Servermethodology get copies of Tom Kyte's
> > books and read the sections that relate to temporary tables and general
> > concepts and architecture.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> I have no doubt that Daniels recomendations around which oracle books
> to read and which tecniques to employ are correct, his comments re SQL
> Server are not.
>
> The current SQL Server lock and transaction managers are new code and
> have been for several releases, they have nothing to do with Sybase
> never mind Ingres. Daniel appears to forget that SQL Server 2005
> includes support for 2 snapshot based isolation levels that address
> many of his out of date points about SQL Servers lock escalation
> policies. And finally the VAST majority of customers I have seen using
> temp tables in almost 10 years of working with SQL Server have nothing
> to do with locking or transaction isolation models in terms of
> reasoning. And of course when properly used in SQL Server(just as in
> Oracle) use of these objects does not automatically equal performance
> or scale problems. A badly designed and/or implemented database
> independent of which platform it is, is the cause of poor performance
> and scale, not the platform itself.

Well, as someone who works with db independent code every day, I have to point out that lots of code has been around since before SQL Server 2005, and hasn't been re-written, and the new code being written has to conform to the old code. The vast majority of the code _I_ work with uses a lowest common denominator locking and concurrency, even though the independent part allows one to select an Oracle style mechanism, since the application packages sell more to SQL Server shops.

The funny thing is, even using the silly mechanism, it still scales better on Oracle. As long as I have the choice, I still work on Oracle/unix.

>
> I will happily agree with Daniel that SQL Server customers use more
> temp tables that Oracle customers and that the usage models do not
> translate well

I've gotten lots of work where the temp tables are abused by programmers who don't understand what is happening under the covers (understandable with the extra layers of abstraction involved), and I magically make it work better. I think it is a good thing to be able to manipulate things totally in memory without setting off concurency mechanisms when dealing with the magnitude of data you find in, say, a sales order. I also think it is too much to ask to expect people to be able to deal with two conflicting concurrency/consistency models at the same time, so for Daniel to say something should never ever be done in Oracle and to blast old stuff in SQL server is actually laudable and timely.

jg

--
@home.com is bogus.
Dadgum hackers!  http://www.signonsandiego.com/uniontrib/20070806/news_mz1b6steel.html