Oracle SQL

Many people know that oracle creates domain types on its own when necessary, for example when using a type declared in a packet (before 11g they could be observed in dba_objects with the name like ‘PLSQL%’).

Fact #1

It acts in the same way when calling an aggregate function “collect”.

-- Firstly we check if there are such types
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
 
 
-- Executing a query with 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.
 
 
-- We check it again and observe that a new type SYSTP% has appeared
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

And also we select data on it from sys.obj$. We will need it later:

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

This is a natural result as you can not return information to the client without having described it.

Fact #2

Now as we know that the type is created, it is interesting to know what will happen to this type: whether it will be removed either after fetch or after disconnecting the client? For example, the domain type was automatically dropped when the packet type was removed. Are we going to have a similar automatic remove here?

According to Bug 4033868: COLLECT FUNCTION LEAVES TEMPORARY SYS TYPES BEHIND this bug is fixed in “11.0″, but I am testing it on 11.2.0.1 and a basic check after disconnection showed, that this type exists until instance restart. In fact, it stays there even after that, but it is not displayed in dba_objects.

I will drop it manually, so I won’t have to restart the instance once again. This is absolutely similar to how oracle “removes” this type in 11.2:

DB11G/XTENDER> drop type "SYSTPZvGjVQTySRSjYVlHXyEE2Q==";
 
Type dropped.

And now lets check it:

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

It seems like we have removed it, but what if we look in the 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

As you can see, the object is still there, but with type#=10 and with “stime” equal to the last date of year 4712, and before that it was type#=13 and stime=mtime=ctime, and in 10.2 after manual drop of this type no entries were left. I will explain the correspondence of the fields from “sys.obj$” and “dba_objects” to clarify this: obj# – object_id, type# ~ type code, ctime,mtime,stime – created, last_ddl_time, timestamp respectively. By the “dba_objects” view code we will see that type# = 10 is supposedly “NON-EXISTENT” and displaying it is not necessary.

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

And the date being set to 31.12.4712 23:59:59 indicates its irrelevance – this future is too distant

Fact #3

“Collect” is a pretty buggy thing in general, as I have repeatedly noticed, and Metalink has information about lots of bugs related to “collect” (for example, Bug 11906197 “Parallel query with COLLECT function fails with ORA-7445/ORA-600.”,”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

I don’t experience such errors while using my slow aggregate. Here is a common dilemma: whether to use an unstable but speedy “collect”, or a slow aggregate of my own…

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;
/

And the results: