Prev: dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE cannot trace service in job class
Next: Why export is not a good archiving tool
From: galen_boyer on 18 May 2010 19:59
Mladen Gogala <gogala.mladen(a)gmail.com> writes:

> To tell the truth, I don't really like Tom's answer, either. Oracle is a
> relational database. Relational databases should return the requested
> rows, as quickly as possible. Relational databases are not formatting
> tools. Formatting the output is a job for the client tools, not for
> Oracle.

But the question should just be, where is the most performant place to
do this. Who gives a hoot if you add non-relational functional to the
database to get a job done?

--
Galen Boyer

--- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Steve Howard on 18 May 2010 21:58
On May 18, 7:59 pm, galen_bo...(a)yahoo.com wrote:
> But the question should just be, where is the most performant place to
> do this.  Who gives a hoot if you add non-relational functional to the
> database to get a job done?  

Our corporate controller. A developer of ours found this function on
asktom and cut and pasted it into the application. Voila...instant
CPU spike on the server whenever the query is run. If more than one
session runs it? Geez, alarms start firing everywhere.

In our case, it made more sense to have a procedure return a cursor to
the application and then build up the string in java (which is free).
From: steph on 19 May 2010 09:06
On 17 Mai, 22:17, The Magnet <a...(a)unsu.com> wrote:
> Hi,
>
> Ok, I am hoping this is now going to be nuts, but say I have data like
> this:
>
> ID    PRODUCT     SKU
> 1         ABC           123
> 1         DEF           456
> 2         XYZ            000
> 2         MNO          999
> 3         GHI            888
>
> And this is what I would need:
>
> 1,ABC:123,DEF:456
> 2,XYZ:000,MNO:999
> 3,GHI:888
>
> So, records that have a certain criteria would have certain columns
> concatenated.  I'm thinking of putting together a dynamic query and
> then opening a cursor and selecting from it, creating the string as I
> go along.
>
> Any thoughts?  I'm going to go with the dynamic cursor unless someone
> else has a better idea.

Maybe something like this helps:
http://www.sqlsnippets.com/en/topic-11591.html
From: joel garry on 19 May 2010 13:20
On May 18, 6:58 pm, Steve Howard <stevedhow...(a)gmail.com> wrote:
> On May 18, 7:59 pm, galen_bo...(a)yahoo.com wrote:
>
> > But the question should just be, where is the most performant place to
> > do this.  Who gives a hoot if you add non-relational functional to the
> > database to get a job done?  
>
> Our corporate controller.  A developer of ours found this function on
> asktom and cut and pasted it into the application.  Voila...instant
> CPU spike on the server whenever the query is run.  If more than one
> session runs it?  Geez, alarms start firing everywhere.
>
> In our case, it made more sense to have a procedure return a cursor to
> the application and then build up the string in java (which is free).

Our controller asked for an additional filter to a report that causes
a cpu plateau for 15 minutes. The funny thing is, it runs in seconds
without the filter. There are too many variables to predict what will
happen to the OLTP that would be affected by adding an index to fix
this. The SQL comes from a code generator, can't simply hint.

We live in interesting times.

jg
--
@home.com is bogus.
Roger Waters is putting together a Wall tour.
http://www.signonsandiego.com/news/2010/may/19/house-gop-stops-major-science-technology-bill/
From: jefftyzzer on 20 May 2010 12:17
On May 17, 1:17 pm, The Magnet <a...(a)unsu.com> wrote:
> Hi,
>
> Ok, I am hoping this is now going to be nuts, but say I have data like
> this:
>
> ID    PRODUCT     SKU
> 1         ABC           123
> 1         DEF           456
> 2         XYZ            000
> 2         MNO          999
> 3         GHI            888
>
> And this is what I would need:
>
> 1,ABC:123,DEF:456
> 2,XYZ:000,MNO:999
> 3,GHI:888
>
> So, records that have a certain criteria would have certain columns
> concatenated.  I'm thinking of putting together a dynamic query and
> then opening a cursor and selecting from it, creating the string as I
> go along.
>
> Any thoughts?  I'm going to go with the dynamic cursor unless someone
> else has a better idea.

Not sure if you've gotten far on your solution, but you may want to
have a look at a Oct 27, 2006 posting titled "concatenating historical
records" in the comp.databases.ibm-db2 group. In it, I mentioned two
solutions I know of: one uses a recursive CTE (in Oracle you can add
SYS_CONNECT_BY_PATH to this mix--see page 122 of Anthony Molinaro's
_SQL Cookbook_, published by O'Reilly), and the other uses the XMLAGG
function.

--Jeff
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: dbms_monitor.SERV_MOD_ACT_TRACE_ENABLE cannot trace service in job class
Next: Why export is not a good archiving tool