Re: ORA-04031 with Collections and SGA Settings - Part II

"Gints Plivna" <gints.plivna_at_gmail.com> wrote in message news:1151915160.553639.293520_at_75g2000cwc.googlegroups.com...
> Dereck L. Dietz wrote:
> > I've come to the conclusion that using collections for what I was trying
to
> > do wasn't workable myself. I have never used the global temporary
tables -
> > compared to a normal table, are they any quicker/efficient?
> Description of temporary tables
>

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.h tm.
> An example and some more info
>

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.h tm.
> >
> > I'm trying to rework an inefficient process as efficiently as I can (and
get
> > away from the way the code uses one table to create another table which
is
> > used to create yet another table and so on) and I'm worried that I'm
trying
> > to do this with a database with an inefficient memory setup as well.
> Smells like ported code from MSSQL, or at least developer with MSSQL
> background ;)
> Creating tables on the fly is very bad idea at least in Oracle.
>
> Gints Plivna
> http://www.gplivna.eu/
>

I don't think it was MSSQL but from what I'm told it did originate in another database and was brought into Oracle. It's not done correctly at all - one process I'm working on originally had 98 tables and I've studied it and have it down to 28 input and final output tables. All the rest were intermediate tables created simply to be used to create other tables. Instead of a database it seems more like an old IBM flat file system.

I'm trying to make this - and other processes - more efficient, quicker and more like how Oracle should be written. Unfortunately with the condition of both the code and the database (as indicated by my memory questions I've posted) I don't think I can do it all in one step. I'm going to have to take small steps to prove to those in charge that the changes I propose are both correct and will work. Received on Tue Jul 04 2006 - 22:04:13 CDT