In article <36CD836F.88CA7BF8_at_pca.state.mn.us>,
Kent Eilers <kent.eilers_at_pca.state.mn.us> wrote:
> Having worked on SQL-Server's Transact-SQL and then moving to PL/SQL I've
> encountered similar moments of disbelief.
>
> PL/SQL is a very powerful language. I love its error-handling capabilities
> and its innate structure-building tendencies. But it has limitations. I
> suspect you will discover (if not already) the severve limitations Oracle
> PL/SQL imposes on temp tables. When you look at the functionality for temp
> tables you will discover you cannot use SQL against these tables! This
> really annoys me. Being forced to loop through embedded cursor after
> embedded cursor and write all the 'FETCH INTO ...' syntax adds untold hours
> to my code creation and debugging efforts. Like you I've learned to avoid
> cursors whenever possible due to perforamance reasons but with Oracle you
> have no choice. The whole purpose of SQL was set operations! Instead I'm
> (finding myself) dealing with each individual record via these fetch
> statements..
>
> I've been working with PL/SQL for 4 months now. I currently have a
> distinctly mixed attitude about it. I've worked with SQL-Server and
> Informix backends before this. I hope after another four months I will
> discover more 'attractions' then 'turn offs.'
>
> Jonathan Tew wrote:
>
> > Wow, thanks for posting a solution to the problem. That strikes we as
> > really complex code though. I've been told that cursors are very
> > inefficent things and should be used as little as possible. Under MS
> > SQL Server often a cursor will blow up in a stored proc causing
> > problems. Does Oracle have such problems with cursors and would this be
> > a bad thing performance wise? I'm under the impression that the kinds
> > of things that I'm trying to do are simple and common... why hasn't
> > Oracle taken care of this problem with an exists() construct?
> >
> > Thanks,
> > Jonathan Tew
>
>
Several remarks:
- Use of temporary tables is a sign of a bad application design. In order for
the tmporary table to be accessible by SQL, it is necessary for some
kind of dictionary operations to be performed. Disk space should also
be allocated because of the possibility that the table will outgrow the
available memory space. Dictionary operation is an overhead by which the
RDBMS maintains itself instead of performing the user work. Therefore,
dictionary operations are not considered good for application performance.
Dynamic space allocation is THE SLOWEST THING POSSIBLE and any performance
aware application designer should avoid it like a plague. Now multiply
that by the number of sessions using the application and you'll see why
the applications using temporary tables are crawling and not running.
It applies to all databases using centralized data dictionary, which
means all major databases.
- There is Oracle supplied package, DBMS_SQL which can execute DDL commands
from within PL/SQL. Temporary table would be roughly equivalent to
creating the table (with SESSIONID in the table name) and dropping
it after you're done. It would also be slow, due to the reasons specified
above, but it's definitely doable.
- You can always create a permanent table with SESSIONID as a column and
then do inserts and selects. It would be much faster then anything that
uses temporary tables. You can invoke a cleanup procedure when you're
done with the table.
- You can revisit your application design and use joins to retrieve rows.
Well written join will always be much faster then a temporary table or
it's emulation.
- You can reverse engineer and modify your application using a CASE tool,
like Designer 2k, PowerDesigner, Knowledge*Ware or Erwin. Those tools
will create procedures and triggers without using temporary tables.
Mladen Gogala
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Feb 19 1999 - 13:44:10 CST
