unique indexes

I've got a fairly large database (150+ GB) with lots of indexes (30+ on some tables). The policy in the development group here has always been that all indexes will be unique. The result is that any index key has a unique key as the last column. The unique key is not, for the most part , used in any queries. It is just there to make the index unique. I've read every piece fo information I can find on the structure of a B+ tree index, how they are updated after inserts, updates and deletes and how Informix stores the index. If the unique key is not used in the query, how can it make the query any more efficient with the expanded index? The only result I can see from the unique column being added to the index is that the index will take up more space and increase I/O. The only documentation I find that supports this is in the Performance Guide for Informix Dynamic Server, Version 7.3 Feb. 1998 on page 4-22 under the heading "Avoiding columns with Duplicate Keys" as follows: To correct this problem, replace the index on the low-selectivity column with a composite index that has a higher selectivity. Use the low-selectivity col-umn as the leading column and a high-selectivity column as your second col-umn in the index. Question: If the low-selectivity column is the only criteria I have for executing a query or applying an update, exactly how is the composite index going to limit the number of rows that the server must search? The composite index limits the number of rows that the database server must search to locate and apply an update. You can use any second column to disperse the key values as long as its value does not change or changes at the same time as the real key. The shorter the second column the better, because its values are copied into the index and expand its size. Somebody at Informix must have had something in mind when they wrote this. I've had a case open with Informix for about a month now and no answer yet. Does anybody out there know what that something is? Any help would be greatly appreciated. Regards, Bill Dare Sent via Deja.com http://www.deja.com/

It's basically a balance between the cost of maintaining a non-unique index and the advantages of selecting via an index. While the key portion of an index is a true b-tree, the rowlist of a non-unique index is not. It is a simple list of the rows ordered by rowid. (Or at least I'm pretty sure that it is ordered.) Basically it looks like: KEY:rowid, rowid, rowid, rowid....... If the key is fairly selective, then maintenance on the entry is not a problem as all of the rowid list will fit on a single page. If the key is not selective, then there is a potential performance hit because it affects a large number of rows. From the select view point, you are correct. But there is a price to pay from an update/insert/delete standpoint. OK - why do we keep the list ordered? Basically to avoid having to access the same page multiple times while retrieving rows from an index. Does this mean that all rows should be unique? - Nope... Is this an issue that I'd personally worry about? - Probably not... What is the limit of selectivity that this might make a difference? --- HMMMMM It depends.... ;-) Probably not until I was running a couple of thousand duplicate rows. Like all things, there is a 'flip side' to the issue. If the index key is wide, then by having the index as a unique key when it could have been non-unique, means that there are fewer rows reflected on that index page. That means that using the first part of the index key is going to be less effecient in selects because more index pages will have to be examined. Also that means that key-only selects have much more work to do. (Opps.) So what all does this mean? If the main activity against the table is inserts/deletes/updates, then the more unique the index the better. However, if the table is rather static and used mainly for queries, then the non-unique index might make more sense. dareb@my-deja.com wrote: > I've got a fairly large database (150+ GB) with lots of indexes (30+ on > some tables). The policy in the development group here has always been > that all indexes will be unique. The result is that any index key has a > unique key as the last column. The unique key is not, for the most part > , used in any queries. It is just there to make the index unique. > I've read every piece fo information I can find on the structure of a B+ > tree index, how they are updated after inserts, updates and deletes and > how Informix stores the index. If the unique key is not used in the > query, how can it make the query any more efficient with the expanded > index? The only result I can see from the unique column being added to > the index is that the index will take up more space and increase I/O. > The only documentation I find that supports this is in the Performance > Guide for Informix Dynamic Server, Version 7.3 Feb. 1998 on page 4-22 > under the heading "Avoiding columns with Duplicate Keys" as follows: > > To correct this problem, replace the index on the low-selectivity column > with > a composite index that has a higher selectivity. Use the low-selectivity > col-umn > as the leading column and a high-selectivity column as your second > col-umn > in the index. > Question: If the low-selectivity column is the only criteria I have for > executing a query or applying an update, exactly how is the composite > index going to limit the number of rows that the server must search? > The composite index limits the number of rows that the > database server must search to locate and apply an update. > You can use any second column to disperse the key values as long as its > value > does not change or changes at the same time as the real key. The shorter > the > second column the better, because its values are copied into the index > and > expand its size. > > Somebody at Informix must have had something in mind when they wrote > this. I've had a case open with Informix for about a month now and > no answer yet. Does anybody out there know what that something is? Any > help would be greatly appreciated. > > Regards, > > Bill Dare > > Sent via Deja.com > http://www.deja.com/

