Oracle11.2透過增加部分物件的會話級可見性/屬性，支援更靈活得維護、開發。
比如新增了editionable物件，不可見索引(invisible index)，不可見統計資訊(pending statistics)，分別驗證如下。

1，支援editionable的物件型別：
同義詞
檢視
函式
過程
包
型別
庫
觸發器

測試如下：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as

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

11.2新特性之不可見索引-臨時統計資訊

相關文章