Results 1 to 4 of 4

Thread: Index creation

  1. #1
    Join Date
    Jun 2004

    Unanswered: Index creation


    I need to create an index over 332 million of records. The question is: How can I calculate the space to create the index? ... actually, oracle gives me the ORA-01652 error, it says me that there isn´t enough ispace in tablespace TEMP, then I need to know how much free space must be allocated in TEMP to create the index.

    Thanks a lot!

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >How can I calculate the space to create the index? .
    I would use basic multiplication; size of fields in Index * number of rows * 1.1 (fudge factor)
    Why are you trying to make this more difficult tan it is?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2003
    Greenville, SC (USA)
    Here's a script that I use for sizing indexes (approx)

    set verify off
    undefine pctfree
    undefine avg_length_of_indexed_cols
    undefine number_of_indexed_cols
    undefine num_of_ind_cols_over_127_bytes
    undefine number_of_rows

    select &&number_of_rows/(((100-&&pctfree)*(Value-161))
    /(100*(&&avg_length_of_indexed_cols+8+&&number_of_i ndexed_cols
    + &&num_of_ind_cols_over_127_bytes))) Blocks_Required
    from V$PARAMETER
    where Name = 'db_block_size';

    Your temp tablespace may need to extent ... Do you have it set up for autoextents ???


  4. #4
    Join Date
    Jul 2003
    increase the size of TEMP
    create a seperate TEMP area and assign that user to the new tablespace/datafile
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts