以前还没有注意到,建立包含GROUP BY的物化视图,Oracle会自动在GROUP BY列上建立函数索引。
看一个简单的例子:
SQL> CREATE TABLE T
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 TYPE VARCHAR2(30));
Table created.
SQL> CREATE MATERIALIZED VIEW LOG ON T
2 WITH ROWID, SEQUENCE(TYPE)
3 INCLUDING NEW VALUES;
Materialized view log created.
SQL> INSERT INTO T
2 SELECT ROWNUM, TABLE_NAME, 'TABLE'
3 FROM USER_TABLES;
79 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE MATERIALIZED VIEW MV_T
2 REFRESH FAST
3 AS SELECT TYPE, COUNT(*)
4 FROM T
5 GROUP BY TYPE;
Materialized view created.
SQL> SELECT INDEX_NAME, TABLE_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'MV_T';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
I_SNAP$_MV_T MV_T
SQL> SELECT INDEX_NAME, COLUMN_NAME
2 FROM USER_IND_COLUMNS
3 WHERE INDEX_NAME = 'I_SNAP$_MV_T';
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
I_SNAP$_MV_T SYS_NC00003$
SQL> SELECT *
2 FROM USER_IND_EXPRESSIONS
3 WHERE INDEX_NAME = 'I_SNAP$_MV_T';
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION
--------------- --------------- ------------------------------ ---------------
I_SNAP$_MV_T MV_T SYS_OP_MAP_NONNULL("TYPE") 1
Oracle为了提高刷新的效率,建立了这个函数索引,其中SYS_OP_MAP_NONNULL的含义可以参考:http://yangtingkun.itpub.net/post/468/503110
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-670625/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-670625/