 Query regarding Index? [message #213733] |
Fri, 12 January 2007 02:04  |
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  |
Frank Naude
Messages: 4581
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 |
flyboy
Messages: 1903
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 |
rleishman
Messages: 3728
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
