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.
分享到:
相关推荐
Research on the Workflow System of Collaborative Process Planning
Research on the intrinsic mode function (IMF) criterion in EMD method
The rate of social science engagement with this paradigm will vary depending on the specific research challenges/ interests and technical ability of the various groups involved. Some will naturally ...
The fusion of the position fingerprint matching algorithm and the polynomial distribution model can reduce the influence of the low positioning accuracy caused by the shortcomings of the polynomial ...
The Beckman Report on Database Research
Research on the Influence of a High-Speed Railway on the Spatial Structure of the Western Urban Agglomeration Based on Fractal Theory-Taking the Chengdu-Chongqing Urban Agglomeration as an Example
The SAGE Handbook of Online Research Methods(2nd) 英文无水印原版pdf 第2版 pdf所有页面使用FoxitReader、PDF-XChangeViewer、SumatraPDF和Firefox测试都可以打开 本资源转载自网络,如有侵权,请联系上传者...
二维光子晶体微腔透过率和品质因子研究,王丹,李列明,本文采用多重散射法研究了单缺陷点二维光子晶体微腔的品质因子和透过率随介质柱数量的变化。研究发现,随着介质柱数量的增加,微
Research on the Remote Data Collection Based SQL Server.pdf
In late May, 2008, a group of database researchers, architects, users and pundits met at the Claremont Resort in Berkeley, California to discuss the state of the research field and its impacts on ...
Research on the Small-size Ultra-low Bending Loss Optical Fibers
Web AR虚拟形象生成系统的研究,冯静怡,廖建新,随着虚拟现实和增强现实技术的发展,新型社交媒体不断出现并快速发展。在这些社交媒体应用之中,最重要的技术之一就是为用户生成
The application research on assembled sluice in the Mekong Delta,Phan Quy Anh Tuan,Xing Wengang,The paper introduces the results of the application research on assembled sluice by using the pre-...
Research on Three-dimensional Reconstruction of the Liver CT Images.pdf
The Handbook of Research on Scalable Computing Technologies | by Kuan-ching Li et al. | 2010 | ISBN: 9781605666617. Pervasive Grid Architectures and Applications. P2P Computing. Scalable Parallel ...
基于符号模型的概念设计,伊国栋,,针对概念设计的抽象性问题,提出了基于符号模型的概念设计方法。通过功能分解建立符号树,描述功能枝叶细分过程;通过功能-结构�
扑翼机脱落涡流体实验研究,朱睿,,本文通过流动显示实验研究扑翼机机翼后缘脱落涡分布。进行脱落涡涡量数据分析研究扑翼机升力特性。PIV研究结果揭示脱落涡的最大环
Title:Optimization research based on Oracle database——Research on Optimization o
How to Start Research in Computer Networks_ Seven Steps on the Road to Success
The Research on Characteristics and Translation of Network Catchwords.zip