Oracle 11G - 插入时索引的性能影响 [英] Oracle 11G - Performance effect of indexing at insert

问题描述

验证没有PK /索引的插入记录加上以后创建的记录是否真的比插入PK /索引更快。

Verify if it is true that insert records without PK/index plus create thme later is faster than insert with PK/Index.

注意


这里的要点不是索引花费更多时间（很明显），而是总成本（不带索引插入+创建） index）高于（Insert with index）。因为我被教导插入没有索引并且稍后创建索引因为它应该更快。

Note
The point here is not about indexing takes more time (it is obvious), but the total cost (Insert without index + create index) is higher than (Insert with index). Because I was taught to insert without index and create index later as it should be faster.

Windows DELL Latitude核心i7 2.8GHz 8G内存和64位64位SSD HDD

Oracle 11G R2 64位

Windows 7 64 bit on DELL Latitude core i7 2.8GHz 8G memory & SSD HDD
Oracle 11G R2 64 bit

我被教导插入没有PK /索引的记录，插入后创建它们比插入PK /索引更快。

I was taught that insert records without PK/Index and create them after insert would be faster than insert with PK/Index.

然而，使用PK / Index的100万条记录插入实际上比之后创建PK / Index更快，大约4.5秒对6秒，下面的实验。通过将记录增加到300万（999000 - > 2999000），结果是相同的。

However 1 million record inserts with PK/Index was actually faster than creating PK/Index later, approx 4.5 seconds vs 6 seconds, with the experiments below. By increasing the records to 3 million (999000 -> 2999000), the result was the same.

• 表DDL如下。数据和
  索引的一个bigfile表空间。
  
  （测试了一个单独的索引表空间，具有相同的结果和较差的整体perforemace）


• 每次运行前刷新缓冲区/假脱机。


• 每次运行实验3次，并确保结果
  相似。

要刷新的SQL：

ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

问题

它是否真的如此插入PK /索引+ PK /索引后创建比插入PK /索引更快？

Question

Would it be actually true that "insert witout PK/Index + PK/Index creation later" is faster than "insert with PK/Index"?

我是否犯了错误或错过了实验中的某些条件？

Did I make mistakes or missed some conditions in the experiment?

TRUNCATE TABLE TBL2;
ALTER TABLE TBL2 DROP CONSTRAINT PK_TBL2_COL1 CASCADE;
ALTER TABLE TBL2 ADD  CONSTRAINT PK_TBL2_COL1 PRIMARY KEY(COL1) ;

SET timing ON
INSERT INTO TBL2
SELECT i+j, rpad(TO_CHAR(i+j),100,'A')
FROM (
  WITH DATA2(j) AS (
      SELECT 0 j FROM DUAL
      UNION ALL
      SELECT j+1000 FROM DATA2 WHERE j < 999000
  )
  SELECT j FROM DATA2
),
(
  WITH DATA1(i) AS (
      SELECT 1 i FROM DUAL
      UNION ALL
      SELECT i+1 FROM DATA1 WHERE i < 1000
  )
  SELECT i FROM DATA1
);
commit;

1,000,000 rows inserted.
Elapsed: 00:00:04.328 <----- Insert records with PK/Index

插入没有PK /索引的记录并在

Insert records without PK/Index and create them after

TRUNCATE TABLE TBL2;
ALTER TABLE &TBL_NAME DROP CONSTRAINT PK_TBL2_COL1 CASCADE;

SET TIMING ON
INSERT INTO TBL2
SELECT i+j, rpad(TO_CHAR(i+j),100,'A')
FROM (
  WITH DATA2(j) AS (
      SELECT 0 j FROM DUAL
      UNION ALL
      SELECT j+1000 FROM DATA2 WHERE j < 999000
  )
  SELECT j FROM DATA2
),
(
  WITH DATA1(i) AS (
      SELECT 1 i FROM DUAL
      UNION ALL
      SELECT i+1 FROM DATA1 WHERE i < 1000
  )
  SELECT i FROM DATA1
);
commit;
ALTER TABLE TBL2 ADD CONSTRAINT PK_TBL2_COL1 PRIMARY KEY(COL1) ;

1,000,000 rows inserted.
Elapsed: 00:00:03.454 <---- Insert without PK/Index

table TBL2 altered.
Elapsed: 00:00:02.544 <---- Create PK/Index

表DDL

Table DDL

CREATE TABLE TBL2 (
    "COL1" NUMBER,
    "COL2" VARCHAR2(100 BYTE),
    CONSTRAINT "PK_TBL2_COL1" PRIMARY KEY ("COL1")
) TABLESPACE "TBS_BIG" ;

推荐答案

如果修改表格更快，确实如此您不必修改一个或多个索引，也可能执行约束检查，但如果您必须添加这些索引，那么这也很大程度上无关紧要。您必须考虑对您希望影响的系统的完整更改，而不仅仅是它的一个部分。

It's true that it is faster to modify a table if you do not also have to modify one or more indexes and possibly perform constraint checking as well, but it is also largely irrelevant if you then have to add those indexes. You have to consider the complete change to the system that you wish to effect, not just a single part of it.

显然，如果要在表中添加单行这已经包含了数百万行，那么删除和重建索引是愚蠢的。

Obviously if you are adding a single row into a table that already contains millions of rows then it would be foolish to drop and rebuild indexes.

但是，即使你有一个完全空的表，你要添加几个百万行它可以更慢地推迟索引直到事后。

However, even if you have a completely empty table into which you are going to add several million rows it can still be slower to defer the indexing until afterwards.

这样做的原因是这样的插入最好用直接路径机制执行，当你使用直接路径插入到包含索引的表中，构建临时段，其中包含构建索引所需的数据（数据加rowid）。如果这些临时段比您刚刚加载的表小得多，那么它们也可以更快地扫描并构建索引。

The reason for this is that such an insert is best performed with the direct path mechanism, and when you use direct path inserts into a table with indexes on it, temporary segments are built that contain the data required to build the indexes (data plus rowids). If those temporary segments are much smaller than the table you have just loaded then they will also be faster to scan and to build the indexes from.

替代方案，如果你有表上的五个索引，是为了构建索引而在加载它之后发生五次全表扫描。

the alternative, if you have five index on the table, is to incur five full table scans after you have loaded it in order to build the indexes.

显然这里涉及巨大的灰色区域，但是干得好：

Obviously there are huge grey areas involved here, but well done for:

1. 质疑权威和一般经验法则，以及


2. 运行实际测试到在你自己的情况下确定事实。

编辑：

进一步的考虑因素 - 在删除索引时运行备份。现在，在紧急恢复之后，您必须有一个脚本来验证所有索引是否到位，当您的业务开始喘息以便系统重新启动时。

Further considerations -- you run a backup while the indexes are dropped. Now, following an emergency restore, you have to have a script that verifies that all indexes are in place, when you have the business breathing down your neck to get the system back up.

此外，如果您绝对决定在批量加载期间不维护索引，请不要删除索引 - 而是禁用它们。这样可以保留索引存在和定义的元数据，并允许更简单的重建过程。请注意，不要通过截断表意外重新启用索引，因为这会再次启用禁用的索引。

Also, if you absolutely were determined to not maintain indexes during a bulk load, do not drop the indexes -- disable them instead. This preserves the metadata for the indexes existence and definition, and allows a more simple rebuild process. Just be careful that you do not accidentally re-enable indexes by truncating the table, as this will render disabled indexes enabled again.

这篇关于Oracle 11G - 插入时索引的性能影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！