Re: calling sql script from pl/sql

It doesn't have anything to do with dogma. In Oracle I have never needed temp tables, sure I could have used them but found views more efficient - less IO, less latch contention, less CPU usage. That doesn't strike me as dogma. More an issue of physics. Ignoring physics would make it dogma. That said, in other rdbmss I have seen how they work and there temp tables made sense (only way to get around certain locking problems, and views were not efficient), but we are talking about Oracle here not other rdbmss in this case. Contrary to some points of view a database is not a database - there are different vendors and they implemented things differently. Therefore, It is best to meet the requirements of the problem - reports gotta run fast - by taking advantage of that particular database's strengths and not forcing another database's practices -which are effective there - on the one you are currently running on.

Executing adhoc DDL is very CPU intensive and limits system concurrency. If you really feel you just gotta have them then look up global temporary tables in the manual. It will make things run faster for you than dynamically creating tables ad-hoc.

I am kind of confused, if you treat the DBA as a professional why are you asking us and not talking to him or her? I don't know your DBA, but my suggestion was a suggestion not an accusation. Is there no DBA or are they on vacation? In our group we do code reviews and in this life I am the acting DBA - most of the past lives I have done development - but our team is pretty open - and small - and so we try to use each other's expertise. The code reviews and design reviews can really help tighten up code (performance is part of the job not something we bolt on afterwards) and keep everyone up to date as to how things work etc.

Jim

"Radu Lascae" <r.nospam.lascae_at_wanadoo.nl> wrote in message news:Pggw8.217$r7.13016_at_castor.casema.net...
> Temporary tables are tables - funny I should need to mention it.
>
> This is not an issue of profesionalism, I've never treated DBA's other
then
> as professionals, it's a dificult profession, I'm aware of it. They've
never
> treated me otherwise either.
>
> The issue is of dogmatics. I have my own. I wonder if this is a particular
> DBA dogma. The question stays unanswered: why?
>
> Regards,
> Radu
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:5yfw8.90947$CH1.80375_at_sccrnsc02...
> > I think the best approach is to work with them and state what you are
> trying
> > to accomplish - need to run this report, but the performance is not up
to
> > snuff. Then ask them how we could improve it. Ask them for their
> expertise
> > and input. You are then treating them as a professional, an expert in
> their
> > area and they will probably have a whole different attitude. Yes,
Sybrand
> > is correct. I can think of two ways - a view or a global temporary
table.
> > I suspect the problem lies in how the data is getting retrieved and some
> > analysis would come up with a better solution.
> > Jim
> > "Radu Lascae" <r.nospam.lascae_at_wanadoo.nl> wrote in message
> > news:fOew8.176$r7.11119_at_castor.casema.net...
> > > With due respect: why?
> > >
> > > I'm not a DBA. I can think of several scenario's in BIS applications
> where
> > > I'd improve performance of certain repports by creating tables on the
> fly.
> > > Some of these reports can't even be created in straight SQL.
> > >
> > > Am I bound to outrage the DBA's with whom I'm working?
> > >
> > > Regards,
> > > Radu
> > > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > > news:uc21ba11ld1076_at_corp.supernews.com...
> > > >
> > > [...]
> > > > Creating tables on the fly however must be considered very bad
> practice.
> > > >
> > > > Regards
> > > >
> > > > --
> > > > Sybrand Bakker
> > > > Senior Oracle DBA
> > > >
> > > > to reply remove '-verwijderdit' from my e-mail address
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Sat Apr 20 2002 - 11:40:29 CDT