索引瘦身_oracle_11g
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了索引瘦身_oracle_11g,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3313字,纯文字阅读大概需要5分钟。
内容图文
![索引瘦身_oracle_11g](/upload/InfoBanner/zyjiaocheng/526/a36e237e64bf4b56b50f47ad8f78e3ec.jpg)
> create table b
as select *
from dba_objects;
SQL>
select count(*)
from b
where status=
‘INACTIVE‘;
COUNT(*
)
----------
0
SQL> update b
set status=
‘INACTIVE‘ where rownum=
1;
update b set status=
‘INACTIVE‘ where rownum=
1
*
ERROR at line 1:
ORA-
12899: value too large
for column
"TT".
"B".
"STATUS" (actual:
8, maximum:
7)
SQL> alter table b modify STATUS varchar2(
10);
Table altered.
SQL> update b
set status=
‘INACTIVE‘ where rownum=
1;
1 row updated.
SQL>
commit;
Commit complete.
SQL> create index cc_ind_status on b(
case when STATUS=
‘INACTIVE‘ then
1 else null end);
Index created.
SQL>
select count(*)
from b
where STATUS=
‘INACTIVE‘;
COUNT(*
)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 749587668
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
|
0 | SELECT STATEMENT | |
1 |
7 |
345 (
1)|
00:
00:
05 |
|
1 | SORT AGGREGATE | |
1 |
7 | | |
|*
2 | TABLE ACCESS FULL| B |
14 |
98 |
345 (
1)|
00:
00:
05 |
---------------------------------------------------------------------------
select count(*)
from b
where (
case when STATUS=
‘INACTIVE‘ then
1 else null end)=
1;
SQL>
select count(*)
from b
where (
case when STATUS=
‘INACTIVE‘ then
1 else null end)=
1
COUNT(*
)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1978997881
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
|
0 | SELECT STATEMENT | |
1 |
3 |
1 (
0)|
00:
00:
01 |
|
1 | SORT AGGREGATE | |
1 |
3 | | |
|*
2 | INDEX RANGE SCAN| CC_IND_STATUS |
1 |
3 |
1 (
0)|
00:
00:
01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE
"STATUS" WHEN
‘INACTIVE‘ THEN
1 ELSE NULL END =
1)
Note
-----
-
dynamic sampling used
for this statement (level=
2)
二、对比大小
SQL> create index cc_ind_status_2 on b(status);
SQL>select SEGMENT_NAME,BYTES/1024/1024 from user_segments where SEGMENT_NAME like ‘CC_IND_STA%‘;
SEGMENT_NAME BYTES/1024/1024
-------------------- ---------------
CC_IND_STATUS .0625
CC_IND_STATUS_2 2
SQL> analyze table b compute statistics;
SQL> select INDEX_NAME,NUM_ROWS from user_indexes where INDEX_NAME like ‘CC_IND_STA%‘;
INDEX_NAME NUM_ROWS
------------------------------------------------------------ ----------
CC_IND_STATUS 1
CC_IND_STATUS_2 86341
索引瘦身_oracle_11g
标签:else 状态 comm arc oracle bytes 优点 ble altered
本文系统来源:https://www.cnblogs.com/lvcha001/p/13357038.html
内容总结
以上是互联网集市为您收集整理的索引瘦身_oracle_11g全部内容,希望文章能够帮你解决索引瘦身_oracle_11g所遇到的程序开发问题。
如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
来源:【匿名】