Oracle 只对部分行建索引

来源：互联网发布：中韩贸易逆差数据编辑：程序博客网时间：2024/05/21 22:47

有些业务会出现一些极端倾斜的情况，如有的流程状态，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

阅读全文

0 0