部分行索引使用介绍
来源:互联网 发布:广西网络安全技术大赛 编辑:程序博客网 时间:2024/05/07 18:57
from http://www.askmaclean.com/archives/%E9%83%A8%E5%88%86%E8%A1%8C%E7%B4%A2%E5%BC%95%E4%BD%BF%E7%94%A8%E4%BB%8B%E7%BB%8D.html
部分行索引使用介绍
函数索引是Oracle索引中比较特殊的,我们这里讨论函数索引中部分行索引的使用。
部分行索引顾名思义仅就表中的一部分记录做索引,请看代码示例:
drop table test;
create table test (t1 int, t2 char(1));
declare
i int :=0;
begin
while i<100000
loop
insert into test values( i, ‘N’);
i:=i+1;
commit;
end loop;
end;
在test表上插入大量t2为N的行,并插入少量t2为Y的行
create index ind_t2y on test( case t2 when ‘Y’ then t2 end);
SQL> select count(*) from test;
COUNT(*)
———-
100004
表上供有100004条数据
SQL> select count(*) from test where t2=’Y';
COUNT(*)
———-
4
为t2列为’Y'的共有4条。
我们来分析该索引:
SQL> analyze index ind_t2y validate structure;
索引已分析
SQL> select lf_rows from index_stats;
LF_ROWS
———-
4
可以看到确实仅记录了4条记录。
我们尝试利用此部分行索引:
SQL> set autotrace on;
SQL> select count(*) from test where t2=’Y';
COUNT(*)
———-
4
Execution Plan
———————————————————-
Plan hash value: 1950795681
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 3 | 43 (12)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST | 2 | 6 | 43 (12)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – filter(“T2″=’Y')
Note
—–
- dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
171 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
没有如预期地使用索引,我们加上hint 再试试
SQL> select /*+ index(test ind_t2y) */ count(*) from test where t2=’Y';
COUNT(*)
———-
4
Execution Plan
———————————————————-
Plan hash value: 2501600095
—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 6 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | IND_T2Y | 98705 | | 1 (0)| 00:00:01 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – filter(“T2″=’Y')
Note
—–
- dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在使用部分行索引的情况下逻辑读大大下降了。
在不加hint的情况下优化器似乎永远无法做出正确的选择,即便修改了CBO相关的参数:
SQL> alter system set optimizer_index_cost_adj=1;
System altered.
SQL> select t2 from test where t2=’Y';
T
-
Y
Y
Y
Y
Execution Plan
———————————————————-
Plan hash value: 1357081020
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 2 | 6 | 43 (12)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 2 | 6 | 43 (12)| 00:00:01 |
————————————————————————–
仅在where 子句中指定了case when then模式时,优化器自觉地使用了该部分行索引:
SQL> select * from test where case t2 when ‘Y’ then t2 end =’Y';
T1 T
———- -
100001 Y
100002 Y
100003 Y
100004 Y
Execution Plan
———————————————————-
Plan hash value: 837354983
—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 2 | 32 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2 | 32 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T2Y | 1 | | 1 (0)| 00:00:01 |
—————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(CASE WHEN “T2″=’Y’ THEN “T2″ END =’Y')
Note
—–
- dynamic sampling used for this statement
Statistics
———————————————————-
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
650 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
部分行索引在特定情况下(譬如某表中仅少数特定行有大量查询更新操作)可以发挥非常巨大的作用。
- 部分行索引使用介绍
- 部分行索引使用介绍
- 索引介绍和使用
- PostgreSql索引的使用4--表达式索引和部分索引
- 数据库索引介绍及使用
- 数据库索引介绍及使用
- ORACLE索引介绍和使用
- 数据库索引介绍及使用
- 数据库索引介绍及使用
- Mysql索引介绍与使用
- 数据库索引介绍及使用
- ORACLE索引介绍和使用
- 数据库索引介绍及使用
- ORACLE索引介绍和使用
- 数据库索引介绍及使用
- ORACLE索引介绍和使用
- ORACLE索引介绍和使用
- hive索引简单使用介绍
- Mysql limit 优化,百万至千万级快速分页 复合索引的引用并应用于轻量级框
- Transact-SQL 调试器配置(帮助文档)
- 区间线段树-poj 3468-A Simple Problem with Integers
- autocad安装
- ( standard c libraries translation )memcpy家族
- 部分行索引使用介绍
- C#中Application.StartupPath和System.Environment.CurrentDirectory的区别
- FeatureLayer到本地shapefile文件
- Java实现统计项目代码行!
- Android应用如何监听自己是否被卸载及卸载反馈功能的实现(第二版)
- 自己的工作计划-备忘
- LINUX编程学习笔记(十四) 创建进程与 父子进程内存空间
- Effective C++条款8,9析构中的异常和虚拟函数
- 关于pkg-config和libtool的相关知识链接 200