Забавный факт о collect

Многие знают о том, что oracle при необходимости сам создает доменные типы, например, когда используют тип объявленный в пакете(до 11g их можно было увидеть в dba_objects с именем like 'PLSQL%').

Факт 1

Точно так же он поступает и при вызове агрегатной функции collect.

-- Сначала проверим есть ли такие типы 
DB11G/XTENDER> select t.type_name,t.type_name,t.typecode 
 2 from dba_types t 
 3 where t.type_name like 'SYSTP%';

no rows selected


-- Выполним запрос с collect
DB11G/XTENDER> select collect(level) from dual connect by level<=10;

COLLECT(LEVEL)
-------------------------------------------------------------------------

SYSTPZvGjVQTySRSjYVlHXyEE2Q==(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

1 row selected.


-- Проверим снова и увидим что появился новый тип SYSTP%
DB11G/XTENDER> select t.type_name,t.type_name,t.typecode 
 2 from dba_types t 
 3 where t.type_name like 'SYSTP%';

TYPE_NAME                      TYPE_NAME                      TYPECODE
------------------------------ ------------------------------ ------------
SYSTPZvGjVQTySRSjYVlHXyEE2Q==  SYSTPZvGjVQTySRSjYVlHXyEE2Q==  COLLECTION

И еще выберем данные по нему из sys.obj$ - это нам потом пригодится:

DB11G/XTENDER> select obj#,type#,ctime,mtime,stime,status
  2  from sys.obj$ o$
  3  where o$.name = 'SYSTPZvGjVQTySRSjYVlHXyEE2Q==';

  OBJ#  TYPE# CTIME               MTIME               STIME              
------ ------ ------------------- ------------------- -------------------
103600     10 28.04.2012 01:02:35 28.04.2012 01:02:35 28.04.2012 01:02:35

Это абсолютно закономерно - нельзя вернуть клиенту информацию не описав ее.

Факт №2

Теперь зная, что тип создается, интересно что потом будет с этим типом: будет ли он удален после фетча или после отключения клиента? Например, при удалении пакетного типа автоматически дропался и доменный тип, будет ли здесь аналогичное автоматическое удаление?

Согласно Bug 4033868: COLLECT FUNCTION LEAVES TEMPORARY SYS TYPES BEHIND этот баг решен в "11.0", но я тестирую на 11.2.0.1 и элементарная проверка после дисконнекта показала, что тип продолжает существовать до рестарта инстанса, однако на самом деле даже после этого он остается, но в dba_objects уже не выводится.

Я дропну его сам, чтобы лишний раз не рестартить инстанс - это абсолютно аналогично тому как oracle "удаляет" этот тип в 11.2:

DB11G/XTENDER> drop type "SYSTPZvGjVQTySRSjYVlHXyEE2Q==";

Type dropped.

А теперь проверим:

DB11G/XTENDER> select * from dba_types
   where type_name='SYSTPZvGjVQTySRSjYVlHXyEE2Q==';

no rows selected

Вроде удалили, однако посмотрим в sys.obj$:

DB11G/XTENDER> select obj#,type#,ctime,mtime,stime,status
  2  from sys.obj$ o$
  3  where o$.name = 'SYSTPZvGjVQTySRSjYVlHXyEE2Q==';

  OBJ#  TYPE# CTIME               MTIME               STIME              
------ ------ ------------------- ------------------- -------------------
103600     10 28.04.2012 01:02:35 28.04.2012 01:40:37 31.12.4712 23:59:59

Как видите, объект остался, но с type#=10 и с stime равным последней дате 4712 года, а раньше было type#=13 и stime=mtime=ctime, а в 10.2 при ручном дропе этого типа никаких записей не оставалось. Поясню соответствия полей из sys.obj$ и dba_objects, чтобы было понятней: obj# - object_id, type# ~ код типа, ctime,mtime,stime - created, last_ddl_time, timestamp соответственно. По коду представления dba_objects мы увидим, что type# = 10 - это якобы "NON-EXISTENT" и выводить его не нужно.

and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)

А установка даты в 31.12.4712 23:59:59 указывает на его неактуальность - уж слишком далекое будущее :)

Факт №3

