sql - COUNT() 基于表中列的影响

Question

编辑:

Database - Oracle 11gR2 - Over Exadata (X2)

---

正在为过去的问题制定问题调查报告，我对下面的情况有点困惑。

假设，我有一个表 MYACCT。有 138 列。它拥有 1000 万 条记录。每小时至少定期更新 1000 条记录(插入/更新/删除)。

主键是COL1 (VARCHAR2(18))(应用程序很少使用它，除了与其他表的联接)

COL2 VARCHAR2(9)) 还有另一个唯一索引。这是经常使用的应用程序。我之前所说的更新都是基于这两列发生的。尽管对此表进行任何 SELECT 操作，但请始终引用 COL2。因此 COL2 将是我们的兴趣。

我们在下面进行查询，

从 MYACCT 中选择计数(COL2)；/* 使用唯一列(不是 PK) */

结果没有问题，但我建议将其更改为

从 MYACCT 中选择计数(COL1)；/* 使用主索引

我只是计算了实际执行所需的时间

Query using the PRIMARY KEY was faster by `0.8-1.0 seconds always!

现在，我试图解释这种行为。只是起草这些查询背后的解释计划。

查询 1:(使用主键)

SELECT COUNT(COL1) FROM MYACCT;

计划:

SQL> select * from TABLE(dbms_xplan.display);
Plan hash value: 2417095184

---------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 | 11337   (1)| 00:02:17 |
|   1 |  SORT AGGREGATE               |         |     1 |            |          |
|   2 |   INDEX STORAGE FAST FULL SCAN| PK_ACCT |    10M| 11337   (1)| 00:02:17 |
---------------------------------------------------------------------------------

9 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      41332  consistent gets
          0  physical reads
          0  redo size
        210  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

---

查询2:(不使用主键)

SELECT COUNT(COL2) FROM MYACCT;

计划:

Plan hash value: 1130703739

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    10 |  7868   (1)| 00:01:35 |
|   1 |  SORT AGGREGATE               |          |     1 |    10 |            |          |
|   2 |   INDEX STORAGE FAST FULL SCAN| MYINDX01 |    10M|    95M|  7868   (1)| 00:01:35 |
------------------------------------------------------------------------------------------

9 rows selected.

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      28151  consistent gets
         23  physical reads
        784  redo size
        233  bytes sent via SQL*Net to client
        346  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

We can find in terms of Cost and Time the Query without primary key wins. Then how come the execution time of primary key is faster???

编辑:

SQL> select segment_name, bytes from dba_segments where segment_name in ('MYINDX01','PK_ACCT');
PK_ACCT               343932928
MYINDX01              234881024

Answer 1

您从 PK 索引中读取的数据比从另一个索引中读取的数据多。 COL1 是 VARCHAR2(18)，而 COL2 是 VARCHAR(9)，这并不一定意味着什么，但意味着 COL1 中的值可能始终比 COL2 中的值长。因此，它们将在表和索引中使用更多存储，并且索引扫描必须从 block 缓冲区和/或磁盘中提取更多数据以用于基于 PK 的查询。

执行统计数据显示；基于 PK 的查询有 41332 个一致获取，而更快的查询只有 28151 个一致获取，因此它在 PK 方面做了更多工作。段大小也显示了这一点 - 对于 PK，您需要阅读大约 328M，对于英国，只需 224M。

如果您发现 PK 版本有时运行得更快，则 block 缓冲区可能至关重要。在示例中，您展示了两个查询都命中了 block 缓冲区 - 23 次物理读取是微不足道的数字，如果索引数据没有一致地缓存，那么您可能会看到 41k 一致获取与 28k 物理读取，这可能会逆转明显的赢家，因为从磁盘的物理读取速度会更慢。如果连续运行两个查询显示其中一个查询更快，但颠倒它们运行的​​顺序则显示另一个查询更快，则通常会出现这种情况。

您不能将此概括为“PK 查询比 UK 查询慢”；这是因为您的具体数据。如果您的 PK 实际上是数字列，而不是保存数字的 VARCHAR2 列，那么您可能还会获得更好的性能，这绝不是一个好主意。