Oracle9i的1467錯誤

在使用自定義聚集函式時出現了一個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;

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

只要避免在同一個子查詢中自定義聚集函式不要超過限定數量就可以了。