Re: Alternative to temporary tables

<sueschoch_at_gmail.com> wrote in message
news:1160061252.439224.276180_at_c28g2000cwb.googlegroups.com...
> The situation:
> I currently have stored procedures that use global temp tables to hold
> differing type of query criteria that is passed in to the procedure.
> This enables me to easily do a join of these temp tables with various
> permanent tables to find data a user is interested in. However, I am
> running oracle 9.2 and am running into the unresolved bug in which a
> huge amount of redo data is generated when inserting into temp tables.
> This has caused havoc in our database with lots of other applications
> failing due to no rollback space.
>
> I know I could parse out the criteria and create some complicated where
> clauses to include the criteria directly but I think there must be an
> easier way. Can anyone suggest a replacement for temporary tables?
> I've not used many of the pl/sql collections yet.
>
> I am not willing to upgrade to 10 just to solve this problem so any
> advice will be greatly appreciated.
>

That bug was fixed in 9.2.0.

But if you don't want to upgrade to 9.2.0.6 you could consider creating an object scalar and table type matching the GTT structure. Then create a pipelined function to return the data you want.

    select * from table(pipelined_function) then behaves as a table with no storage
behind it.

Obviously there are other overheads in latching and CPU - but if your critical bottleneck is redo then this bypasses the issue.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html