基于GROUP BY的物化视图自动建立索引

以前还没有注意到，建立包含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/