Oracle 只对部分行建索引
有些业务会出现一些极端倾斜的情况,如有的流程状态,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.lf_rows, 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
还没有评论,来说两句吧...