Research on the Oracle 11g

最新推荐文章于 2025-08-18 15:06:57 发布

henrybai

最新推荐文章于 2025-08-18 15:06:57 发布

阅读量512

点赞数

CC 4.0 BY-SA版权

文章标签： oracle table sql access filter statistics

本文链接：https://blog.youkuaiyun.com/henrybai/article/details/2705039

本文记录了在Oracle Database 11g中创建表格、插入数据、建立无段索引的过程，并展示了如何使用explain plan来查看查询计划，通过设置_use_nosegment_indexes参数观察索引对查询效率的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

连接到:
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

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

-------

e |