10g索引的作用实验1
来源:互联网 发布:app定位软件 编辑:程序博客网 时间:2024/06/05 14:59
1)授权SCOTT DBA权限
grant dba to scott;
2)SCOTT创建测试大表
create table tab_idx_test
as
select * from dba_objects
where owner in('PUBLIC','SCOTT','SYS','SYSTEM');
as
select * from dba_objects
where owner in('PUBLIC','SCOTT','SYS','SYSTEM');
3)打开执行计划
SET AUTOTRACE TRACE EXPLAIN
4)查询指定条件数据
SQL>SELECT OWNER,OBJECT_NAME FROM TAB_IDX_TEST WHERE OBJECT_NAME='DBA_INDEXES';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2925858188
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 581 | 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB_IDX_TEST | 7 | 581 | 142 (3)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBA_INDEXES')
Note
-----
- dynamic sampling used for this statement
SQL>
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2925858188
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 581 | 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB_IDX_TEST | 7 | 581 | 142 (3)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DBA_INDEXES')
Note
-----
- dynamic sampling used for this statement
SQL>
5)在object_name上创建索引idx_tab_idx_test
CREATE INDEX idx_tab_idx_test
on tab_idx_test(object_name);
on tab_idx_test(object_name);
6)再次查询指定条件数据
SQL> SELECT OWNER,OBJECT_NAME FROM TAB_IDX_TEST WHERE OBJECT_NAME='DBA_INDEXES';
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3112998378
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 166 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_IDX_TEST | 2 | 166 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TAB_IDX_TEST | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='DBA_INDEXES')
Note
-----
- dynamic sampling used for this statement
SQL>
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3112998378
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 166 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_IDX_TEST | 2 | 166 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TAB_IDX_TEST | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='DBA_INDEXES')
Note
-----
- dynamic sampling used for this statement
SQL>
分析:
1)从Cost上来看,如果不在OBJECT_NAME创建索引,则
COST为142,CPU占用为3%,Rows为7,Bytes为581
2)如果在OBJECT_NAME创建索引,则
COST为2,CPU占用为0%,Rows为2,Bytes为166
- 10g索引的作用实验1
- 10g 中RETENTION GUARANTEE 的作用
- oracle 10g的服务作用
- 10g 中RETENTION GUARANTEE 的作用
- Oracle 10g各个服务的作用
- Hive-0.8.1索引的实验
- 整理索引的实验
- 数据库索引的作用
- 数据库索引的作用
- 数据库索引的作用
- 数据库索引的作用
- 数据库索引的作用
- 数据库索引的作用
- 数据库索引的作用
- SQL索引的作用
- 索引的作用
- 数据库索引的作用
- MySQL索引的作用
- 在同一个设备上安装2个版本oracle
- 在同一个设备上安装2个版本oracle
- SPARC如何进入OK模式
- 查看raid信息
- ORACLE账户提示EXPIRED(GRACE)问题解决
- 10g索引的作用实验1
- IMPDP出现ORA-39083、ORA-14102 Or ORA-39083、ORA-2158、ORA-39112
- expdp无法导出空表问题
- gcc在solaris上安装的过程
- solaris下安装top工具详解
- javascript与java值互相调用
- “当前不会命中断点。还没有为该文档加载任何符号。”解决办法
- Android Installation error: INSTALL_FAILED_INSUFFICIENT_STORAGE
- FLEX中使用outerDocument