在Oracle临时表上放置索引是否安全?

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

我假设我们在谈论 GLOBAL TEMPORARY 桌子。

将一个临时表看作是由每个进程从系统字典中存储的模板动态创建和删除的多个表。 .

在 Oracle , DML A的 temporary table 影响所有进程,而表中包含的数据只影响使用它们的一个进程。

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

数据仓库 对于一个 临时表 (即其布局,包括列名和索引)对所有具有足够权限的人都可见。

这意味着 存在 的索引将影响您的进程以及使用该表的其他进程,从某种意义上说,任何修改 临时表 还必须修改索引。

数据 相反,包含在表中(也包含在索引中)只会影响创建它们的进程,甚至对其他进程都不可见。

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

• 创建两个 temporary tables 具有相同的列布局
• 其中一个的索引
• 根据进程使用索引表或非索引表

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

[编辑] 我看到你已经完善了你的问题,这里有一个稍微完善的答案:

来自:

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

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

如果您需要索引以便在事务范围内进行有效的处理,那么我想您必须在查询中显式地提示它,因为统计信息不会显示表的任何行。

你问的是两个不同的问题,索引和统计。 对于索引,可以,您可以在临时表上创建索引,这些索引将按常规进行维护。

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

嗯,我试过了,索引是可见的,在第二个会话中使用。如果确实需要索引,为数据创建新的全局临时表会更安全。

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

这是我运行的测试用例:

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

您还可以使用动态采样提示(10g):

选择/*+动态采样(3)*/val 从索引测试 其中VAL=1;

见 Ask Tom

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

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

第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>在索引测试(val)上创建唯一索引idx_val;
在索引测试(val)上创建唯一索引idx_val
*
第1行出错:
ORA-14452:尝试在已在使用的临时表上创建、更改或删除索引
SQL& GT;

仍然失败,首先必须断开会话1,否则必须截断表。

第1课:

SQL> truncate table index_test;
Table truncated.
SQL>

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

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

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