11.2新特性之不可见索引-临时统计信息

Oracle11.2通过增加部分对象的会话级可见性/属性，支持更灵活得维护、开发。
比如新增了editionable对象，不可见索引(invisible index)，不可见统计信息(pending statistics)，分别验证如下。

1，支持editionable的对象类型：
同义词
视图
函数
过程
包
类型
库
触发器

测试如下：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as mh@boclink
 
SQL> desc t
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
A    INTEGER Y                        
B    INTEGER Y                        
C    INTEGER Y                        
 
SQL> alter user mh enable editions;
 
User altered
 
SQL> create edition test_ed;
 
Done
 
SQL>
SQL> select sys_context('userenv','current_edition_name') from dual;
 
SYS_CONTEXT('USERENV','CURRENT
--------------------------------------------------------------------------------
ORA$BASE
 
SQL> create editioning view vt
  2  a
  3 
SQL>
SQL> create editioning view vt
  2  as
  3  select a,b from t;
 
View created
 
SQL> alter session set edition=test_ed;
 
Session altered
 
SQL> create editioning view vt
  2  as
  3  select a,b,c from t;
 
create editioning view vt
as
select a,b,c from t
 
ORA-00955: 名称已由现有对象使用
 
SQL> create or replace editioning view vt
  2  as
  3  select a,b,c from t;
 
View created
 
SQL> desc vt;
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
A    INTEGER Y                        
B    INTEGER Y                        
C    INTEGER Y                        
 
SQL> alter session set edition=ora$base;
 
Session altered
 
SQL> desc vt;
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
A    INTEGER Y                        
B    INTEGER Y                        
 
SQL> alter session set edition=test_ed;
 
Session altered
 
SQL> desc vt;
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
A    INTEGER Y                        
B    INTEGER Y                        
C    INTEGER Y                        
 
SQL> drop view vt;
 
View dropped
 
SQL> desc vt;
Object vt does not exist.
 
SQL> alter session set edition=ora$base;
 
Session altered
 
SQL> desc vt;
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
A    INTEGER Y                        
B    INTEGER Y                        
 
SQL> drop edition test_ed;
 
Done
 
SQL>

SQL> desc user_objects;
Name           Type          Nullable Default Comments                                                                    
-------------- ------------- -------- ------- ----------------------------------------------------------------------------
OBJECT_NAME    VARCHAR2(128) Y                Name of the object                                                          
SUBOBJECT_NAME VARCHAR2(30)  Y                Name of the sub-object (for example, partititon)                            
OBJECT_ID      NUMBER        Y                Object number of the object                                                 
DATA_OBJECT_ID NUMBER        Y                Object number of the segment which contains the object                      
OBJECT_TYPE    VARCHAR2(19)  Y                Type of the object                                                          
CREATED        DATE          Y                Timestamp for the creation of the object                                    
LAST_DDL_TIME  DATE          Y                Timestamp for the last DDL change (including GRANT and REVOKE) to the object
TIMESTAMP      VARCHAR2(19)  Y                Timestamp for the specification of the object                               
STATUS         VARCHAR2(7)   Y                Status of the object                                                        
TEMPORARY      VARCHAR2(1)   Y                Can the current session only see data that it place in this object itself?  
GENERATED      VARCHAR2(1)   Y                Was the name of this object system generated?                               
SECONDARY      VARCHAR2(1)   Y                Is this a secondary object created as part of icreate for domain indexes?   
NAMESPACE      NUMBER        Y                Namespace for the object                                                    
EDITION_NAME   VARCHAR2(30)  Y                Name of the edition in which the object is actual                           

2，不可见视图
SQL> desc t;
Name Type    Nullable Default Comments
---- ------- -------- ------- --------
A    INTEGER Y                        
B    INTEGER Y                        
C    INTEGER Y                        
 
SQL> create index idx_t_a on t(a);
 
Index created
 
SQL> set autotrace on
Cannot SET AUTOTRACE
 
SQL> explain plan for select * from t where a=1;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1194865126
--------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    39 |     1   (0)| 00:
|   1 |  TABLE ACCESS BY INDEX ROWID| T       |     1 |    39 |     1   (0)| 00:
|*  2 |   INDEX RANGE SCAN          | IDX_T_A |     1 |       |     1   (0)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
 
18 rows selected
 
SQL> alter index idx_t_a invisible;
 
Index altered
 
SQL> explain plan for select * from t where a=1;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    39 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
 
17 rows selected
 
SQL> select visibility from user_indexes where table_name='T';
 
VISIBILITY
----------
INVISIBLE
 
SQL> show parameter visible
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
 
SQL> alter session set optimizer_use_invisible_indexes=true;
 
Session altered
 
SQL> explain plan for select * from t where a=1;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1194865126
--------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    39 |     1   (0)| 00:
|   1 |  TABLE ACCESS BY INDEX ROWID| T       |     1 |    39 |     1   (0)| 00:
|*  2 |   INDEX RANGE SCAN          | IDX_T_A |     1 |       |     1   (0)| 00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
 
18 rows selected

3,PENDING STATISTICS

SQL> show parameter pending
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics     boolean     FALSE
 
SQL> select num_rows from user_tables where table_name='T';
 
  NUM_ROWS
----------
 
SQL> exec dbms_stats.set_table_prefs(user,'T','PUBLISH','false');
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_stats.gather_table_stats(user,'T');
SQL>
PL/SQL procedure successfully completed
 
SQL>
SQL> select num_rows from user_tables where table_name='T';
 
  NUM_ROWS
----------
 
SQL> exec dbms_stats.publish_pending_stats(user,'T');
 
PL/SQL procedure successfully completed
 
SQL> select num_rows from user_tables where table_name='T';
 
  NUM_ROWS
----------
         0
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-752084/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-752084/