Re: Global Temp Tables - lots of redo in 9i, why? how to fix?

Apart from the non-interference between private sets of data, you should also bear in mind that the 'one-line difference' is very expensive.

How do you keep 'scratch' data invisible and/or get rid of it. You either rollback, or delete it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the implicit "truncate on commit / exit" effect to do the same thing)

GTTs do have their place - after all, Oracle uses them internally in several parts of their code - and in many ways a GTT is simply a 'cleaner' way for Oracle (and users) to allow for optimal instantiation of an in-line view which otherwise could not be optimised. Of course, the follow-on to that comment is this:

If you pre-empt Oracle's internal mechanisms for optimising the SQL, your application doesn't get faster as the optimizer evolves. If you aim to stick with pure SQL, each release of the optimizer may have improved ways of handling that SQL - so one argument for avoiding GTTs is actually very similar to the argument for avoiding HINTs.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____UK_______April 8th
____UK_______April 22nd

____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"DA Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3E6148AF.C729043F_at_exesolutions.com...


> Rene Nyffenegger wrote:

>

> >

> > Althoug Session A has already used 1, 2 and 3 as values for the

primary key,


> > session B can happily use these values as well, without getting

blocked until


> > A either commits or rolls back.

> >

> > Rene Nyffenegger

> >

> >

> > --

> >   no sig today

>

> Nice. I hadn't considered that.

>

> Thanks.

>

> Daniel Morgan

>