在 Oracle 临时表上放置索引安全吗？

Question

我读过，不应分析临时表，因为它会破坏其他表的统计信息。 指数怎么样？ 如果我在程序运行期间在表上放置索引，使用该表的其他程序会受到该索引的影响吗？

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

没有一个回复具有权威性，因此我提供上述贿赂。

Answer 1

您还可以使用动态采样提示（10g）：

select /*+ DYNAMIC_SAMPLING (3) */ val
来自索引测试
其中 val = 1；

请参阅询问 Tom

Answer 2

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

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

会话 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>

会话 2（会话 1 仍处于连接状态时）：

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>

会话 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 的连接，否则表必须被截断。

会话 1：

SQL> truncate table index_test;
Table truncated.
SQL>

现在您可以在会话 2 中创建索引：

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

当然该索引将被任何会话使用。

Answer 3

索引是否会影响我的进程以及使用该表的所有其他进程？ 或者它会单独影响我的流程吗？

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

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

在 Oracle 中，临时表的 DML 会影响所有进程，而表中包含的数据只会影响使用它们的一个进程。

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

具有足够权限的每个人都可以看到临时表的DML（即其布局，包括列名和索引）。

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

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

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

• 创建两个具有相同列布局的临时表
• 在其中一个上建立索引
• 使用索引表或非索引表，具体取决于过程

Answer 4

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

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

来自：

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

“索引可以在临时表上创建。它们也是临时的并且索引中的数据与数据具有相同的会话或事务范围在基础表中。”

如果您需要索引在事务范围内进行有效处理，那么我想您必须在查询中显式提示它，因为统计信息将显示表中没有行。

Answer 5

您问的是两个不同的事情：索引和统计数据。
对于索引，是的，您可以在临时表上创建索引，它们将照常维护。

对于统计，我建议您显式设置表的统计信息以表示查询时表的平均大小。 如果您只是让 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

Answer 6

好吧，我尝试了一下，索引是可见的，并被第二个会话使用。 如果您确实需要索引，则为数据创建新的全局临时表会更安全。

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

这是我运行的测试用例：

--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;