| Need Help in creating Index [message #151755] |
Mon, 19 December 2005 01:07  |
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
|
Member |
|
|
Hi,
I have to create one index and i want to know certain things.
How much space this index requires while creating it ?
I have got the no. of rows (22318545) & average row length by using this below query and this returns 78.58 and with this i estimated it to be(78.58*22318545) 1.6 GB nearly 2 GB.
Is this the correct way to know this much space will be required to create the index ??
*************Query to get the average row length*************
SELECT AVG(LENGTH(STATUS)+LENGTH(SERVICE_OBJ_ID0)+LENGTH(DESCR)+LENGTH(OBJ_ID0)) FROM Table1
*************************************************************
Other thing is, How can i determine the value of ini_trans, Intial Extent & Next Extent size What factor i have to consider to set these values. My tablespace will be dictonary managaned. This is a existing tablespace.
Please help me in this
Thanks in advance
Chandan Singh
|
|
|
|
| Re: Need Help in creating Index [message #151824 is a reply to message #151755] |
Mon, 19 December 2005 06:31  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi,
About initrans, initial extent,next extents, I think you can leave these values to their defaults and it should be ok...in locally managed tablespaces they are automatically managed and do not require to be specified..
about knowing the space for the index, remember that index also has to store the ROWID of the row , in addition to the values of columns of the index.
is the index to be created on the 4 columns that you have mentioned here?
SELECT AVG(LENGTH(STATUS)+LENGTH(SERVICE_OBJ_ID0)+LENGTH(DESCR)+LENGTH(OBJ_ID0)) FROM Table1
For index, you have to consider ONLY the columns which you are indexing , not all columns.
Thanks,
N.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
