the cost of create index in sybase IQ

[Former Member]

I have a question about create index on sybase IQ.

I run sp_iqestspace procedure for the table,

(DBA)> call sp_iqestspace('psdss_dw.DW_AF_CUST_INFO',141488120,131072)

Execution time: 0.007 seconds

Cases                        Index size  Create time Msg

--------------------------------------------------------

Tablename: psdss_dw.DW_AF_CU                            

Rows: 141488120                                         

Columns/Row Width: 19/703                               

Min case                     39786463232 67h13m/CPU     

Avg case                     54706388992 123h13m/CPU    

Max case                     69626306560 179h14m/CPU    

RAW DATA                     99466148360 

It realy need 67h13m(min)  to create this index?

it realy need 99466148360 diskspace?

which factor decide the time spent?

[c_baker · Accepted solution]

The raw data calculation is simply (row width) * (row count) and only represents what the 'possible' raw data input size could be.  IQ will not use 99GB to store this table.  In reality it will be much less.  These storage estimates are the numbers to the left of the load time estimates and are probably high given that this is not a large amount of data and a narrow table.

If the table contains varchars, then this will further reduce the size as the estimates are based on the full width.  The data will also compress very well with IQ16's nbit-FP indexes, if only these are used by default to load the table.

As far as the rest of the calculation, this sp really needs to be checked.  The general guidelines for loading are ~20-40 GB/hour/CPU.  So if you are running even 4 cores, the worst-case scenario (99GB) would take just over an hour - assuming IQ is configured according to best practices for disk, memory, etc.

Chris