> 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
