overhead of null values in an index

Hi, IDS 7.30.UC7 I want to add an index to a table which has approx. 100,000 rows. The index will be on one column, a varchar(1,30) and I'd really like to make the index unique. However, currently 99.99% of the rows in the table have NULL in this column, its about to start being used. Searches on this index will be a mixtures of column = "my_value" and column matches "a nice pattern". Over time the column will be updated with values for about 1/3 of the existing rows. I have three questions;- 1. What effect will these nulls have on the efficiency/space required for the index? 2. Would ordering the column descending give any benefit? I was think along the lines of the null keys being at the back end of the index and this having some effect on performance. 2. Is there any simple way of creating the unique index constraint but allowing the nulls to be duplicated? One of the few pluses of a flat file based system I worked on back in pre-history, was that you added the keys into the indexes manually, you didn't have to put a key in every index for every record if you didn't want to. -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf .

Hi Tony, > I want to add an index to a table which has approx. 100,000 rows. The index > will be on one column, a varchar(1,30) and I'd really like to make the index ... do you mean varchar(30,1) or varchar(30,10) ? > unique. However, currently 99.99% of the rows in the table have NULL in > this column, its about to start being used. Searches on this index will be > a mixtures of column = "my_value" and column matches "a nice pattern". Over > time the column will be updated with values for about 1/3 of the existing > rows. In the end appr. 65% of all rows will have a NULL value, that is 65.000 rows. > I have three questions;- > > 1. What effect will these nulls have on the efficiency/space required for > the index? 1a.) I guess the optimizer will not use the index if your where condition looks like: column is null because the filterfactor (F) is too high ( would be 65% ). But you must know the number of data pages of your table ( npused ). Cost comparison for sequential scan vs. index lookup Seq. Scan: npused:systables + nrows:systables * 0.03 Index Lookup: F * ( levels:sysindexes + leaves:sysindexes - 1 + npused:systables + nrows:systables * 0.03 ) 1b.) Duplicate values are stored compressed. If you have a 2KB page size there will fit 402 NULL-value rows into a single page. There will be exactly 162 NULL-value pages in the leaf-level of the index and a single branch-node will be neccessary to track the 162 leaf pages. May I assume that the average fillfactor of your unique varchar values will be 15 characters ? ( I need the average fillfactor to calculate the number of resulting leaves. ) On this condition you will need 438 leaf pages to store the unique values if the index page fillfactor will be about 100%. The whole index will have 3 levels. Don't forget to run an UPDATE STATISTICS HIGH or MEDIUM for this column to ensure that the optimizer will know the correct number of NULL values !!!!! > 2. Would ordering the column descending give any benefit? I was think along > the lines of the null keys being at the back end of the index and this > having some effect on performance. No, the ordering will not affect a regular search. > 2. Is there any simple way of creating the unique index constraint but > allowing the nulls to be duplicated? > > One of the few pluses of a flat file based system I worked on back in > pre-history, was that you added the keys into the indexes manually, you > didn't have to put a key in every index for every record if you didn't want > to. You might do the same with your database. Every column that allows NULL values is a candidate to be swapped out. Normally it makes no sense if you have just a few NULL values. In your situation it might be better and if the values that you intend to store in the column should be unique it will become a MUST. Your current table schema looks like: primary_key other_columns varchar-column Create an additional table and reuse the primary_key of your current table: current_table new_table ------------- ------------ primary_key primary_key ( and foreign key ) other_columns varchar-column NOT NULL UNIQUE Now you can create the unique index on the new table and there's no need to store NULL values. Don't worry about the new table. It's cheaper to create the new table than to store the NULL values in the current_table. Best regards -- Stefan Weideneder Phone: +49 89/3565478-2 --------------- --- Fax: +49 89/3565478-3 ------------- ------ mailto:/stefan@weideneder.de --- -------- http://www.weideneder.de -----