Typo on my part.... "Does this mean that all rows should be unique? - Nope..." should have been "Does this mean that all indexes should be unique? - Nope..." Madison Pruet wrote: > It's basically a balance between the cost of maintaining a non-unique index > and the advantages of selecting via an index. While the key portion of an > index is a true b-tree, the rowlist of a non-unique index is not. It is a > simple list of the rows ordered by rowid. (Or at least I'm pretty sure that > it is ordered.) Basically it looks like: > > KEY:rowid, rowid, rowid, rowid....... > > If the key is fairly selective, then maintenance on the entry is not a > problem as all of the rowid list will fit on a single page. If the key is > not selective, then there is a potential performance hit because it affects > a large number of rows. From the select view point, you are correct. But > there is a price to pay from an update/insert/delete standpoint. > > OK - why do we keep the list ordered? Basically to avoid having to access > the same page multiple times while retrieving rows from an index. > > Does this mean that all rows should be unique? - Nope... > > Is this an issue that I'd personally worry about? - Probably not... > > What is the limit of selectivity that this might make a difference? --- > HMMMMM It depends.... ;-) Probably not until I was running a couple of > thousand duplicate rows. > > Like all things, there is a 'flip side' to the issue. If the index key is > wide, then by having the index as a unique key when it could have been > non-unique, means that there are fewer rows reflected on that index page. > That means that using the first part of the index key is going to be less > effecient in selects because more index pages will have to be examined. Also > that means that key-only selects have much more work to do. (Opps.) > > So what all does this mean? If the main activity against the table is > inserts/deletes/updates, then the more unique the index the better. However, > if the table is rather static and used mainly for queries, then the > non-unique index might make more sense. > > dareb@my-deja.com wrote: > > > I've got a fairly large database (150+ GB) with lots of indexes (30+ on > > some tables). The policy in the development group here has always been > > that all indexes will be unique. The result is that any index key has a > > unique key as the last column. The unique key is not, for the most part > > , used in any queries. It is just there to make the index unique. > > I've read every piece fo information I can find on the structure of a B+ > > tree index, how they are updated after inserts, updates and deletes and > > how Informix stores the index. If the unique key is not used in the > > query, how can it make the query any more efficient with the expanded > > index? The only result I can see from the unique column being added to > > the index is that the index will take up more space and increase I/O. > > The only documentation I find that supports this is in the Performance > > Guide for Informix Dynamic Server, Version 7.3 Feb. 1998 on page 4-22 > > under the heading "Avoiding columns with Duplicate Keys" as follows: > > > > To correct this problem, replace the index on the low-selectivity column > > with > > a composite index that has a higher selectivity. Use the low-selectivity > > col-umn > > as the leading column and a high-selectivity column as your second > > col-umn > > in the index. > > Question: If the low-selectivity column is the only criteria I have for > > executing a query or applying an update, exactly how is the composite > > index going to limit the number of rows that the server must search? > > The composite index limits the number of rows that the > > database server must search to locate and apply an update. > > You can use any second column to disperse the key values as long as its > > value > > does not change or changes at the same time as the real key. The shorter > > the > > second column the better, because its values are copied into the index > > and > > expand its size. > > > > Somebody at Informix must have had something in mind when they wrote > > this. I've had a case open with Informix for about a month now and > > no answer yet. Does anybody out there know what that something is? Any > > help would be greatly appreciated. > > > > Regards, > > > > Bill Dare > > > > Sent via Deja.com > > http://www.deja.com/

