Re: Global temporary tables v pl/sql tables

A GTT generates no redo on its data/index blocks as it should never needs to be recovered - in principle temporary data can always be recreated on demand. This doesn't stop the blocks being written to disc.

Bear in mind that GTTs still generate UNDO (as you may want to rollback, or rollback to savepoint, when using GTTs - and undo generates redo, so using GTTs does generate redo indirectly.

Optimum use of GTTs is

    insert
    select
    truncate

Avoid usage like

    insert small row
    update
    update
    update
    delete
    update
    select
    truncate

All the updates and deletes erode the I/O benefit of using a GTT - and if you need this type of processing you may be better off with pl/sql tables and casting pl/sql tables (preferably object-type tables) to relational tables when needed. It's more CPU and memory intensive but less I/O intensive.

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

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Ryan wrote in message ...


>What about performance comparisons? I know that in some cases pl/sql tables

>are useful if you need to do a series of joins and /or sorts you can load

>one table into memory by putting it in a pl/sql table.

>

>How is the data in a GTT stored? I would assume there is no redo and if

>there is no redo, DBWR does not write it to the data file. So is it stored

>in memory and in the temporary tablespace?

>

>