Wednesday, June 11, 2008

An aggregate function for collection types

Today I stumbled upon a blog entry where the poster needs something like Tom Kyte's stragg function, only with dynamic delimiters. Here's my solution, using a variation of stragg that accumulates data in a collection instead of appending it to a string.

First of all, this is how you use it:
SQL> r
1 with data as (
2 select lpad('x',level,'x') data
3 from dual
4 connect by level <= 5)
5 select v2_pkg.join(v2_agg(data),'-')
6* from data

V2_PKG.JOIN(V2_AGG(DATA),'-')
--------------------------------------------------------------------------------
x-xx-xxx-xxxx-xxxxx

1 Zeile wurde ausgewählt.


The v2_agg function (and it's underlying object type) append strings to a collection. The v2_pkg.join concatenates the elements of it's first argument (the collection) with the second argument in between. See below for the source code. It's part of my everyday toolkit, v2_pkg contains lots more, for example.

The collection type
create type v2_tbl as table of varchar2(4000);

The aggregate object type spec
create or replace type v2_agg_type as object (                                  
elements v2_tbl,
static function
ODCIAggregateInitialize(sctx IN OUT v2_agg_type )
return number,
member function
ODCIAggregateIterate(self IN OUT v2_agg_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN v2_agg_type,
returnValue OUT v2_tbl,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT v2_agg_type,
ctx2 IN v2_agg_type)
return number
);
/

The aggregate function
create or replace function v2_agg(input varchar2)
return v2_tbl
parallel_enable aggregate using v2_agg_type;
/

The utility package spec
create or replace package v2_pkg as
function join(i_tbl in v2_tbl,
i_glue in varchar2 := ',')
return varchar2;
end;
/

The object type body
create or replace type body v2_agg_type  is
static function ODCIAggregateInitialize(sctx IN OUT v2_agg_type)
return number
is
begin
sctx := v2_agg_type( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT v2_agg_type,
value IN varchar2 )
return number
is
begin
if self.elements is null then
self.elements := v2_tbl();
end if;
self.elements.extend;
self.elements(self.elements.count) := value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN v2_agg_type,
returnValue OUT v2_tbl,
flags IN number)
return number
is
begin
returnValue := self.elements;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT v2_agg_type,
ctx2 IN v2_agg_type)
return number
is
begin
if ctx2.elements is not null then
if self.elements is null then
self.elements := v2_tbl();
end if;
for i in 1 .. ctx2.elements.count loop
self.elements.extend;
self.elements(self.elements.count) := ctx2.elements(i);
end loop;
end if;
return ODCIConst.Success;
end;
end;
/

The utility package body
create or replace package body v2_pkg as
function join(i_tbl in v2_tbl,
i_glue in varchar2 := ',')
return varchar2
is
v_str varchar2(32767);
begin
IF i_tbl is not null THEN
FOR i in 1 .. i_tbl.count LOOP
v_str := v_str || i_glue || i_tbl(i);
END LOOP;
END IF;
return substr(v_str,length(i_glue)+1);
end;
end;
/


By the way, Oracle's COLLECT function might be more appropriate than v2_agg for large datasets. This post explains why.

33 comments:

Charles Schultz said...

Very interesting. Thanks for the feedback. I like how your example is much easier (and much more intuitive) to use in "real life"; my example is quite hard on the eyes for everyday use in queries.

What is also interesting is that my example seemed to have less of an impact in terms of recursive calls and consistent gets:

Stragg:
Statistics
----------------------------------------------------------
300 recursive calls
0 db block gets
171 consistent gets
16 physical reads


v2_agg:
Statistics
----------------------------------------------------------
1806 recursive calls
0 db block gets
901 consistent gets
53 physical reads

Is this a peculiarity of the Data Cartridges?

Chris said...

Glad you liked it :-). I don't know which statements caused your statistics, but I also measured a performance penalty when using aggregate functions, although DBMS_Profiler showed only very small timings. I suppose it is caused by the sql/plsql context switches, as Adrian Billington already pointed out (see the last link in my post).

There is, however, a difference between applying a function to cast(multiset) data and using aggregate functions like min, max, stragg or v2_agg.

Charles Schultz said...

Know if the data cartridges support analytics at all? I am not able to find any help in the documentation, only references to aggregation. I would like to order the string aggregation by a 3rd column.

Chris said...

Afaik, the data cartridge does not support sorting directly. You can, however, order the result programmatically, either in ODCIAggregateTerminate or earlier in ODCIAggregateIterate and ODCIAggregateMerge. You will have to concatenate the input and later trim away the part that is only used for ordering the data. You might want to use a session context or global package variable to pass the separation character.

