有些业务会出现一些极端倾斜的情况，如有的流程状态，1表示新建，2表示流程中，14表示流程结束。系统允许几年，99%的数据流程状态是14。索引也是占空间的，且索引大了之后高度也会增加IO，此时可以对少量的数据建索引。
sql> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/sql Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
sql> drop table test;
sql> create table test as select * from dba_objects;sql> select t.temporary,count(1) from test t group by t.temporary;
T COUNT(1)
- ----------
Y 425
N 90060
sql> create index ind_t_temporary on test(temporary) nologging;
sql> exec dbms_stats.gather_table_stats(user,'TEST',METHOD_OPT=>'for all columns size skewonly');
sql> analyze index ind_t_temporary validate structure;
sql> select s.btree_space,s.lf_rows,s.height
from index_stats s
where s.name = upper('ind_t_temporary');
BTREE_SPACE LF_ROWS HEIGHT
----------- ---------- ----------
1319372 90485 2
sql> set autotrace traceonly
sql> select * from test where temporary='Y';
已选择425行。
已用时间: 00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 2248635872
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 380 | 38000 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 380 | 38000 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_TEMPORARY | 380 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEMPORARY"='Y')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
122 consistent gets
0 physical reads
0 redo size
46843 bytes sent via sql*Net to client
779 bytes received via sql*Net from client
30 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
425 rows processed
sql> select * from test where temporary='N';
已选择90060行。
已用时间: 00: 00: 10.20
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90097 | 8798K| 367 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST | 90097 | 8798K| 367 (1)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TEMPORARY"='N')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7251 consistent gets
0 physical reads
0 redo size
4153723 bytes sent via sql*Net to client
66504 bytes received via sql*Net from client
6005 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90060 rows processed
sql> set autotrace off
sql> drop index ind_t_temporary;
sql> create index ind_t_temporary on test(case temporary when 'Y' then 'Y' end) nologging;
sql> analyze index ind_t_temporary validate structure;
sql> select s.btree_space,s.height
from index_stats s
where s.name = upper('ind_t_temporary');
BTREE_SPACE LF_ROWS HEIGHT
----------- ---------- ----------
7996 425 1
sql> set autotrace traceonly
sql> select * from test t where (CASE t.temporary WHEN 'Y' THEN 'Y' END)='Y'; 已选择425行。 已用时间: 00: 00: 00.07 执行计划 ---------------------------------------------------------- Plan hash value: 2248635872 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 905 | 90500 | 59 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 905 | 90500 | 59 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_T_TEMPORARY | 362 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(CASE "TEMPORARY" WHEN 'Y' THEN 'Y' END ='Y') 统计信息 ---------------------------------------------------------- 38 recursive calls 0 db block gets 128 consistent gets 0 physical reads 0 redo size 23847 bytes sent via sql*Net to client 779 bytes received via sql*Net from client 30 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 425 rows processed sql> select * from test t where (CASE t.temporary WHEN 'N' THEN 'N' END)='N'; 已选择90060行。 已用时间: 00: 00: 10.60 执行计划 ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 905 | 90500 | 368 (1)| 00:00:05 | |* 1 | TABLE ACCESS FULL| TEST | 905 | 90500 | 368 (1)| 00:00:05 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(CASE "T"."TEMPORARY" WHEN 'N' THEN 'N' END ='N') 统计信息 ---------------------------------------------------------- 8 recursive calls 0 db block gets 7253 consistent gets 0 physical reads 0 redo size 4153723 bytes sent via sql*Net to client 66504 bytes received via sql*Net from client 6005 sql*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 90060 rows processed
