Creating an Extremely Large Index in Oracle

Question

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

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... :(

Answer 1

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.

Answer 2

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 /tmp directory in Unix or %TEMP% folded in Windows : the directory / folder itself is permanent, but the data stored within it are temporary.

REUSE means do not fail if the file already exists (usually used when the filename points to a raw device, like unformatted disk partition, to avoid OS file 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.

AUTOEXTEND ON means "grow the file if required". If you set it to off and 10Gb will be not enough for the sorting operation, the tablespace will not automatically grow, and the operation will fail.

EXTENT MANAGEMENT LOCAL means that the tablespace layout is stored in the tablespace itself (not in the system tables). Not sure about 11g, but in previous versions of Oracle this option was not available for temporary tablespaces.

ALTER USER me TEMPORARY TABLESPACE ts_tmp;

This makes the user me to use the newly created temp tablespace as a temporary storage medium

CREATE UNIQUE INDEX big_table_idx ON big_table ( record_id );

This just creates the index.

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.

Before dropping ts_tmp, revert the default temp tablespace for the user:

ALTER USER me TEMPORARY TABLESPACE temp; -- or whatever the previous query returned.

Answer 3

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.