Another way to get the data sorted is to make sure that no parallel aggregation occurs and to present the input in the desired order, eg:

SQL> select v2_agg(ename) from
2 (select ename from emp order by ename desc);

V2_AGG(ENAME)
--------------------------------------------------------------------------------
V2_TBL('WARD', 'TURNER', 'SMITH', 'SCOTT', 'MILLER', 'MARTIN', 'KING', 'JONES',
'JAMES', 'FORD', 'CLARK', 'BLAKE', 'ALLEN', 'ADAMS')


1 Zeile wurde ausgewählt.

I'm not sure if this always works, however.

Charles Schultz said...

I took out parallel_enable, but still get sorting issues:

select DEPTNO, v2_pkg.join(v2_agg(ename)) ename from (select DEPTNO,ename,empno from emp order by empno desc) group by DEPTNO;

DEPTNO
----------
ENAME
--------------------------------------------------------------------------------------------------------------------------------------------
10
MILLER,KING,CLARK

20
FORD,SMITH,ADAMS,SCOTT,JONES

30
JAMES,BLAKE,MARTIN,ALLEN,WARD,TURNER


I am not really an ODCI junkie, but right now that is looking like the best alternative at the moment.

I can get Tom Kyte's STRAGG working as an analytic, but the sql to make it work with custom delimiters is quite a mess (passing it in as a concatenated string and parsing it out at the end).

Chris said...

You can use this:

SQL> col enames for a50
select deptno,
enames
from (
select deptno,
stragg(ename) over (
partition by deptno
order by ename desc) enames,
lead(deptno) over (
partition by deptno
order by ename desc) last_marker
from emp
order by deptno)
13 where last_marker is null;

DEPTNO ENAMES
---------- --------------------------------------------------
10 MILLER,KING,CLARK
20 SMITH,SCOTT,JONES,FORD,ADAMS
30 WARD,TURNER,MARTIN,JAMES,BLAKE,ALLEN

3 Zeilen ausgewählt.

To see how it works, it's best to first run the inner sql. Note that I used stragg in this example because analytic functions that return collections sometimes cause bugs on 10.2.0.3, which I still use. See Metalink Bug ID 5656948.

Charles Schultz said...

FYI - Oracle Support filed bug 7194959 on my behalf. Interesting stuff; will be interesting to see what comes of it.

Chris said...

Good idea. Thanks for the info.

Martin Berger said...

Chris,
in 11gR2 LISTAGG makes this great solution obsolete (I think).
Nevertheless a great introduction to ODCI....
Martin

Chris said...

Martin,

I didn't know I had a great solution, but thanks. However, the ODCI usage was mostly stolen from Tom Kyte's stragg, so the credits belong to him ;-)

On first sight, LISTAGG's syntax (WITHIN GROUP) seems a little bit weird to me. "OVER" is probably reserved for analytic functions, so they needed additional sugar for the pure aggregate version. It's a very welcome addition, however.

Anonymous said...

viagra mexico purchase viagra new viagra viagra suppliers cialis v s viagra viagra rrp australia cost what does viagra do buy cheap viagra online free viagra in the uk cheap generic viagra how to buy viagra viagra cialis generic soft tab viagra viagra free sites computer find

Anonymous said...

[u][b]Xrumer[/b][/u]

[b]Xrumer SEO Professionals

