Home » SQL & PL/SQL » SQL & PL/SQL » Query regarding Index?
icon5.gif  Query regarding Index? [message #213733] Fri, 12 January 2007 02:04 Go to next message
yugamore
Messages: 23
Registered: December 2006
Junior Member
I have a table with around 10 million rows.
There are around six coulmns in this table which are frequetly queried.
All thses columns are NOT UNIQUE. If I have to create Index on these columns :

1) When I try to create composite index it given an error (ORA-01652 Unable to extend temp segment by 128 in tablespace xyz)

I don't want to create Index in any other Table space or don't want to add data file in the same table space....

How can I deal with this issue?

2) Also let me know if creating two indexes on three ,three columns each would help better??

Re: Query regarding Index? [message #213743 is a reply to message #213733] Fri, 12 January 2007 02:54 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Quote:
ORA-01652 Unable to extend temp segment by 128 in tablespace xyz


The index will be "temporary" while being created. Looks like you don't have enough space to create the index in tablespace xyz.
Re: Query regarding Index? [message #213759 is a reply to message #213733] Fri, 12 January 2007 03:59 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
1. Index is the additional structure containing values of containing rows (or functions on them) plus reference to data. It has to be stored somewhere. If you want to create the index in other tablespace than xyz, specify it in the index_attributes section of create index statement (TABLESPACE tblspc_name).
2.
Quote:
There are around six coulmns in this table which are frequetly queried.

Do you mean you FILTER result set by values of that six columns (use it in WHERE clause)? Then creating index is very helpful. It depends on your queries. If most of them use all six columns, use one index. If you can group them by different columns used, make index for each group (suppose you have at most 3 groups).
Re: Query regarding Index? [message #213933 is a reply to message #213759] Fri, 12 January 2007 17:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Like Frank said, XYZ is the TEMP tablespace. It is used to sort the columns before the index can be built. You may very well have plenty of space for the index to be stored, but if you cannot sort it, you can't build it.

Either get the DBA to add more TEMP space, or export the table, truncate, create the index on the empty table, then import.

Ross Leishman
Re: Query regarding Index? [message #213952 is a reply to message #213733] Fri, 12 January 2007 22:53 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
1. My mistake, must have overlooked that. Frank and Ross are right, your problem is in the TEMP tablespace size.
Previous Topic: INSERT INTO
Next Topic: External tables and character set
Goto Forum:
  


Current Time: Mon Dec 05 18:48:33 CST 2016

Total time taken to generate the page: 0.07420 seconds