Получение ид строк в запросе с группировкой

OzerovL
Дата: 14.03.2011 12:19:53
Добрый день.
Как получить иденитификаторы строк, составивших значения аггрегатов в запросе с группировкой? Аггрегатня ф-ция, возвращающая в поле выборки таблицу работает очень медленно (6000 строк - 12 сек). Аггреатная ф-ция с clobo'м даже не очень смешно, вот.
Perepil
Дата: 14.03.2011 12:30:54
То есть нужно и сумму 100 получить и список из 10-ти записей по 10, из которых эти самые 100 сложились?
Что-то мне это сильно напоминает использование аналитической функции

select id, gruppa, sum(amount) over(partition by gruppa) sum_amount from my_table
-2-
Дата: 14.03.2011 12:38:21
OzerovL,

collect
OzerovL
Дата: 14.03.2011 12:42:44
Perepil
Спасибо, попробую.
-2-
collect? - е сли можно - немного подробнее
orawish
Дата: 14.03.2011 12:43:21
OzerovL,

над дизайном подумайте.
имхо, первопричина проблемы в том, что вы пытаетесь одним запросом сделать
две разные (в половом смысле) вещи
OzerovL
Дата: 14.03.2011 14:32:22
-2-
Да, спасибо, collect - решение, но у клиента к сожалению 9-ка.
dbms_photoshop
Дата: 14.03.2011 14:40:07
OzerovL
Аггрегатня ф-ция, возвращающая в поле выборки таблицу работает очень медленно (6000 строк - 12 сек).
Либо ложь либо криво написана.
User-Defined Aggregate Functions работают намного шустрее.
OzerovL
Дата: 14.03.2011 15:38:13
Вроде все просто. Проблемы возникают с быстродействием при размере возвращаемой таблицы > 6000 элементов а при 50000 - ошибка: operand value exceeds system limits

CREATE TYPE number38_t AS TABLE OF NUMBER(38);

CREATE OR REPLACE TYPE agg_numarray
AS OBJECT (

n_agg number38_T,

static function ODCIAggregateInitialize(sctx in out agg_numarray)
return number,

member function ODCIAggregateIterate (self in out agg_numarray,
value in NUMBER )
return number,

member function ODCIAggregateTerminate (self in agg_numarray,
return_value out number38_T,
flags in number )
return number,

member function ODCIAggregateMerge (self in out agg_numarray,
ctx2 in agg_numarray)
return number
)
/
CREATE OR REPLACE TYPE BODY agg_numarray
IS
static function ODCIAggregateInitialize(sctx in out agg_numarray)
return number is
begin
sctx := agg_numarray(null);
sctx.n_agg := number38_T();
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self in out agg_numarray, value in number)
return number is
BEGIN
n_agg.extend(1);
n_agg(n_agg.last) := value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self in agg_numarray, return_value out number38_T, flags in number)
return number is
begin
return_value := n_agg;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self in out agg_numarray, ctx2 in agg_numarray)
return number IS
begin
return ODCIConst.Success;
end;

END;
/
dbms_photoshop
Дата: 14.03.2011 16:38:29
OzerovL,
На моих дровах конструкция:
SELECT MOD(id, 5) x, f_agg(to_char(id)) y
  FROM (SELECT rownum id FROM dual CONNECT BY LEVEL <= 10000)
 GROUP BY MOD(id, 5);
Работает 3 секунды.
5% удается сэкономить если:
drop type number38_t force; 
CREATE OR REPLACE TYPE number38_t AS varray(32000) OF NUMBER(38)
/
Плохо, конечно, что EXTEND(1). Можно попытаться как-то сократить число вызовов EXTEND.
OzerovL
Дата: 14.03.2011 17:19:57
dbms_photoshop

увы, результаты весьма красноречивы

SELECT MOD(id, 5) x, agg_narr(id) y
FROM (
SELECT rownum id FROM dual CONNECT BY LEVEL <= 30000
)
GROUP BY MOD(id, 5);
время исполнения 64,563 сек.

SELECT MOD(id, 5) x, collect(id) y
FROM (
SELECT rownum id FROM dual CONNECT BY LEVEL <= 30000
)
GROUP BY MOD(id, 5);
время исполнения 0,766 сек.