Oracle 只对部分行建索引
发布时间：2020-12-12 16:28:54 所属栏目：百科 来源：网络整理
导读：有些业务会出现一些极端倾斜的情况，如有的流程状态，1表示新建，2表示流程中，14表示流程结束。系统允许几年，99%的数据流程状态是14。索引也是占空间的，且索引大了之后高度也会增加IO，此时可以对少量的数据建索引。 SQL select * from v$version; BANNER
|
有些业务会出现一些极端倾斜的情况，如有的流程状态，1表示新建，2表示流程中，14表示流程结束。系统允许几年，99%的数据流程状态是14。索引也是占空间的，且索引大了之后高度也会增加IO，此时可以对少量的数据建索引。 SQL> select * from v$version; 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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |