Oracle9i的1467错误-CSDN博客

在使用自定义聚集函数时出现了一个ORA-1467错误。

根据Oracle文档上的描述，1467错误是由于排序的键值超过了DB_BLOCK_SIZE。

但是出现错误的SQL似乎并不满足这个条件。下面简单构造这个错误：

SQL> CREATE OR REPLACE TYPE T_LINK AS OBJECT (
2 STR VARCHAR2(30000),
3 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER,
4 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER,
5 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER,
6 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER
7 )
8 /

Type created.

SQL> CREATE OR REPLACE TYPE BODY T_LINK IS
2 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK) RETURN NUMBER IS
3 BEGIN
4 SCTX := T_LINK(NULL);
5 RETURN ODCICONST.SUCCESS;
6 END;
7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK, VALUE IN VARCHAR2) RETURN NUMBER IS
9 BEGIN
10 SELF.STR := SELF.STR || VALUE || ',';
11 RETURN ODCICONST.SUCCESS;
12 END;
13
14 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK, RETURNVALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
15 BEGIN
16 RETURNVALUE := SUBSTR(SELF.STR, 1, LENGTH(SELF.STR) - 1);
17 RETURN ODCICONST.SUCCESS;
18 END;
19
20 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK, CTX2 IN T_LINK) RETURN NUMBER IS
21 BEGIN
22 NULL;
23 RETURN ODCICONST.SUCCESS;
24 END;
25 END;
26 /

Type body created.

SQL> CREATE OR REPLACE FUNCTION F_LINK(P_STR VARCHAR2) RETURN VARCHAR2
2 AGGREGATE USING T_LINK;
3 /

Function created.

首先是构造自定义聚集函数，关于这个函数的详细说明，可以参考：http://yangtingkun.itpub.net/post/468/3380

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

Table created.

SQL> SELECT OWNER, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID)
2 FROM T
3 GROUP BY OWNER;
SELECT OWNER, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID)
*
ERROR at line 1:
ORA-01467: sort key too long

SQL> DESC T
Name Null? Type
--------------------------------- -------- --------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

OWNER列的长度只有30，而且即使换成长度为1的列也没有作用。

SQL> SELECT TEMPORARY, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID)
2 FROM T
3 GROUP BY TEMPORARY;
SELECT TEMPORARY, F_LINK(OBJECT_NAME), F_LINK(SUBOBJECT_NAME), F_LINK(OBJECT_ID)
*
ERROR at line 1:
ORA-01467: sort key too long

既然和GROUP BY列没有太大的关系，那么是否与F_LINK输入列的长度有关：

SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
2 FROM T
3 GROUP BY OWNER;
SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
*
ERROR at line 1:
ORA-01467: sort key too long

即使将F_LINK的输入参数变成常数1、2、3，问题仍然会出现，看来和F_LINK输入参数的长度也没有关系。

虽然与F_LINK输入参数长度没有关系，但是和F_LINK函数调用次数有关，将F_LINK三次调用变为两次调用，就可以得到结果：

SQL> SELECT OWNER, F_LINK(1), F_LINK(2)
2 FROM T
3 WHERE ROWNUM < 10
4 GROUP BY OWNER;

OWNER F_LINK(1) F_LINK(2)
------------------------------ ------------------------------ -----------------
SYS 1,1,1,1,1,1,1,1,1 2,2,2,2,2,2,2,2,2

这个问题在9i上就会出现，而且与DB_BLOCK_SIZE的大小没有关系。在DB_BLOCK_SIZE为8K和16K的环境下测试，得到的结果完全一样。

SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Table created.

SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
2 FROM T
3 GROUP BY OWNER;
SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
*
ERROR at line 1:
ORA-01467: sort key too long

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NUMBER NUMBER;
3 V_STR VARCHAR2(4000);
4 V_RES NUMBER;
5 BEGIN
6 V_RES := DBMS_UTILITY.GET_PARAMETER_VALUE('db_block_size', V_NUMBER, V_STR);
7 DBMS_OUTPUT.PUT_LINE(V_NUMBER);
8 END;
9 /
8192

PL/SQL procedure successfully completed.

SQL> CONN TEST@GPODB
Enter password:
Connected.
SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
2 FROM T
3 GROUP BY OWNER;
SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3)
*
ERROR at line 1:
ORA-01467: sort key too long

SQL> SHOW PARAMETER DB_BLOCK_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 16384

而这个问题在10g已经得到了解决，测试发现即使有30多个F_LINK的调用，也没有出现ORA-1467错误。

SQL> CONN YANGTK/YANGTK@YTK已连接。
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> COL F_LINK(1) FORMAT A20
SQL> COL F_LINK(2) FORMAT A20
SQL> COL F_LINK(3) FORMAT A20
SQL> COL F_LINK(4) FORMAT A20
SQL> SELECT OWNER, F_LINK(1), F_LINK(2), F_LINK(3), F_LINK(4)
2 FROM T
3 WHERE ROWNUM < 5
4 GROUP BY OWNER;

OWNER F_LINK(1) F_LINK(2) F_LINK(3) F_LINK(4)
-------------------- -------------------- -------------------- -------------------- ------
SYS 1,1,1,1 2,2,2,2 3,3,3,3 4,4,4,4

在9i上如果碰到这个问题，可以考虑使用变通的方法解决。比如使用CONNECT BY语句来代替自定义聚集函数。

SQL> SELECT OWNER, MAX(LTRIM(SYS_CONNECT_BY_PATH(OBJECT_NAME, ','), ',')) OBJECT_NAME,
2 MAX(LTRIM(SYS_CONNECT_BY_PATH(SUBOBJECT_NAME, ','), ',')) SUBOBJECT_NAME,
3 MAX(LTRIM(SYS_CONNECT_BY_PATH(OBJECT_ID, ','), ',')) OBJECT_ID
4 FROM
5 (
6 SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID,
7 ROW_NUMBER() OVER(PARTITION BY OWNER ORDER BY OBJECT_NAME) RN
8 FROM T
9 WHERE OBJECT_ID BETWEEN 30014 AND 30017
10 )
11 START WITH RN = 1
12 CONNECT BY PRIOR RN + 1 = RN
13 AND PRIOR OWNER = OWNER
14 GROUP BY OWNER;

OWNER OBJECT_NAME SUBOBJECT_NAM OBJECT_ID
-------- ---------------------------------------------------- ------------- ---------------
OLAPSYS ALL$OLAP1_CUBES,ALL$OLAPMR_DIM_LEVELS_KEYMAPS 30016,30017
PUBLIC CWM2_OLAP_AW_ACCESS,CWM2_OLAP_INSTALLER 30014,30015

如果无法聚集函数函数无法代替，可以使用下面的办法：

SQL> SELECT A.OWNER, A.OBJECT_NAME, A.SUBOBJECT_NAME, B.OBJECT_ID
2 FROM
3 (
4 SELECT OWNER, F_LINK(OBJECT_NAME) OBJECT_NAME,
5 F_LINK(SUBOBJECT_NAME) SUBOBJECT_NAME
6 FROM T
7 WHERE OBJECT_ID BETWEEN 30014 AND 30017
8 GROUP BY OWNER
9 ) A,
10 (
11 SELECT OWNER, F_LINK(OBJECT_ID) OBJECT_ID
12 FROM T
13 WHERE OBJECT_ID BETWEEN 30014 AND 30017
14 GROUP BY OWNER
15 ) B
16 WHERE A.OWNER = B.OWNER;