NVARCHAR2索引造成的ORA-964错误（一）

研究NVARCHAR2类型的时候，发现一个有趣的bug。

输入NVARCHRA2类型字符串：http://yangtingkun.itpub.net/post/468/476812

 

 

在上面的文章连接中，描述了表字段类型为VARCHAR2类型，而输入字符串是NVARCHAR2类型时，如果通过创建一个函数索引来达到索引扫描的目的。

不过上面的文章的测试是在10g中进行的，如果在9204环境中，就会出现一个有趣的bug：

SQL> 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

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM ALL_OBJECTS;

已创建30947行。

SQL> COMMIT;

提交完成。

SQL> CREATE INDEX IND_T_NAME ON T(NAME);

索引已创建。

SQL> SET AUTOT ON EXP
SQL> SELECT * FROM T WHERE NAME = 'T';

        ID NAME
---------- ------------------------------
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'IND_T_NAME' (NON-UNIQUE)

 

SQL> SELECT * FROM T WHERE NAME = N'T';

        ID NAME
---------- ------------------------------
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'

 

SQL> CREATE INDEX IND_T_N_NAME ON T(TO_NCHAR(NAME));

索引已创建。

SQL> SELECT * FROM T WHERE NAME = N'T';
SELECT * FROM T WHERE NAME = N'T'
 *
第 1 行出现错误:
ORA-00964: 表名不在 FROM 列表中

到执行最后一个查询的时候，出现了一个有趣的错误，ORA-964。

首先说SQL语句本身肯定没有错误，而且在之前建立函数索引之前执行也没有问题。在上一篇文章中Oracle的10g以上版本执行，也没有碰到这个错误。

再来看这个错误本身，也让人摸不着头脑，说时候，这个错误号还是第一次看到。从执行的SQL语句上看，显然和这个错误描述不沾边。

如果说上面的错误还不算奇怪，那么下面看看更奇怪的：

SQL> SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = N'T';
SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = N'T'
 *
第 1 行出现错误:
ORA-00964: 表名不在 FROM 列表中

SQL> SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = n'T';

        ID NAME
---------- ------------------------------
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

执行计划
----------------------------------------------------------
ERROR:
ORA-00964: 表名不在 FROM 列表中

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错

上面两个SQL唯一的差别就是标识NVARCHAR2的N标识是否大写。而对于Oracle来说，字符串外的命令都会自动转换为大写，可是上面两个SQL的结果完全不一样，第一个SQL和前面的错误一样，而第二个SQL可以得到结果，只不过在获取执行计划的过程中出现了ORA-964错误。

一个大小写的区别居然导致结果如此不同。不过从这里也可以看到，错误多半发生在获取执行计划阶段，否则ORA-964错误的由来真的很难解释。

最后再来看一组更神奇的现象：

SQL> SELECT * FROM T WHERE NAME = N'T';

        ID NAME
---------- ------------------------------
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'

 

SQL> SELECT * FROM T WHERE NAME = n'T';

        ID NAME
---------- ------------------------------
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

执行计划
----------------------------------------------------------
ERROR:
ORA-00964: 表名不在 FROM 列表中

SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
SQL> SELECT * FROM T WHERE NAME = N'T';

        ID NAME
---------- ------------------------------
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

执行计划
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'

 

SQL> SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = N'T';
SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = N'T'
 *
第 1 行出现错误:
ORA-00964: 表名不在 FROM 列表中

SQL> SET AUTOT OFF
SQL> SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = N'T';
SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = N'T'
 *
第 1 行出现错误:
ORA-00964: 表名不在 FROM 列表中

SQL> SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = n'T';

        ID NAME
---------- ------------------------------
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

SQL> SELECT /*+ FIRST_ROWS */ * FROM T WHERE NAME = N'T';

        ID NAME
---------- ------------------------------
     29502 T
     30798 T
     30829 T
     30925 T
     30932 T

执行过刚才的SQL，再次运行刚才错误的语句，发现N’T’的写法，居然可以顺利执行了。只是n’T’还有一定的问题。

莫非是一次正确的执行，导致Oracle不需要进行分析和解析，从而导致了错误的消失。

下面关闭AUTOTRACE，看看问题是否会消失：

可以看到执行N’T’查询的时候，仍然报错，而执行n’T’查询则可以顺利执行，而且执行之后，再次运行N’T’的查询，居然也可以顺利进行。

根据上面所有这些因素判断，应该错误出现在Oracle后台的递归调用SQL中，肯定是由于Oracle没有妥善处理字符串前面的N造成的。

由此可见，对于不是很常用的功能，发生bug的几率要远远大于常用的功能。由于用户使用的少，排除掉的bug也就比较少，所以最新的功能和很偏很冷的功能，都会伴随着大量的bug而存在。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-533335/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-533335/