Thanks. Bill Dare In article <3A6DA0D7.415B0411@home.com>, Madison Pruet <mpruet@home.com> wrote: > It's basically a balance between the cost of maintaining a non-unique index > and the advantages of selecting via an index. While the key portion of an > index is a true b-tree, the rowlist of a non-unique index is not. It is a > simple list of the rows ordered by rowid. (Or at least I'm pretty sure that > it is ordered.) Basically it looks like: > > KEY:rowid, rowid, rowid, rowid....... > > If the key is fairly selective, then maintenance on the entry is not a > problem as all of the rowid list will fit on a single page. If the key is > not selective, then there is a potential performance hit because it affects > a large number of rows. From the select view point, you are correct. But > there is a price to pay from an update/insert/delete standpoint. > > OK - why do we keep the list ordered? Basically to avoid having to access > the same page multiple times while retrieving rows from an index. > > Does this mean that all rows should be unique? - Nope... > > Is this an issue that I'd personally worry about? - Probably not... > > What is the limit of selectivity that this might make a difference? --- > HMMMMM It depends.... ;-) Probably not until I was running a couple of > thousand duplicate rows. > > Like all things, there is a 'flip side' to the issue. If the index key is > wide, then by having the index as a unique key when it could have been > non-unique, means that there are fewer rows reflected on that index page. > That means that using the first part of the index key is going to be less > effecient in selects because more index pages will have to be examined. Also > that means that key-only selects have much more work to do. (Opps.) > > So what all does this mean? If the main activity against the table is > inserts/deletes/updates, then the more unique the index the better. However, > if the table is rather static and used mainly for queries, then the > non-unique index might make more sense. > > dareb@my-deja.com wrote: > > > I've got a fairly large database (150+ GB) with lots of indexes (30+ on > > some tables). The policy in the development group here has always been > > that all indexes will be unique. The result is that any index key has a > > unique key as the last column. The unique key is not, for the most part > > , used in any queries. It is just there to make the index unique. > > I've read every piece fo information I can find on the structure of a B+ > > tree index, how they are updated after inserts, updates and deletes and > > how Informix stores the index. If the unique key is not used in the > > query, how can it make the query any more efficient with the expanded > > index? The only result I can see from the unique column being added to > > the index is that the index will take up more space and increase I/O. > > The only documentation I find that supports this is in the Performance > > Guide for Informix Dynamic Server, Version 7.3 Feb. 1998 on page 4-22 > > under the heading "Avoiding columns with Duplicate Keys" as follows: > > > > To correct this problem, replace the index on the low-selectivity column > > with > > a composite index that has a higher selectivity. Use the low-selectivity > > col-umn > > as the leading column and a high-selectivity column as your second > > col-umn > > in the index. > > Question: If the low-selectivity column is the only criteria I have for > > executing a query or applying an update, exactly how is the composite > > index going to limit the number of rows that the server must search? > > The composite index limits the number of rows that the > > database server must search to locate and apply an update. > > You can use any second column to disperse the key values as long as its > > value > > does not change or changes at the same time as the real key. The shorter > > the > > second column the better, because its values are copied into the index > > and > > expand its size. > > > > Somebody at Informix must have had something in mind when they wrote > > this. I've had a case open with Informix for about a month now and > > no answer yet. Does anybody out there know what that something is? Any > > help would be greatly appreciated. > > > > Regards, > > > > Bill Dare > > > > Sent via Deja.com > > http://www.deja.com/ > > Sent via Deja.com http://www.deja.com/

In article <3A6DA0D7.415B0411@home.com>, Madison Pruet <mpruet@home.com> writes >It's basically a balance between the cost of maintaining a non-unique index >and the advantages of selecting via an index. While the key portion of an >index is a true b-tree, the rowlist of a non-unique index is not. It is a >simple list of the rows ordered by rowid. (Or at least I'm pretty sure that >it is ordered.) Basically it looks like: > >KEY:rowid, rowid, rowid, rowid....... > True... >If the key is fairly selective, then maintenance on the entry is not a >problem as all of the rowid list will fit on a single page. If the key is >not selective, then there is a potential performance hit because it affects >a large number of rows. From the select view point, you are correct. But >there is a price to pay from an update/insert/delete standpoint. > True. >What is the limit of selectivity that this might make a difference? --- >HMMMMM It depends.... ;-) Probably not until I was running a couple of >thousand duplicate rows. > But what about locking? Even with row key locking, locks are applied to indices. Remember Online v7 uses 'key value' locking. i.e. it locks the whole list!. This means no other rows on the same list can have their indices updated. i.e. all rows with the same index key are also locked! Imagine every update locking several hundred rows per index! ^^^^^^^^^ Of course each index will probably cause a different group of rows to be locked. So updating one row with thirty indexes could lock say 30x2,000 = 60,000 rows for each row updated! This is much worse then the row vs page level locking question! Try to explain to the user which 'other rows' get locked when updating one row when several composite indexes are involved! And of course you need to updating this information for users whenever you change the indexes on a table e.g. adding new columns which are then indexed! -- David Williams

