10g New Feature--Collect function

最新推荐文章于 2024-12-16 18:16:05 发布

原创最新推荐文章于 2024-12-16 18:16:05 发布 · 705 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#function #oracle #types #user #sql #system

Oracle数据库学习专栏收录该内容

29 篇文章

订阅专栏

本文介绍了 Oracle 数据库中 Collect 函数的功能与使用方法，该函数能够将多行数据转换为集合数据类型。通过示例展示了如何在 SQL 查询中应用 Collect 函数，并探讨了其在硬解析过程中创建系统所有类型的行为及其持久化特性。

10g New Feature--Collect function

Collect Functio can use in sql query to convert multiple rows of data to a collection of data. Oracle create a SYS owned type for it.

-->tested it in Walmart Prod Env

SQL> select collect(tname) from tab;

select collect(tname) from tab

ORA-20900: User DEV_USER is not allowed to run DDL at any time.

ORA-06512: at line 76

From the error message we can see, Oracle try to launch DDL statement after we run the query. What is the DDL? That is try to create a sys-owned type.

-->tested it in Local pc.

13:09:38 scott@ORCL>select deptno, collect(ename) from emp group by deptno;

DEPTNO
----------
COLLECT(ENAME)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('CLARK', 'KING', 'MILLER')

20
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('CLARK', 'KING', 'MILLER', 'SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')

30
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD', 'ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')

40
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')

60
SYSTPp1jeTzi6RhyW0a79+j6jpQ==('CLARK', 'KING', 'MILLER')

Obviously SYSTPp1jeTzi6RhyW0a79+j6jpQ== is new type. This new type is created when doing hard parse. Once two sql use the same execution plan, this type can be reused, Otherwise another new type will be created.

14:08:19 scott@ORCL>select collect(ename) from emp;

COLLECT(ENAME)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTPP9LRWdLHR3WjenOV17QwWQ==('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER', 'SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN',
'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER', 'SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'M
ILLER', 'SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')

After the new was created, even you clear shared pool or bounce the db, ti will persist. Oracle official said that it will be purged in 24 hours if it is not used. But if you have big shared pool, the time can be longer.

13:12:44 scott@ORCL>conn / as sysdba

13:13:11 sys@ORCL>select owner,type_name, type_oid from dba_types where type_name like '%SYSTPUE+PI2RYST6xWXI1s8F+Ng==%';

OWNER TYPE_NAME TYPE_OID
------------------------------ ------------------------------ --------------------------------
SCOTT SYSTPUE+PI2RYST6xWXI1s8F+Ng== 8A5621C5481146779EB6C7591E397360

13:13:32 sys@ORCL>shutdown immediate;

ORACLE 例程已经关闭。
13:14:10 sys@ORCL>startup;
ORACLE 例程已经启动。

Total System Global Area 612368384 bytes
Fixed Size      1250428 bytes
Variable Size    260049796 bytes
Database Buffers   343932928 bytes
Redo Buffers      7135232 bytes

13:33:53 sys@ORCL>select owner,type_name, type_oid from dba_types where type_name like '%SYSTPUE+PI2RYST6xWXI1s8F+Ng==%';

OWNER TYPE_NAME TYPE_OID
------------------------------ ------------------------------ --------------------------------
SCOTT SYSTPUE+PI2RYST6xWXI1s8F+Ng== 8A5621C5481146779EB6C7591E397360