Oracle 我们需要重建索引的一种情况-如何避免它？

读过汤姆的书后，我知道总的来说没有理由重建索引，所以我们没有。但是，我们有一个特定的索引确实可以从重建中受益。

由于它是一个日期分区表上的本地分区索引，我们通常可以重建每个分区一次 (一旦它可能不会得到更多的行添加)，这工作，但我们宁愿避免重建，如果可能的话。此外，测试表明，向分区添加多达10% 个额外行可以 “要求” 重建。

重建有助于性能的原因是它显著减少了叶块的数量。为了确切地了解发生了什么，我创建了有问题的表的两个副本，并以相同的顺序将相同的行插入到每个表中。然后，我在一个上重建了索引分区。USER_IND_PARTITIONS中的相关列 (最上面的一行是重建的分区):

BLEVEL  LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY  CLUSTERING_FACTOR NUM_ROWS
3 672990  86854495 1   1    67274260  86854495
3 891511  87980511 1   1    68388974  87980511
复制

我怀疑造成这种情况的原因是索引前导列中的数据的分布方式-它具有幂律分布。此外，它是另一个表的唯一标识符，并且标识符是按顺序分配的; 这意味着经常看到的实体倾向于获得较低的数字，这将倾向于使索引向左倾斜。但是，我尝试随机分配标识符; 它稍微提高了重建前的性能，但重建仍然有所帮助。

表定义:

CREATE TABLE test_bad
( id number(*,0) NOT NULL ENABLE,
  parent_id varchar2(64) NOT NULL ENABLE,
  group_id number(*,0) NOT NULL ENABLE,
  entity_id number(*,0) NOT NULL ENABLE,
  loc_start number(10,2) NOT NULL ENABLE,
  loc_len number(10,2) NOT NULL ENABLE,
  rating number(7,6) NOT NULL ENABLE,
  parent_timestamp date NOT NULL ENABLE,
  rank number(*,0) NOT NULL ENABLE,
  status number(1,0) NOT NULL ENABLE,
  section_id number(*,0) NOT NULL ENABLE,
  type_id number(*,0) NOT NULL ENABLE,
  manual_entry number(1,0) NOT NULL ENABLE,
  security_id number(*, 0) not null enable
)
  PARTITION BY RANGE (parent_timestamp) INTERVAL (interval'1'day)
 (PARTITION VALUES LESS THAN (to_date(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
复制

索引定义:

CREATE INDEX test_bad_entity_idx
  ON test_bad (entity_id, parent_timestamp, type_id, group_id, parent_id, security_id,
               loc_start+loc_len, loc_start, loc_len, rating, rank,
               section_id, status, manual_entry)
  COMPRESS 6 LOCAL NOLOGGING;
复制

显示性能差异的跟踪输出 (先重建，然后非重建-SQL是相同的，除了表和索引名称):

select /*+ INDEX(T1 TEST_GOOD_ENTITY_IDX) INDEX(T2 TEST_GOOD_ENTITY_IDX) */
distinct t1.parent_id
from test_good t1, test_good t2
where t1.entity_id=1178
and t2.entity_id=586
and t1.parent_id=t2.parent_id
and t1.group_id=t2.group_id
and t1.parent_timestamp=t2.parent_timestamp
and t2.loc_start between (t1.loc_start+t1.loc_len) and (t1.loc_start+t1.loc_len+0.16)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0        299          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       10      0.05       0.21        557        558          0        4348
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.09       0.26        557        857          0        4348

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      4348       4348       4348  HASH UNIQUE (cr=558 pr=557 pw=0 time=217256 us cost=23 size=19072 card=298)
      7012       7012       7012   PARTITION RANGE ALL PARTITION: 1 1048575 (cr=558 pr=557 pw=0 time=227977 us cost=22 size=19072 card=298)
      7012       7012       7012    HASH JOIN  (cr=558 pr=557 pw=0 time=227954 us cost=22 size=19072 card=298)
     29183      29183      29183     INDEX RANGE SCAN TEST_GOOD_ENTITY_IDX PARTITION: 1 1048575 (cr=255 pr=255 pw=0 time=7741 us cost=11 size=24000 card=750)(
object id 104485)
     40862      40862      40862     INDEX RANGE SCAN TEST_GOOD_ENTITY_IDX PARTITION: 1 1048575 (cr=303 pr=302 pw=0 time=130375 us cost=11 size=24000 card=750
)(object id 104485)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       557        0.00          0.16


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.04          0        292          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       10      0.07       0.31        853        854          0        4348
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.11       0.35        853       1146          0        4348

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      4348       4348       4348  HASH UNIQUE (cr=854 pr=853 pw=0 time=314130 us cost=31 size=19456 card=304)
      7012       7012       7012   PARTITION RANGE ALL PARTITION: 1 1048575 (cr=854 pr=853 pw=0 time=327280 us cost=30 size=19456 card=304)
      7012       7012       7012    HASH JOIN  (cr=854 pr=853 pw=0 time=327261 us cost=30 size=19456 card=304)
     29183      29183      29183     INDEX RANGE SCAN TEST_BAD_ENTITY_IDX PARTITION: 1 1048575 (cr=384 pr=384 pw=0 time=131085 us cost=15 size=24544 card=767)
(object id 104491)
     40862      40862      40862     INDEX RANGE SCAN TEST_BAD_ENTITY_IDX PARTITION: 1 1048575 (cr=470 pr=469 pw=0 time=251236 us cost=15 size=24544 card=767)
(object id 104491)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       853        0.00          0.25
复制

现在，你可以合理地说，这两种说法仍然相当快，这是真的。但是，我们运行许多类似的语句，需要查看更多的行，甚至对于较小的语句，这种性能上的一致差异也会增加-我们一直使用此索引。(这些测试也在使用ssd的Oracle文件的服务器上运行，这在我们的客户中并不常见。)

索引通常具有 “平衡” 点，即在插入伪随机数据的情况下，已使用和未使用 (“浪费”) 空间之间的比率。通常，您会看到大约70-80% 的利用率。如果您重建该索引，那么是的，索引将更加紧凑 (即，100-pc free百分比满，因此通常为90)，但这在某种程度上是索引的人为表示。

所以我认为你不需要重建你的索引...但是，当然，它确实建议您寻求替代方案来提高该查询的性能。

有些事情 * 探索 * (我没有确定的解决方案，因为我们没有所有可用的数据)

1) 其中有多少是本地分区扫描？如果您有几十个或几百个分区，那么向下根，每个分区分支的开销可能是工作的很大一部分。也许全球指数可能会更好 (当然，这伴随着你需要平衡的其他问题)

2) 您在索引中有 “额外” 列 (我认为避免访问表)。您 * 可能 * 在拥有更少的列以获得更好的索引密度方面获得一些好处 (以不得不访问表为代价)。

3) 备用/附加索引以支持联接的 “后半部分”。通过这个我的意思是，当前的索引与 'entity' 领先，以支持 “where t1.entity _ id = 1178”，但也许第二个索引允许通过不同的前导路径进入t2，例如 (entity_id，parent_timestamp，group_id，parent_id，loc_start) 来满足这一点:

and t2.entity_id=586
and t2.parent_id=...
and t2.group_id=...
and t2.parent_timestamp=...
and t2.loc_start between ... and ...
复制

尽可能高效。