> > > But what about locking? Even with row key locking, locks are applied > to indices. Remember Online v7 uses 'key value' locking. i.e. it locks > the whole list!. This means no other rows on the same list can have > their indices updated. i.e. all rows with the same index key are also > locked! Imagine every update locking several hundred rows per index! > ^^^^^^^^^ Huh???. I just ran the following test on one window create database cmpdb with log; create table tab1 ( col1 int, col2 char(20)) lock mode row; create index idx1 on tab1 (col1); begin work; insert into tab1 values (1, "test1"); insert into tab1 values (1, "test1a") And then while I was still in transaction from another window I did... insert into tab1 values (1,"test1b") Had no problems. The open transaction still had the key locks and the second window transaction completed without any problems. This is on 9.3 pre-beta. Based on your argument, I would have expected the second window to have locked on the open transaction that I had in the first window. Can you get me a test example of what you are seeing? > > Of course each index will probably cause a different group of rows > to be locked. So updating one row with thirty indexes could lock > say 30x2,000 = 60,000 rows for each row updated! > > This is much worse then the row vs page level locking question! > > Try to explain to the user which 'other rows' get locked when > updating one row when several composite indexes are involved! > And of course you need to updating this information for users > whenever you change the indexes on a table > e.g. adding new columns which are then indexed! > > -- > David Williams

In article <3A6E3FAD.5AC9B879@home.com>, Madison Pruet <mpruet@home.com> wrote: > > > > > > But what about locking? Even with row key locking, locks are applied > > to indices. Remember Online v7 uses 'key value' locking. i.e. it locks > > the whole list!. This means no other rows on the same list can have > > their indices updated. i.e. all rows with the same index key are also > > locked! Imagine every update locking several hundred rows per index! > > ^^^^^^^^^ > > Huh???. I just ran the following test on one window > > create database cmpdb with log; > create table tab1 ( > col1 int, > col2 char(20)) lock mode row; > create index idx1 on tab1 (col1);> > begin work; > insert into tab1 values (1, "test1"); > insert into tab1 values (1, "test1a")> > And then while I was still in transaction from another window I did... > > insert into tab1 values (1,"test1b")> > Had no problems. The open transaction still had the key locks and the second > window transaction completed without any problems. This is on 9.3 pre-beta. > Based on your argument, I would have expected the second window to have locked > on the open transaction that I had in the first window. Can you get me a test > example of what you are seeing? I took that test a little furthur, added an index to col2 and loaded enough data so a query would use the index and I was able to select and also delete as long as a sequential scan was not required. These are the locks from the first session in the insert transaction: 10222cd4 0 283ef138 10c328dc HDR+IX 100101 0 0 103a017c 0 283ef138 10c24e80 HDR+X 100101 301 K- 1 103b20b4 0 283ef138 105977cc HDR+X 100101 302 0 1051c514 0 283ef138 10bcc4e4 HDR+X 100101 302 K- 2 105977cc 0 283ef138 103a017c HDR+X 100101 301 K- 2 10bcc4e4 0 283ef138 103b20b4 HDR+X 100101 302 K- 1 10c24e80 0 283ef138 10222cd4 HDR+X 100101 301 0 10c328dc 0 283ef138 10db62e0 HDR+S 100002 217 0 10db62e0 0 283ef138 0 HDR+X 100002 216 0 These are the locks from the delete transaction: 10263c38 0 30af13c0 106037fc HDR+X 100101 318 K- 2 1043ad60 0 30af13c0 0 S 100002 217 0 10460e8c 0 30af13c0 104e9ddc HDR+X 100101 318 0 104e9ddc 0 30af13c0 105747c8 IX 100101 0 0 105747c8 0 30af13c0 1043ad60 HDR+IS 1000fb 0 0 106037fc 0 30af13c0 10460e8c HDR+X 100101 318 K- 1 318 is the rowid that I deleted and 301 and 302 are the rowids inserted in the first transaction. It appears to me that key value locking includes the rowid as part of the key, hence making every index unique from a locking standpoint. Any other thoughts on this. Regards, Bill > > > > > Of course each index will probably cause a different group of rows > > to be locked. So updating one row with thirty indexes could lock > > say 30x2,000 = 60,000 rows for each row updated! > > > > This is much worse then the row vs page level locking question! > > > > Try to explain to the user which 'other rows' get locked when > > updating one row when several composite indexes are involved! > > And of course you need to updating this information for users > > whenever you change the indexes on a table > > e.g. adding new columns which are then indexed! > > > > -- > > David Williams > > Sent via Deja.com http://www.deja.com/