发表于: 2006.01.07 23:56
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/50192
---------------------------------------------------------------
前些日子在检查数据库的时候发现，由于开发人员的笔误，建立一个复合索引的时候，第二列是个常数，很奇怪的是，Oracle居然认可这种写法。
研究了一下，发现这种常数索引对于单列索引也是成立的。
这就是最简单的常数索引：
SQL> CREATE TABLE TEST (ID NUMBER);
表已创建。
SQL> CREATE INDEX IND_TEST ON TEST (1);
索引已创建。
这种常数索引不能对相同的常数索引两次
SQL> CREATE INDEX IND_TEST1 ON TEST (1);
CREATE INDEX IND_TEST1 ON TEST (1)
*
ERROR 位于第 1 行:
ORA-01408: 此列列表已编制索引
常数索引支持各种常数，包括字符类型，数值类型日期类型等。
查询USER_INDEXES视图可以发现，Oracle实际上把这种常数索引当做了基于函数的索引了，它认为常数就是一个返回恒定数值的函数。
SQL> SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE TABLE_NAME = 'TEST';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IND_TEST FUNCTION-BASED NORMAL
不过由于这种常数索引没有包含任何字段，所以对于包含WHERE的查询语句，常数索引不能提供任何有意义的帮助。
总体来说，常数索引基本上没有什么意义。如果非要说常数索引有什么功能的话，那么由于常数索引的索引项不为空，因此常数索引可以用来回答表中的记录总数。
当然，可以创建空值NULL的索引，不过这个索引就真的没有任何意义了。
下面是通过常数索引得到表中记录总数的例子：
SQL> INSERT INTO TEST SELECT ROWNUM FROM DBA_OBJECTS;
已创建30936行。
SQL> COMMIT;
提交完成。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST')
PL/SQL 过程已成功完成。
SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM TEST;
COUNT(*)
----------
30936
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_TEST' (NON-UNIQUE) (Cost=4 Card=30936)
其实这个常数索引的执行效率甚至比主键全扫还高，性能测试的具体情况参考：http://blog.itpub.net/post/468/50190
re: 常数索引 
