在 Oracle 中创建超大索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2098069/
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
Creating an Extremely Large Index in Oracle
提问by Rudiger
Can someone look at the linked reference and explain to me the precise statements to run?
有人可以查看链接的参考资料并向我解释要运行的精确语句吗？
Oracle DBA's Guide: Creating a Large Index
Here's what I came up with...
这是我想出的...
CREATE TEMPORARY TABLESPACE ts_tmp
TEMPFILE 'E:\temp01.dbf' SIZE 10000M
REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL;
ALTER USER me TEMPORARY TABLESPACE ts_tmp;
CREATE UNIQUE INDEX big_table_idx ON big_table ( record_id );
DROP TABLESPACE ts_tmp;
Edit 1
编辑 1
After this index was created, I ran an explain plan for a simple query and get this error:
创建此索引后，我为一个简单的查询运行了一个解释计划并收到此错误：
ORA-00959: tablespace 'TS_TMP' does not exist
It seems like it's not temporary at all... :(
似乎它根本不是暂时的...... :(
回答by Quassnoi
CREATE TEMPORARY TABLESPACE ts_tmp TEMPFILE 'E:\temp01.dbf' SIZE 10000M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL;
CREATE TEMPORARY TABLESPACE ts_tmp TEMPFILE 'E:\temp01.dbf' SIZE 10000M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL;
This creates a temporary tablespace (an area on disk where the intermediate sort results will be stored). An index is a sorted set of data, and sorting needs lots of space.
这会创建一个临时表空间（磁盘上将存储中间排序结果的区域）。索引是一组经过排序的数据，排序需要大量空间。
"Temporary" here means that the data that is stored is temporary by nature, not that the tablespace itself is temporary. Think of it like of /tmpdirectory in Unixor %TEMP%folded in Windows: the directory / folder itself is permanent, but the data stored within it are temporary.
这里的“临时”是指存储的数据本质上是临时的，而不是表空间本身是临时的。把它想象成/tmp目录在Unix或%TEMP%折叠在Windows：目录/文件夹本身是永久的，但其中存储的数据是临时的。
REUSEmeans do not fail if the file already exists (usually used when the filename points to a raw device, like unformatted disk partition, to avoid OSfile management overhead). Instead, it will just open the file for writing and fill it with the new data. If not for this clause, the command would fail if the file with the given name existed.
REUSE意味着如果文件已经存在则不会失败（通常在文件名指向原始设备时使用，例如未格式化的磁盘分区，以避免OS文件管理开销）。相反，它只会打开文件进行写入并用新数据填充它。如果不是这个子句，如果给定名称的文件存在，则命令将失败。
AUTOEXTEND ONmeans "grow the file if required". If you set it to off and 10Gbwill be not enough for the sorting operation, the tablespace will not automatically grow, and the operation will fail.
AUTOEXTEND ON表示“根据需要增加文件”。如果设置为off，10Gb将不足以进行排序操作，表空间不会自动增长，操作将失败。
EXTENT MANAGEMENT LOCALmeans that the tablespace layout is stored in the tablespace itself (not in the system tables). Not sure about 11g, but in previous versions of Oraclethis option was not available for temporary tablespaces.
EXTENT MANAGEMENT LOCAL意味着表空间布局存储在表空间本身（而不是系统表中）。不确定11g，但在以前版本中，Oracle此选项不适用于临时表空间。
ALTER USER me TEMPORARY TABLESPACE ts_tmp;
ALTER USER me TEMPORARY TABLESPACE ts_tmp;
This makes the user meto use the newly created temp tablespace as a temporary storage medium
这使得用户me可以使用新创建的临时表空间作为临时存储介质
CREATE UNIQUE INDEX big_table_idx ON big_table ( record_id );
CREATE UNIQUE INDEX big_table_idx ON big_table ( record_id );
This just creates the index.
这只是创建索引。
DROP TABLESPACE ts_tmp;
DROP TABLESPACE ts_tmp;
This drops the temporary tablespace.
这会删除临时表空间。
Before running the script, figure out the current default tablespace:
在运行脚本之前，找出当前的默认表空间：
SELECT temporary_tablespace
FROM dba_users
WHERE username = 'ME'
Most probably, it will return TEMP.
最有可能的是，它会返回TEMP。
Before dropping ts_tmp, revert the default temp tablespace for the user:
在删除之前ts_tmp，恢复用户的默认临时表空间：
ALTER USER me TEMPORARY TABLESPACE temp; -- or whatever the previous query returned.
回答by Stellios
The HWM (high water mark) increases which depending on how you calculate usage appears full - to view the proper usage of TEMPORARY tablespaces use the V$SORT_USAGE and V$SORT_SEGMENT views.
HWM（高水位线）会增加，这取决于您如何计算使用量似乎已满 - 要查看 TEMPORARY 表空间的正确使用情况，请使用 V$SORT_USAGE 和 V$SORT_SEGMENT 视图。
回答by Omar Al Kababji
There is a small secret about oracle, table space, this one only increases in oracle, and will never decrease in size, what they are trying to do here is to avoid this situation, so it creates a temporary table space and use that table space to create the index and then drop it.
oracle有一个小秘密，表空间，这个只会在oracle中增加，永远不会减少，他们在这里试图做的就是避免这种情况，所以它创建了一个临时表空间并使用了那个表空间创建索引，然后删除它。
