Index columns

Hello list! I've got following question: In my database exists a table with adresses. For the moment the prename, name, street,zip-code and city are indexed with her own indexes. This got the problem with double adresses. I want use an index over all these fields. So can add these index without problems or is it better to remove the existing indexes and create an unique index?? Whats about the additonal space I will need on HDD? Whats about the performance of this table? Its the main table in my system? Thanx in adance for any reply? Dirk Emmermacher Lowersaxony gymnastics federation

Dirk Emmermacher wrote: > > Hello list! > > I've got following question: > > In my database exists a table with adresses. For the moment the prename, > name, street,zip-code and city are indexed with her own indexes. This > got the problem with double adresses. I want use an index over all these > fields. So can add these index without problems or is it better to > remove the existing indexes and create an unique index?? > > Whats about the additonal space I will need on HDD? > > Whats about the performance of this table? > > Its the main table in my system? > > Thanx in adance for any reply? > > Dirk Emmermacher > Lowersaxony gymnastics federation Is there a need for the separate indices? It seems to me that a composite index might be a bit more efficient. One of the things that I've done before is to take a snapshot of all my queries that hit a particular table and run an SQL analysis to see if the index strategy is still valid. Admittedly, it's not something I do continually, but it came in handy when I wanted to add an index to an existing table. The new index ended up eliminating the need for two other indices. Just remember, your mileage may vary. -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

Dirk Emmermacher wrote: > Hello list! > > I've got following question: > > In my database exists a table with adresses. For the moment the prename, > name, street,zip-code and city are indexed with her own indexes. This > got the problem with double adresses. I want use an index over all these > fields. So can add these index without problems or is it better to > remove the existing indexes and create an unique index?? > > Whats about the additonal space I will need on HDD? > > Whats about the performance of this table? > > Its the main table in my system? If its the main table in your system, you'd better tread carefully. First, figure out why the table originally had the 5 individual indexes - presumably, you are not the original designer. If you can not get answers, try and figure out if these indexes are being used - one way to do this is to detach them (not required if you are using ids2000) and then monitor the activity on them over a representative period of time (a week?) by looking into sysptprof. Without knowing anything about your app., I can say that the table's indexes are not unusual. One can imagine querying by each of those columns (although "city" seems a bit much). Keep in mind that replacing of the 5 indexes by a single composite index can improve performance for specific queries, but will totally toast others (that do not use the left-most column of the index in theWHERE clause). If you do want to ensure uniqueness, you will need to add a unique index or a primary key on the columns that define uniqueness. You can then safely get rid of one of your original 5 indexes - the one that matches the left-most column of your composite unique index. HDD requirements are proportional to the index's column sizes. RTFM (Performance guide) for details. All the best, Rudy

Dirk Emmermacher wrote: > > Hello list! > > I've got following question: > > In my database exists a table with adresses. For the moment the prename, > name, street,zip-code and city are indexed with her own indexes. This > got the problem with double adresses. I want use an index over all these > fields. So can add these index without problems or is it better to > remove the existing indexes and create an unique index?? Keep the singleton indexes except the one that contains the column that starts the composite index since Informix can only use index keys from the beginning. > Whats about the additonal space I will need on HDD? This will cost you space. What about it? Estimate? Keylen (from dbschema or myschema -s watch out for the dbaccess bug if you have descending keys) times the number of rows (to calculate keylen yourself it is sum of key column lenghts plus 4 time 1.5). > Whats about the performance of this table? Performance for queries that include the first N columns from the new index as filters or join columns will improve, others will not. > Its the main table in my system? So? Art S. Kagel

Another option to look into is the advanced indexing technology of OMNIDEX from DISC (Dynamic Information Systems Corporation). (Attention: Promotional information follows. Please disregard if not interested in another solution.) OMNIDEX uses specialized Multidimensional and Aggregation Indexes to deliver unlimited multidimensional analysis by any number of criteria or attributes, and high-speed, dynamic data summaries that instantly aggregate the qualified information, without the need for aggregation tables. OMNIDEX Multidimensional Indexes are ideal for ad-hoc querying, they work in well for both high and low cardinality data, and they're very efficient in terms of build time and disk space. OMNIDEX layers on top of your existing Informix database, and it supports databases with tables up to 500M rows in size, including Oracle, SQL Server, Informix, Sybase, or flat files. How much OMNIDEX can help depends on your needs and environment. If you would be interested in a free performance analysis or more information, please contact me. Cheryl Grandy DISC cgrandy@disc.com 303 444-4000 www.disc.com/home OMNIDEX - for the fastest applications ever! In article <8f6em1$n1h$1@news.xmission.com>, NDSTURNERBUND@t-online.de (Dirk Emmermacher) wrote: > > Hello list! > > I've got following question: > > In my database exists a table with adresses. For the moment the prename, > name, street,zip-code and city are indexed with her own indexes. This > got the problem with double adresses. I want use an index over all these > fields. So can add these index without problems or is it better to > remove the existing indexes and create an unique index?? > > Whats about the additonal space I will need on HDD? > > Whats about the performance of this table? > > Its the main table in my system? > > Thanx in adance for any reply? > > Dirk Emmermacher > Lowersaxony gymnastics federation > Sent via Deja.com http://www.deja.com/ Before you buy.