2

I have a great table with over 2.000.000.000 rows. Therefor i want to create an index on column1:

CREATE INDEX "SCHEMA"."INDEX_TABLE1" ON "SCHEMA"."TABLE1" ("STR1") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING
STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;

If i execute this my temp tablespace will be used and if its full (max 20gb) the creation will be aborted.

Where is my fault to create the index?

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
bladepit
  • 853
  • 5
  • 14
  • 29
  • 2
    Have you considered partitioning that table? That way you will immediately benefit from partition pruning when the column you use as partition key is in the where clause. Plus, you can use local indexes that will use less space and be faster when used in conjunction with partition pruning. – André Neves May 28 '13 at 16:29
  • 2
    Increasing the temporary tablespace may be your only option. Sorting a lot of data requires a lot of disk space. One possible exception is if the table was just loaded, and you know the data is already in order, you can create the index with the `NOSORT` option. – Jon Heller May 29 '13 at 05:12
  • thank you for your answers. i will try it in the future – bladepit May 31 '13 at 11:36

1 Answers1

2

From Oracle documentation:

Creating a Large Index

When creating an extremely large index, consider allocating a larger temporary tablespace for the index creation using the following procedure:

Create a new temporary tablespace using the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement.

Use the TEMPORARY TABLESPACE option of the ALTER USER statement to make this your new temporary tablespace.

Create the index using the CREATE INDEX statement.

Drop this tablespace using the DROP TABLESPACE statement. Then use the ALTER USER statement to reset your temporary tablespace to your original temporary tablespace.

Using this procedure can avoid the problem of expanding your usual, and usually shared, temporary tablespace to an unreasonably large size that might affect future performance.

Community
  • 1
  • 1
Jehy
  • 4,729
  • 1
  • 38
  • 55