Re: Slight "I have some string, how lng it it, BTW, it's blue" question

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:PuOdneEYqcI_bBvZnZ2dnUVZ_radnZ2d_at_comcast.com...
>
> "Brian Peasland" <oracle_dba_at_nospam.peasland.net> wrote in message
> news:J0HwM2.Lx3_at_igsrsparc2.er.usgs.gov...
> : Basically, the rule of thumb I use is if it can be done with SQL
> : statements, do it in SQL. If not, then use PL/SQL.
> :
> : If you use a view and query from the view, you are using a SQL solution.
> : Using cursors to do your join will usually be slower than doing it with
> : a well-formed SQL statement.
>
> can you expand on what you mean by 'Using cursors to do your join'? are
you
> really indicating using more than one SQL statement and somehow performing
> the join programmatically? after all a 'join' is by definition down within
a
> SQL statement which is run within a cursor -- so what am i missing here?
>

IMHO - nothing
Hard to explain in some ways - but basically - the standard approaxh seems to be - i you use - fot example - 5 tables - you set up 5 cursors - one for each table as iterate through them lke you'd do in IDMS COBOL - rather thyan use views
Personally - I'd have shot somneone for doing that in my previous place for doing that.
But been told that is the standard here ...... (?!)

Hardware salesweasles must love us!
> :
> : Cursors are not the fastest mechanism if you are using PL/SQL. Array
> : processing is much faster. Daniel Morgan has posted some examples here:
> :
> : http://www.psoug.org/reference/bulk_collect.html
> :
>
> IIRC correctly from a Tom Kyte presentation, cursors (perhaps just for
loop
> implicit cursors) in later versions of Oracle are automatcially optimized
to
> due array fetches under the covers

this is a V8 though, and call me a cynic - but I do not believe the optimiser has been written that can optinise a multi table join the way a competent DBA can use views (plus a sp if required) compared to trying to do the same thing using cursors.
OK - I don't know the Oracle engine very well now - but everything I've used so far - it's a killer.
I mean, shit, OK one of PL/SQL's big selling pointsx is that it's so flexible that a COBOL programmer can write COBOL in PL/SQL. (ish) Sureley it'd be better to teach them ANSI 92 and set theory? Neither are terribly hard

• so the principle is pretty much the
  > same (assuming multiple rows are returned) but the way to get there may
  > differ a bit per Oracle version
  >
  > ++ mcs
  >
  >