Теперь перейдем к тому, что меня собственно заставило повозиться с collect: ошибки с параллельным выполнением collect (обсуждение на форуме с указанием и подробным описанием Bug 11906197).

Вообще collect довольно глючная штука, я и сам на это уже неоднократно нарывался, и на металинке много различных багов связанных с collect(например, "Bug 8912282: COLLECT+UNIQUE+ORDER DOES NOT REMOVE DUPLICATES", "Bug 6145841: ORA-600[KOLOGSF2] ON CAST(COLLECT(..)) CALL","Bug 11802848: CAST/COLLECT DOES NOT WORK IN VERSION 11.2.0.2 WITH TYPE SYS.DBMS_DEBUG_VC2COLL", "Bug 6996176: SELECT COLLECT DISTINCT GROUP BY STATEMENT RETURNS DUPLICATE VALUES").

create table test_parallel parallel 8 as 
select mod(level,8) a, level b 
from dual 
connect by level<=1000;
create index IX_TEST_PARALLEL on TEST_PARALLEL (A);

DB11G/XTENDER> select/*+ PARALLEL(2)*/ cast(collect(a) as number_table) from test_parallel ;
select/*+ PARALLEL(2)*/ cast(collect(a) as number_table) from test_parallel
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-21710: argument is expecting a valid memory address of an object

Elapsed: 00:00:00.12
DB11G/XTENDER> select cast(collect(b) as number_table) from test_parallel group by a;
select cast(collect(b) as number_table) from test_parallel group by a
*
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly

Elapsed: 00:00:17.57

А вот с медленным своим агрегатом таких ошибок нет. Стандартная дилемма: либо нестабильный, но быстрый collect, либо медленный свой агрегат...

create or replace type ncollect_type as object
(
 
  data            sys.ku$_objnumset,
  
  static function ODCIAggregateInitialize
    ( sctx in out ncollect_type )
    return number ,

  member function ODCIAggregateIterate
    ( self  in out ncollect_type ,
      val   in     number
    ) return number ,
    
  member function ODCIAggregateDelete
    (  self in out  ncollect_type, 
       val  in      number
    ) return number ,
  member function ODCIAggregateTerminate
    ( self        in  ncollect_type,
      returnval   out sys.ku$_objnumset,
      flags in number
    ) return number ,
    
  member function ODCIAggregateMerge
    ( self in out ncollect_type,
      ctx2 in     ncollect_type
    ) return number
)
/
create or replace type body ncollect_type is

  static function ODCIAggregateInitialize
  ( sctx in out ncollect_type )
  return number
  is
  begin
    sctx := ncollect_type( sys.ku$_objnumset()) ;
    return ODCIConst.Success ;
  end;

  member function ODCIAggregateIterate
  ( self  in out ncollect_type ,
    val   in     number
  ) return number
  is
  begin
    self.data:=self.data multiset union sys.ku$_objnumset(val);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateDelete
  (  self in out  ncollect_type, 
     val  in      number
  ) return number
  is
  begin
    self.data:=self.data multiset except sys.ku$_objnumset(val);
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate
  ( self        in  ncollect_type ,
    returnval   out sys.ku$_objnumset ,
    flags       in  number
  ) return number
  is
  begin
    returnval:=self.data;
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateMerge
  ( self in out ncollect_type ,
    ctx2 in     ncollect_type
  ) return number
  is
  begin
    self.data := self.data multiset union ctx2.data;
    return ODCIConst.Success;
  end;
end;
/

И результаты:

Вариант	Время(сек)
select/*+ NO_PARALLEL*/ cast(collect(b) as number_table) from test_parallel group by a;	0.03
select/*+ NO_PARALLEL*/ ncollect(b) from test_parallel group by a	0.08
select ncollect(b) from test_parallel group by a;	0.07
select/*+ NO_PARALLEL*/ collect(a) from test_parallel;	0.02
select/*+ NO_PARALLEL*/ ncollect(a) from test_parallel	0.18
select ncollect(a) from test_parallel;	0.19
select/*+ NO_PARALLEL*/ collect(b) from test_parallel;	0.02
select/*+ NO_PARALLEL*/ ncollect(b) from test_parallel	0.18
select ncollect(b) from test_parallel;	0.06