Index Creation - Space Estimate

[Former Member]

Hi All,

In our Production system we have one secondary Index missing, actually problem was ..we were applying some support packs sometime back which does this creation of Index ..but here we had option to skip this Index creation while applying support pack. Since the size of table(PPOIX) in Production system is very huge (11GB) … now I want to create this Index on Production …before doing I want to estimate ..how much space this new Index will take …so that accordingly I can extend tablespace . ….

I was thinking whether my calculation is correct …

On QAS system … the size of Index for this table is 99MB and table size is 165 MB …. Going by this … for 11GB table …I estimate it would take 6Gb space for Index …. Is this correct ? …

And what are the other things I should take care …while creating Index for 11 GB table in production … PSAPROLL is 9 GB freee ….i am dng it in Non Production hours …wat are the other things I should keep in mind while dng ? ..Thanks for any suggestions ..

Regards,

P

[Former Member]

Hi Priyank

Example

table - PPOIX

primary index: PPOIX~0 (Fields: "MANDT", "PERNR", "SEQNO", "ACTSIGN", "RUNID", "POSTNUM")

your new index: PPOIX~001 (Fields: "RUNID", "TSLIN")

To guess the final size you can:

- compare the index to an existing index (primary index) on the table, for example if PPOIX_{0 is 6gb, then PPOIX}001 will be smaller, just because both contain RUNID, but 001 only has TSLIN as additional field

- calculate the size of a key and multiply with the number of rows: ( sum(fields in bytes) + 10 bytes rowid ) * num_rows

Of course both are guessings and can be wrong under special circumstances.

You don't need to worry about PSAPROLL, but PSAPTEMP has to have index_size space free.

During the index build the table will be locked against DML (insert, update, delete will hang), so choose a period of low system activity, or do it with no users/jobs working. If you need to speedup index creation it is possible to create it directly with sqlplus with parallel and nologging. But this is for experienced DBAs only

Regards Michael

[former_member204746]

the safest way is to create your index on a copy of PRD, such as your refreshed QAS system. take a snapshot of tablespace usage before and after.

[stefan_koehler]

Hello,

unfortunately you didn't tell us your oracle version.

But you can use DBMS_SPACE.CREATE_INDEX_COST to estimate the index sizes.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#i1002198

But this procedure is only available in Oracle 10g and above.

Regards

Stefan

[Former Member]

Hi Stefhan,

Thanks for that.

Oracle version is 9.2.0.7.

Regards,

Priyank.

[stefan_koehler]

Hello,

ok then take a look at the this page and go to the section "Estimating the Size of an Index":

http://www.remote-dba.net/t_grid_rac_skip_scan_indexes.htm

Regards

Stefan