在 Oracle 临时表上放置索引是否安全？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/941094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it safe to put an index on an Oracle Temporary Table?
提问by EvilTeach
I have read that one should not analyze a temp table, as it screws up the table statistics for others. What about an index? If I put an index on the table for the duration of my program, can other programs using the table be affected by that index?
我读过不应该分析临时表，因为它会破坏其他人的表统计信息。索引呢？如果我在程序运行期间在表上放置一个索引，使用该表的其他程序是否会受到该索引的影响？
Does an index affect my process, and all other processes using the table? or Does it affect my process alone?
索引是否会影响我的进程以及使用该表的所有其他进程？或者它会单独影响我的过程吗？
None of the responses have been authoritative, so I am offering said bribe.
没有一个回复是权威的，所以我提供了贿赂。
回答by Quassnoi
Does an index effect my process, and all other processes using the table? or Does it effect my process alone?
索引是否会影响我的进程以及使用该表的所有其他进程？或者它会单独影响我的过程吗？
I'm assuming we are talking of GLOBAL TEMPORARYtables.
我假设我们在谈论GLOBAL TEMPORARY桌子。
Think of a temporary table as of multiple tables that are created and dropped by each process on the fly from a template stored in the system dictionary.
将临时表想象为多个表，每个进程从存储在系统字典中的模板动态创建和删除这些表。
In Oracle, DMLof a temporary tableaffects all processes, while data contained in the table will affect only one process that uses them.
In Oracle, DMLof atemporary table影响所有进程，而表中包含的数据只会影响使用它们的一个进程。
Data in a temporary tableis visible only inside the session scope. It uses TEMPORARY TABLESPACEto store both data and possible indexes.
a 中的数据temporary table仅在会话范围内可见。它用于TEMPORARY TABLESPACE存储数据和可能的索引。
DMLfor a temporary table(i. e. its layout, including column names and indexes) is visible to everybody with sufficient privileges.
DMLfor a temporary table（即它的布局，包括列名和索引）对拥有足够权限的每个人都是可见的。
This means that existenceof the index will affect your process as well as other processes using the table in sense that any process that modifies data in the temporary tablewill also have to modify the index.
这意味着索引的存在将影响您的进程以及使用该表的其他进程，因为任何修改表中数据的进程temporary table也必须修改索引。
Datacontained in the table (and in the index too), on the contrary, will affect only the process that created them, and will not even be visible to other processes.
数据表中包含的（以及指数也行），相反，只会影响创建它们的过程中，甚至不会对其他进程可见。
IF you want one process to use the index and another one not to use it, do the following:
如果您希望一个进程使用索引而另一个进程不使用它，请执行以下操作：
- Create two
temporary tableswith same column layout - Index on one of them
- Use indexed or non-indexed table depending on the process
- 创建两个
temporary tables具有相同列布局的 - 其中之一的索引
- 根据流程使用索引表或非索引表
回答by dpbradley
I assume you're referring to true Oracle temporary tables and not just a regular table created temporarily and then dropped. Yes, it is safe to create indexes on the temp tables and they will be used according to the same rules as a regular tables and indexes.
我假设您指的是真正的 Oracle 临时表，而不仅仅是临时创建然后删除的常规表。是的，在临时表上创建索引是安全的，它们将按照与常规表和索引相同的规则使用。
[Edit] I see you've refined your question, and here's a somewhat refined answer:
[编辑] 我看到你已经提炼了你的问题，这里有一个有点提炼的答案：
From:
从：
Oracle? Database Administrator's Guide
10g Release 2 (10.2)
Part Number B14231-02
"Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table."
“可以在临时表上创建索引。它们也是临时的，索引中的数据与基础表中的数据具有相同的会话或事务范围。”
If you need the index for efficient processing during the scope of the transaction then I would imagine you'll have to explicitly hint it in the query because the statistics will show no rows for the table.
如果您需要索引以在事务范围内进行有效处理，那么我想您必须在查询中明确提示它，因为统计信息将不显示表的行。
回答by Chi
You're asking about two different things, indexes and statistics. For indexes, yes, you can create indexes on the temp tables, they will be maintained as per usual.
你问的是两个不同的事情，索引和统计。对于索引，是的，您可以在临时表上创建索引，它们将照常维护。
For statistics, I recommend that you explicitly set the stats of the table to represent the average size of the table when queried. If you just let oracle gather stats by itself, the stats process isn't going to find anything in the tables (since by definition, the data in the table is local to your transaction), so it will return inaccurate results.
对于统计，我建议您明确设置表的统计信息，以表示查询时表的平均大小。如果你只是让 oracle 自己收集统计信息，统计进程不会在表中找到任何东西（因为根据定义，表中的数据对于你的事务是本地的），所以它会返回不准确的结果。
e.g. you can do:
例如你可以这样做：
exec dbms_stats.set_table_stats(user, 'my_temp_table', numrows=>10, numblks=>4)
exec dbms_stats.set_table_stats(user, 'my_temp_table', numrows=>10, numblks=>4)
Another tip is that if the size of the temporary table varies greatly, and within your transaction, you know how many rows are in the temp table, you can help out the optimizer by giving it that information. I find this helps out a lot if you are joining from the temp table to regular tables.
另一个提示是，如果临时表的大小变化很大，并且在您的事务中，您知道临时表中有多少行，您可以通过向优化器提供该信息来帮助优化器。如果您从临时表加入到常规表，我发现这会很有帮助。
e.g., if you know the temp table has about 100 rows in it, you can:
例如，如果您知道临时表中有大约 100 行，您可以：
SELECT /*+ CARDINALITY(my_temp_table 100) */ * FROM my_temp_table
SELECT /*+ CARDINALITY(my_temp_table 100) */ * FROM my_temp_table
回答by Plasmer
Well, I tried it out and the index was visible and used by the second session. Creating a new global temporary table for your data would be safer if you really need an index.
好吧，我试了一下，索引是可见的，并被第二个会话使用。如果您确实需要索引，则为您的数据创建一个新的全局临时表会更安全。
You are also unable to create an index while any other session is accessing the table.
当任何其他会话正在访问该表时，您也无法创建索引。
Here's the test case I ran:
这是我运行的测试用例：
--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;
回答by diederikh
回答by Wernfried Domscheit
You cannot create an index on a temporary table while it is used by another session, so answer is: No, it cannot affect any other process, because it is not possible.
当临时表被另一个会话使用时，您不能在临时表上创建索引，因此答案是：不，它不能影响任何其他进程，因为这是不可能的。
An existing Index affects only your current session, because for any other session the temporary table appears empty, so it cannot access any index values.
现有索引仅影响您当前的会话，因为对于任何其他会话，临时表显示为空，因此它无法访问任何索引值。
Session 1:
第 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 (while session 1 is still connected):
会话 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>
Back to session 1:
回到会话 1：
SQL> delete from index_test;
1 row deleted.
SQL> commit;
Commit complete.
SQL>
Session 2:
第 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>
still failing, you first have to disconnect session 1 or table has to be truncated.
仍然失败，您首先必须断开会话 1 或表必须被截断。
Session 1:
第 1 节：
SQL> truncate table index_test;
Table truncated.
SQL>
Now you can create the index in Session 2:
现在您可以在会话 2 中创建索引：
SQL> create unique index idx_val on index_test(val);
Index created.
SQL>
This index of course will be used by any session.
这个索引当然会被任何会话使用。
