Oracle differentiates materialized views and tables at 10 GB

Oracle distinguishes materialized views from tables before 9i at the beginning of 10g. Many functions do not distinguish between tables and materialized views. After 10g. Many features differentiate tables from materialized views. The common comment on table statement is no longer valid for MATERIALIZED views and must be replaced by the comment on materialized view statement. Www.2cto.com SQL> SELECT * FROM V $ VERSION; BANNER ----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64 biPL/SQL Release 10.2.0.4.0-ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0-ProductionNLSRTL Version 10.2.0.4.0-Production SQL> CREATE TABLE T_BASE (tbID NUMBER, TYPE VARCHAR2 (30), num number); www.2cto.com Table created. SQL> CREATE MATERIALIZED VIEW LOG ON T_BASE2 WITH ROWID, SEQUENCE (TYPE, NUM) 3 INCLUDING New values; Materialized view log created. SQL> CREATE MATERIALIZED VIEW MV_BASE2 REFRESH FAST ENABLE QUERY REWRITE AS3 SELECT TYPE, SUM (NUM) SUM_NUM, COUNT (NUM) CNT_NUM, COUNT (*) CNT4 FROM T_BASE5 GROUP BY TYPE; materialized view created. SQL> COMMENT ON TABLE MV_BASE IS 'comment ON A MATERIALIZED view'; COMMENT ON TABLE MV_BASE IS 'comment ON A MATERIALIZED view' * www.2cto.com ERROR at line 1: O RA-12098: cannot comment on the materialized view SQL> COMMENT ON MATERIALIZED VIEW MV_BASE IS 'comment ON A MATERIALIZED view'; COMMENT created. SQL> COL COMMENTS FOR A60SQL> SELECT * FROM USER_MVIEW_COMMENTS; MVIEW_NAME COMMENTS COMMENT MV_BASE COMMENT ON A MATERIALIZED VIEW not only has changed COMMENT, but also Oracle introduces the execution plan of MATERIALIZED views. Rows are refined to separate scanning of materialized views FROM full-Table scanning: www.2cto.com SQL> SET AUTOT ON EXPSQL> SELECT COUNT (*) FROM MV_BASE; COUNT (*) ---------- 0 Execution Plan -------------------------------------------------------- Plan hash value: 3034976462 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | average --------------------------------------------------------------- ---------- | 0 | select statement | 1 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 2 | MAT_VIEW access full | MV_BASE | 1 | 2 (0) | 00:00:01 | notice Note www.2cto.com ------dynamic sampling used for this statement SQL> SELECT/* + REWRITE */TYPE, COUNT (*) FROM T_BASE GROUP BY TYPE; no rows selected Execution Plan ----------- --------------------------------------------- Plan hash value: 1008429399 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 0 | select statement | 1 | 30 | 2 (0) | 00:00:01 | 1 | MAT_VIEW rewrite access full | MV_BASE | 1 | 30 | 2 (0) | 00:00:01 | www.2cto.com Note ------dynamic sampling used for this statement before 9i, it is difficult to distinguish whether a table or materialized view is scanned from the execution plan, but it is clear now. In general, this improvement makes sense. Users can better understand whether the objects to be processed are tables or materialized views.