函式索引的儲存

SQL> create table t as select rownum a from all_objects where rownum < 11;

Table created

SQL> select table_name,column_name from user_tab_cols;

TABLE_NAME COLUMN_NAME

------------------------------ ------------------------------

T A

SQL> create index t_ind on t(a+100);

Index created

SQL> select table_name,column_name from user_tab_cols;

TABLE_NAME COLUMN_NAME

------------------------------ ------------------------------

T A

T SYS_NC00002$

這裡可以看到表t多出來一個列SYS_NC00002$

SQL> select a, SYS_NC00002$ from t;

A SYS_NC00002$

---------- ------------

1 101

2 102

3 103

4 104

5 105

6 106

7 107

8 108

9 109

10 110

10 rows selected

從查詢的結果可以看到這個列就是我們的函式索引的值。

SQL>

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> select table_name,blocks from user_tables where table_name='T';

TABLE_NAME BLOCKS

------------------------------ ----------

T 4

SQL> create index t_ind1 on t(rpad(to_char(a),4000,'*'));

Index created

SQL> select table_name,column_name from user_tab_cols where table_name='T';

TABLE_NAME COLUMN_NAME

------------------------------ ------------------------------

T A

T SYS_NC00002$

T SYS_NC00003$

SQL> select a, SYS_NC00002$, SYS_NC00003$ from t;

A SYS_NC00002$ SYS_NC00003$

---------- ------------ --------------------------------------------------------------------------------

1 101 1*******************************************************************************

2 102 2*******************************************************************************

3 103 3*******************************************************************************

4 104 4*******************************************************************************

5 105 5*******************************************************************************

6 106 6*******************************************************************************

7 107 7*******************************************************************************

8 108 8*******************************************************************************

9 109 9*******************************************************************************

10 110 10******************************************************************************

10 rows selected

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed

SQL> select table_name,blocks from user_tables where table_name='T';

TABLE_NAME BLOCKS

------------------------------ ----------

T 4

SQL> select index_name ,leaf_blocks from user_indexes where table_name='T';

INDEX_NAME LEAF_BLOCKS

------------------------------ -----------

T_IND 1

T_IND1 10