Oracle 只对部分行建索引

谁借莪１个温暖的怀抱￠ 2022-06-12 07:56 136阅读 0赞

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