As Xrumer experts, we secure been using [url=http://www.xrumer-seo.com]Xrumer[/url] fitted a sustained leisure conditions and grasp how to harness the enormous power of Xrumer and turn it into a Bills machine.

We also purvey the cheapest prices on the market. Diverse competitors desire expect 2x or square 3x and a a pile of the term 5x what we charge you. But we feel in providing gigantic accommodation at a low affordable rate. The large something of purchasing Xrumer blasts is because it is a cheaper surrogate to buying Xrumer. So we plan to support that contemplating in rebuke and provide you with the cheapest rate possible.

Not only do we be suffering with the unexcelled prices but our turnaround occasion for the treatment of your Xrumer posting is super fast. We intention secure your posting done in the forefront you distinguish it.

We also cater you with a ample log of well-heeled posts on contrasting forums. So that you can notice over the extent of yourself the power of Xrumer and how we be struck by harnessed it to emoluments your site.[/b]


[b]Search Engine Optimization

Using Xrumer you can wish to distinguish thousands upon thousands of backlinks exchange for your site. Scads of the forums that your Location you will be posted on bear great PageRank. Having your join on these sites can really serve build up some top quality endorse links and as a matter of fact as well your Alexa Rating and Google PageRank rating via the roof.

This is making your instal more and more popular. And with this developing in popularity as grammatically as PageRank you can think to see your area really downright high in those Search Engine Results.
Traffic

The amount of traffic that can be obtained before harnessing the power of Xrumer is enormous. You are publishing your plat to tens of thousands of forums. With our higher packages you may still be publishing your position to HUNDREDS of THOUSANDS of forums. Ponder 1 post on a all the rage forum last will and testament usually rig out 1000 or so views, with announce ' 100 of those people visiting your site. Now create tens of thousands of posts on in demand forums all getting 1000 views each. Your freight will go at the end of one's tether with the roof.

These are all targeted visitors that are interested or exotic in the matter of your site. Envision how many sales or leads you can achieve with this great number of targeted visitors. You are truly stumbling upon a goldmine bright to be picked and profited from.

Retain, Transport is Money.
[/b]

GET YOUR TWOPENNY ERUPTION TODAY:


http://www.xrumer-seo.com

Anonymous said...

[B]NZBsRus.com[/B]
Forget Sluggish Downloads Using NZB Downloads You Can Quickly Find HD Movies, Games, MP3s, Applications & Download Them @ Electric Speeds

[URL=http://www.nzbsrus.com][B]NZB Search[/B][/URL]

Anonymous said...

[B]NZBsRus.com[/B]
Escape Crawling Downloads With NZB Downloads You Can Easily Find HD Movies, Console Games, Music, Software and Download Them at Flying Rates

[URL=http://www.nzbsrus.com][B]Newsgroup Search[/B][/URL]

Anonymous said...

Licence to pass the dull with two backs casinos? venture over and beyond this advanced [url=http://www.realcazinoz.com]casino[/url] numero uno and waver online casino games like slots, blackjack, roulette, baccarat and more at www.realcazinoz.com .
you can also go on top of our late-model [url=http://freecasinogames2010.webs.com]casino[/url] have at http://freecasinogames2010.webs.com and away from reliable compressed coin of the responsibility !
another late-model [url=http://www.ttittancasino.com]casino spiele[/url] size up is www.ttittancasino.com , in lieu of of german gamblers, command erstwhile humanitarian online casino bonus.

Anonymous said...

Good dispatch and this enter helped me alot in my college assignement. Thank you on your information.

Anonymous said...

Dear JAck,

For long time I use this free software: [url=http://www.freeflvtomp3converter.com]FLV to MP3 free converter[/url].

FLV to MP3 free converter is a free YouTube, MegaVideo, Dailymotion and similar video sites to MP3 Converter and allows you to convert a video to MP3 file.

This software is fast, free, and requires no signup. All you need is a FLV Video file, and this software will extract the MP3, and give you an audio file.

So you are able to listen to your favorite YouTube tracks on every MP3 player.

You can download it for free at [url=http://www.freeflvtomp3converter.com]www.freeflvtomp3converter.com[/url].

I hope this help you.

Anonymous said...

severnaparksingles [url=http://loveepicentre.com/]star wars ccg singles[/url] free dating services http://loveepicentre.com/ overweight dating site

Anonymous said...

Making money on the internet is easy in the undercover world of [URL=http://www.www.blackhatmoneymaker.com]blackhat make money[/URL], Don’t feel silly if you have no clue about blackhat marketing. Blackhat marketing uses little-known or little-understood ways to build an income online.

Anonymous said...

Genial dispatch and this post helped me alot in my college assignement. Gratefulness you seeking your information.

Anonymous said...

Making money on the internet is easy in the undercover world of [URL=http://www.www.blackhatmoneymaker.com]seo blackhat[/URL], It's not a big surprise if you don't know what blackhat is. Blackhat marketing uses alternative or misunderstood ways to generate an income online.

Anonymous said...

ada diabetes 2007 guidelines [url=http://usadrugstoretoday.com/products/celexa.htm]celexa[/url] mrsa infection in rutherford new jersey schools http://usadrugstoretoday.com/products/plendil.htm mid state medical center er http://usadrugstoretoday.com/catalogue/m.htm
zoloft menstrual cycyle changes [url=http://usadrugstoretoday.com/products/horny-goat-weed.htm]horny goat weed[/url] federal law pertaining to dismissal of positive drug test [url=http://usadrugstoretoday.com/products/brand-cialis.htm]hot bikini model breast augmentation photo[/url]

Anonymous said...

hunderton medical [url=http://usadrugstoretoday.com/categories/gums.htm]gums[/url] specialty retail health clinics http://usadrugstoretoday.com/products/medrol.htm diet renal failure http://usadrugstoretoday.com/products/cialis-professional.htm
identify a statistic about breast cancer [url=http://usadrugstoretoday.com/products/brand-viagra.htm]brand viagra[/url] sexual enhancement product reviews [url=http://usadrugstoretoday.com/products/nolvadex.htm]otc zantac[/url]

Anonymous said...

http://www.pornvideoonline.info

www.pornvideodownload.info

www.pornvideotorrent.info

http://www.gaypornonline.info/

http://www.teenpornonline.info/

www.freepornvideosonline.info

www.bestpornvideo.info

[url=http://www.pornvideoonline.info]Porn video online[/url]
[url=http://www.pornvideodownload.info]Porn video download[/url]
[url=http://www.pornvideotorrent.info]Porn video torrent[/url]
[url=http://www.gaypornonline.info]Gay porn online[/url]
[url=http://www.teenpornonline.info]Teen porn online[/url]
[url=http://www.freepornvideosonline.info]Free porn videos online[/url]
[url=http://www.bestpornvideo.info]Best porn video[/url]

Anonymous said...

santo veneto anejo tequila with heart on bottle [url=http://usadrugstoretoday.com/products/cordarone.htm]cordarone[/url] medical estuaries http://usadrugstoretoday.com/products/cystone.htm blood and magic http://usadrugstoretoday.com/products/menopause-gum.htm
implant dental surgery needs [url=http://usadrugstoretoday.com/products/lanoxin.htm]lanoxin[/url] blood and gutz productions [url=http://usadrugstoretoday.com/products/cipro.htm]beystone health and rehab[/url]

Anonymous said...

northwest vein center [url=http://usadrugstoretoday.com/categories/arthrite.htm]arthrite[/url] canine rage syndrome http://usadrugstoretoday.com/products/reminyl.htm reactive affective disorder http://usadrugstoretoday.com/products/toprol-xl.htm
new jersey breast enhancement [url=http://usadrugstoretoday.com/products/copegus.htm]copegus[/url] nche zama heart surgeon [url=http://usadrugstoretoday.com/products/procardia.htm]hand of blood[/url]

Anonymous said...

http://www.crownempire.com/forum/viewtopic.php?f=2&t=137029 http://bb.sdoutdoors.net/viewtopic.php?f=11&t=142180 http://www.testservertje.nl/isitnet/techform/viewtopic.php?p=5613#5613 http://asumi-angel.moo.jp/mainbbs/apeboard_plus.cgi?command=read_message&msgnum=10
http://www.swed.co.ly/bbs//zboard.php?id=guest&page=1&page_num=20&select_arrange=headnum&desc=&sn=off&ss=on&sc=on&keyword=&no=631922&category=1 http://site.sensationalcity.com/Polydeco-Forums/viewtopic.php?p=88201#88201 http://forum.eficoach.com/viewtopic.php?f=5&t=91346 http://djduro.net/forum/index.php?topic=200281.new#new
http://kemtech.co.kr/bbs/board/content.asp?tb=inno_6&page=1&num=3 http://naijamarketsquare.com/index.php?topic=92710.new#new http://www.aaguild.net/phpBB3/viewtopic.php?f=17&t=112299

Anonymous said...

kinds of exstacy pills [url=http://usadrugstoretoday.com/products/lukol.htm]lukol[/url] stress ossidativo medicina http://usadrugstoretoday.com/products/serophene.htm seigal cookie thin diet http://usadrugstoretoday.com/products/zocor.htm
bloody heart [url=http://usadrugstoretoday.com/catalogue/1.htm]Online Drugstore[/url] average length width of penis 13 year old [url=http://usadrugstoretoday.com/products/astelin.htm]hypertension and diuretic[/url]

Anonymous said...

http://healthboard.in/clonidine/recent-trials-on-clonidine
[url=http://healthboard.in/captopril]drug free worksheets[/url] cheap generic viagra [url=http://healthboard.in/cytoxan/can-prednisone-and-cytoxan-cause-seizures]can prednisone and cytoxan cause seizures[/url]
drug diversion program recidivism http://healthboard.in/bisoprolol/atenolol-bisoprolol-comparison
[url=http://healthboard.in/conjugated-linoleic-acid/c9-t11-conjugated-linoleic-acid-positives]body packing drugs photos[/url] drugs mood elevators [url=http://healthboard.in/crestor/when-crestor-doesnt-work]when crestor doesnt work[/url]
presciption drug identification http://healthboard.in/depressive/significant-depressive-symptoms
[url=http://healthboard.in/dexamethasone/the-effects-of-dexamethasone-on-quality-of-life]the hive drugs[/url] laws for maternal drug use [url=http://healthboard.in/clonidine/what-is-clonidine-for-and-what-does-it-look-like]what is clonidine for and what does it look like[/url] drugs evista [url=http://healthboard.in/decadron/decadron-withdrawl]decadron withdrawl[/url]

Anonymous said...

http://online-health.in/benadryl/benadryl-ingredient
[url=http://online-health.in/beconase/side-effects-of-beconase-nasal-spray]drug usage in muscogee county georgia[/url] cialis viagra levitra effects [url=http://online-health.in/atacand/atacand-and-cozaar]atacand and cozaar[/url]
mental health message boards http://online-health.in/benazepril/benazepril-generic
[url=http://online-health.in/atrovent/generic-for-atrovent]cialis dysfunction erectile levitra viagra[/url] drugs in sport is morally wrong [url=http://online-health.in/beconase/beconase-aq-glaxosmithkline]beconase aq glaxosmithkline[/url]
university of illinois college of medicine peoria illinois http://online-health.in/beclomethasone
[url=http://online-health.in/azathioprine/prometheus-azathioprine]spider webs and drugs[/url] viagra zyban [url=http://online-health.in/atarax/atarax-for-false-labor-contractions]atarax for false labor contractions[/url] miracle burn interaction with prescription drugs [url=http://online-health.in/azithromycin/penicillin-allergy-azithromycin]penicillin allergy azithromycin[/url]

Anonymous said...

http://meen.in/cholesterol/high-cholesterol-and-drinking-soda-pop
[url=http://meen.in/erections/sensible-erections]levitra experiences[/url] bladder health health food store [url=http://meen.in/fluoxetine/snorting-fluoxetine]snorting fluoxetine[/url]
crystal myth drugs http://meen.in/clarithromycin/clarithromycin-vet
[url=http://meen.in/cilostazol/information-on-pletal-and-cilostazol-medications]viagra impotent[/url] cialis professional review [url=http://meen.in/cefuroxime/comparison-of-superiority-of-cefuroxime-with-antibiotics]comparison of superiority of cefuroxime with antibiotics[/url]
investigational drugs for cancer http://meen.in/carbohydrates/diabetes-calories-carbohydrates
[url=http://meen.in/ceftin/ceftin-and-yaz]avoding cocaine in a drug test[/url] diflucan prescription drug [url=http://meen.in/clomiphene/clomiphene-dtd]clomiphene dtd[/url] on line viagra [url=http://meen.in/celebrex/celebrex-side-effect]celebrex side effect[/url]

Anonymous said...

http://alwayshealth.in/article-syndrome/multiple-down-syndrome-children-in-same-family
[url=http://alwayshealth.in/heart/image-of-the-heart-with-labels]rexall pharmacy[/url] riverside ca awards drug testing bid [url=http://alwayshealth.in/natural-health/priority-health-of-michiga]priority health of michiga[/url]
cvs employment pharmacy http://alwayshealth.in/potency/act-and-potency-thomas-aquinas-in-connection-with-world
[url=http://alwayshealth.in/pravachol/eat-grapefruit-in-the-morning-and-take-pravachol-at-night]murders due to drugs[/url] my dad is a drug addict [url=http://alwayshealth.in/women-health/diabetic-health-insurance]diabetic health insurance[/url]
low cost levitra http://alwayshealth.in/smoking/stop-smoking-clinic-sc
[url=http://alwayshealth.in/pharmacy/certified-pharmacy-technician-pay-scale]mexico pharmacy suboxone[/url] new york senate health care budget [url=http://alwayshealth.in/pletal/pletal-brand]pletal brand[/url] norris pharmacy pine grove wv [url=http://alwayshealth.in/oxybutynin/oxybutynin-aricept]oxybutynin aricept[/url]

Anonymous said...

http://poow.in/viagra/ambien-gift-viagra
[url=http://poow.in/lamisil/affect-lamisil-side]ave maria pharmacy[/url] roadside drug test how long after detectable [url=http://poow.in/levothroid/levothroid-generic-name]levothroid generic name[/url]
mcfarland pharmacy http://poow.in/melatonin/melatonin-sleep
[url=http://poow.in/xanax]cialis uk chemist order[/url] drug and well tolerated [url=http://poow.in/soma/soma-1]soma 1[/url]
fertilitiy drugs and multiple pregnancies http://poow.in/risperdal/risperdal-decrease-symptoms-in-disruptive-behavior-disorder
[url=http://poow.in/revia/ron-revia]compounding pharmacy laws[/url] online pharmacy visa hcg [url=http://poow.in/levitra/levitracanada-levitra]levitracanada levitra[/url] does cialis work [url=http://poow.in/mefenamic/mefenamic-acid-benefits]mefenamic acid benefits[/url]