在Oracle临时表上创建索引是否安全？

Question

我读到过不应该分析临时表，因为这会破坏其他表的统计信息。那索引呢？如果在我的程序运行期间在表上放置一个索引，是否会影响使用该表的其他程序?

索引会影响我的进程和所有使用该表的其他进程吗？ 还是仅影响我的进程？

没有权威的回复，所以我提供了贿赂。

- EvilTeach

Answer 1

索引是否会影响我的进程以及使用该表的所有其他进程？还是仅影响我的进程？

我假设我们正在讨论 GLOBAL TEMPORARY 表。

将临时表视为从存储在系统字典中的模板上即时由每个进程创建和删除的多个表。

在 Oracle 中，对于临时表的 DML 将影响所有进程，而数据仅会影响使用它们的单个进程。

临时表中的数据仅在会话范围内可见。它使用 TEMPORARY TABLESPACE 存储数据和可能的索引。

临时表的 DML（包括列名和索引）对具有足够权限的所有人都可见。

这意味着索引的存在将影响您的进程以及使用该表的其他进程，因为修改临时表中的数据的任何进程也必须修改索引。

相反，表中包含的数据（以及索引中包含的数据）仅会影响创建它们的进程，并且甚至不对其他进程可见。

如果您想要一个进程使用索引，而另一个进程不使用索引，请执行以下操作：

- 创建两个具有相同列布局的临时表 - 其中一个建立索引 - 根据进程使用带或不带索引的表

- Quassnoi

Answer 2

我假设你指的是真正的 Oracle 临时表，而不仅仅是创建临时并随后删除的常规表。是的，在临时表上创建索引是安全的，并且它们将按照与常规表和索引相同的规则使用。

[编辑] 我看到您已经完善了问题，这里是一个稍微完善的答案：

来自：

Oracle® Database Administrator's Guide
10g Release 2 (10.2)
Part Number B14231-02

"可以在临时表上创建索引。这些索引也是临时的，索引中的数据与基础表中的数据具有相同的会话或事务范围。

如果您需要在事务范围内对索引进行有效处理，则我想您将不得不在查询中明确提示它，因为统计信息将显示表中没有行。"

- dpbradley

---

索引将存在于所有会话中（因此可能会影响它们的处理并且可能会给它们带来问题，如果它是唯一索引而他们期望非唯一数据，或者即使只是减慢他们的插入/更新）。 此外，添加/删除索引将对表进行简短的锁定。 同样，您可以在临时表上收集统计信息，但它们将被假定适用于该表上的所有查询，这可能或可能不适合您的情况。 - Gary Myers

不，索引与表格以完全相同的方式存储 - 在每个会话中单独存储，因此即使索引是唯一的，在不同会话之间也不会发生冲突。 显然，添加/删除索引会锁定表格，但您有多频繁地添加/删除索引呢？ 是的，统计信息适用于临时表的全局范围，这就是为什么有时在查询全局临时表时需要使用CARDINALITY提示的原因。 - Jeffrey Kemp

好的，Gary和Jeff，你们都理解这个问题，但似乎有不同的意见。 - EvilTeach

观点无关紧要，结果才是最重要的——试一试就知道了！ - Jeffrey Kemp

顺便提一下，我制作了许多应用程序，这些应用程序特别依赖于GTT上的索引对于每个会话都是本地的这一事实。 - Jeffrey Kemp

Answer 3

您在询问两个不同的事情，即索引和统计信息。 对于索引，是的，您可以在临时表上创建索引，它们将像通常一样进行维护。

对于统计信息，我建议您明确设置表的统计信息，以表示查询表时的平均大小。如果您让Oracle自行收集统计信息，统计过程将找不到表中的任何内容（因为根据定义，表中的数据仅限于您的事务范围内），因此它会返回不准确的结果。

例如，您可以执行以下操作： exec dbms_stats.set_table_stats(user, 'my_temp_table', numrows=>10, numblks=>4) 另一个提示是，如果临时表的大小差异很大，并且在您的事务中，您知道临时表中有多少行，则可以通过提供该信息来帮助优化器。如果您从临时表连接到常规表，则我发现这非常有帮助。

例如，如果您知道临时表中大约有100行，则可以： SELECT /*+ CARDINALITY(my_temp_table 100) */ * FROM my_temp_table

- Chi

---

我只是在询问索引。我了解统计信息。临时表可能会被多个进程同时使用。在表上放置统计信息可能会产生负面影响。 - EvilTeach

我将测试使用基数提示。 非常感谢 +1 - EvilTeach

Answer 4

我试过了，第二个会话可以看到并使用索引。如果你真的需要一个索引，为你的数据创建一个新的全局临时表会更安全。

同时，当其他会话正在访问该表时，你也无法创建索引。

以下是我运行的测试用例：

--first session
create global temporary table index_test (val number(15))
on commit preserve rows;

create unique index idx_val on index_test(val);

--second session
insert into index_test select rownum from all_tables;
select * from index_test where val=1;

- Plasmer

Answer 5

你也可以使用动态采样提示（10g）：

select /*+ DYNAMIC_SAMPLING (3) */ val from index_test where val = 1;

参见Ask Tom

- diederikh

Answer 6

当临时表正在被另一个会话使用时，您无法在其上创建索引，因此答案是：不行，它不能影响任何其他进程，因为这是不可能的。

现有的索引仅影响您当前的会话，因为对于任何其他会话，临时表都为空，因此无法访问任何索引值。

会话1：

SQL> create global temporary table index_test (val number(15)) on commit preserve rows;
Table created.
SQL> insert into index_test values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>

Session 2 (当第一次连接仍然保持时):

SQL> create unique index idx_val on index_test(val);
create unique index idx_val on index_test(val)
                               *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
SQL>

返回第1个会话：

SQL> delete from index_test;
1 row deleted.
SQL> commit;
Commit complete.
SQL>

第二节：

SQL> create unique index idx_val on index_test(val);
create unique index idx_val on index_test(val)
                               *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
SQL>

仍然无法通过，您首先必须断开会话1或清空表格。

会话1：

SQL> truncate table index_test;
Table truncated.
SQL>

现在你可以在第二个会话中创建索引：

SQL> create unique index idx_val on index_test(val);
Index created.
SQL>

这个索引当然会被任何会话使用。

- Wernfried Domscheit