问题是这样引起的：测试建一个single table hash cluster，建了好几次，也查了不少资料，一般报错 ORA-01753 是因为设置的字段类型不一致，但是经检查字段类型确实是一致了，且number也被cast定成了整形，应该没问题呀，但是总是报错：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
test@ORADG(192.168.0.41)> create cluster hash_cluster 2 (hash_key number(10)) 3 hashkeys 75000 4 size 150 5 single table 6 hash is hash_key 7 / Cluster created. Elapsed: 00:00:02.42 test@ORADG(192.168.0.41)> create table t_hashed 2 cluster hash_cluster(object_id) 3 as 4 select owner,object_name,subobject_name, 5 cast(object_id as number(10)) as object_id, 6 data_object_id,object_type,created, 7 last_ddl_time,timestamp,status,temporary, 8 generated,secondary 9 from all_objects; cluster hash_cluster(object_id) * ERROR at line 2: ORA-01753: column definition incompatible with clustered column definition Elapsed: 00:00:00.10 test@ORADG(192.168.0.41)> select * from v$version; BANNER ---------------------------------------------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production CORE 9.2.0.3.0 Production TNS for Linux: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production |
但是同样的语句在10g中是可以实现的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SQL> create cluster hash_cluster 2 (hash_key number(10)) 3 hashkeys 75000 4 size 150 5 single table 6 hash is hash_key 7 / Cluster created. SQL> create table t_hashed 2 cluster hash_cluster(object_id) 3 as 4 select owner,object_name,subobject_name, 5 cast(object_id as number(10)) as object_id, 6 data_object_id,object_type,created, 7 last_ddl_time,timestamp,status,temporary, 8 generated,secondary 9 from all_objects; Table created. 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 Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> |
一开始是怀疑9i版本不支持single table hash cluster，但是测试直接写建表语句就能顺利建立：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
test@ORADG(192.168.0.41)> create table t_hashed 2 (owner VARCHAR2(30), 3 object_name VARCHAR2(30), 4 subobject_name VARCHAR2(30), 5 OBJECT_ID NUMBER(10,0), 6 DATA_OBJECT_ID NUMBER, 7 OBJECT_TYPE VARCHAR2(18), 8 CREATED DATE, 9 LAST_DDL_TIME DATE, 10 TIMESTAMP VARCHAR2(19), 11 STATUS VARCHAR2(7), 12 TEMPORARY VARCHAR2(1), 13 GENERATED VARCHAR2(1) , 14 SECONDARY VARCHAR2(1) 15 ) cluster hash_cluster(object_id) 16 17 / Table created |
因此进一步怀疑cast函数的问题，进一步测试：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
####### 9204上 ############### test@ORADG(192.168.0.41)> create table xx(a number); Table created. Elapsed: 00:00:00.09 test@ORADG(192.168.0.41)> create table yy as select cast(a as number(10,0)) as a from xx; Table created. Elapsed: 00:00:00.09 test@ORADG(192.168.0.41)> test@ORADG(192.168.0.41)> test@ORADG(192.168.0.41)> desc xx Name Null? Type ----------------------------------------------------- -------- ------------------------------------ A NUMBER test@ORADG(192.168.0.41)> desc yy Name Null? Type ----------------------------------------------------- -------- ------------------------------------ A NUMBER ##### 10201上 ####################### SQL> create table xx(a number); Table created. SQL> create table yy as select cast(a as number(10,0)) as a from xx; Table created. SQL> desc xx; Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER SQL> desc yy Name Null? Type ----------------------------------------- -------- ---------------------------- A NUMBER(10) |
看来确实是因为这个cast函数的问题了，查了一下，发现这个问题竟然是个BUG：Bug 3138341：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
Subject: Support Description of Bug 3138341 Doc ID: Note:3138341.8 Type: PATCH Last Revision Date: 26-MAR-2004 Status: PUBLISHED Click here for details of sections in this note. Bug 3138341 Wrong datatypes returned from PLSQL REF Cursor using CAST This note gives a brief overview of bug 3138341. Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions < 10G Versions confirmed as being affected 8.1.7.4 9.0.1.4 9.2.0.4 Platforms affected Generic (all / most platforms affected) Fixed: This issue is fixed in 9.2.0.5 (Server Patch Set) 10g Production Base Release Symptoms: Wrong Results Related To: Datatypes - Objects (Types/Collections) PL/SQL Description Calling a PLSQL stored procedure containing a REF cursor on a select with a CAST operator on an ADT can return the wrong precision value. -------------------------------------------------------------------------------- |