Research on the Oracle 11g

A test regarding vitual index:

 

C:/Users/henry>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 7月 24 14:57:58 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table test(id int,name varchar2(30));

表已创建。

SQL> insert into test select rownum,object_name from all_objects
  2  /

已创建68193行。

SQL>
SQL>
SQL> commit;

提交完成。

SQL> create unique index ix_test on test(id) nosegment;

索引已创建。

SQL>
SQL> explain plan for select * from test where id=1;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    30 |    91   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    30 |    91   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("ID"=1)

Note
-----
   - dynamic sampling used for this statement

已选择17行。

SQL> analyze table test compute statistics;

表已分析。

SQL> explain plan for select * from test where id=1;

已解释。

SQL>  explain plan for select * from test where id=1;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    28 |    91   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    28 |    91   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("ID"=1)

已选择13行。

SQL> alter session set "_use_nosegment_indexes"=true;

会话已更改。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    28 |    91   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    28 |    91   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("ID"=1)

已选择13行。

SQL>  explain plan for select * from test where id=1;

已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 166686173

--------------------------------------------------------------------------------

-------

| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Tim

e     |

--------------------------------------------------------------------------------

-------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |     1 |    28 |     2   (0)| 00:

00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |    28 |     2   (0)| 00:

00:01 |

|*  2 |   INDEX UNIQUE SCAN         | IX_TEST |     1 |       |     1   (0)| 00:

00:01 |

--------------------------------------------------------------------------------

-------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

已选择14行。

SQL>

 

 

This is maybe very helpful for the tuning SQL.