Unique index ??
I am having an argument with one of our developers. He wants me to create the following (unique) index on a new table. This just sounds ridiculous to me. Any comments / suggestions ? create unique index "stanley".iu_dao on "stanley".done_and_out (dao_scheme_chk,dao_category_chk,dao_operator_chk,dao_status_chk, dao_dt_from_chk,dao_dt_to_chk,dao_ta_code_chk,dao_rule_chk, dao_iteration_chk,dao_rej_code_chk,dao_batch_type_chk,dao_dr_type_chk, dao_units_chk,dao_clm_amt_chk,dao_tar_amt_chk,dao_ben_amt_chk); Here is the table schema: create table "stanley".done_and_out ( dao_pk serial not null , dao_scheme_chk char(4) not null , dao_category_chk smallint not null , dao_operator_chk char(8) not null , dao_status_chk char(1) not null , dao_dt_from_chk date not null , dao_dt_to_chk date not null , dao_ta_code_chk char(7), dao_rule_chk char(4), dao_iteration_chk smallint, dao_rej_code_chk smallint, dao_batch_type_chk smallint, dao_dr_type_chk char(3), dao_units_chk decimal(10,2), dao_clm_amt_chk char(20), dao_tar_amt_chk decimal(10,2), dao_ben_amt_chk char(20), dao_operator_ass char(8) not null , dao_status_ass char(1), dao_rej_code_ass smallint, dao_tar_amt_ass decimal(10,2), dao_ben_amt_ass decimal(10,2), dao_mem_amt_ass char(20) ) sending to informix-list
What about firing him and the guy (gal) who hired him ? ;) "Dirk Moolman" <DirkM@mxgroup.co.za> a 'crit dans le message de news:1107337089.0f1469ac8cfe1a844a9c35adcf022c39@teranews... > > I am having an argument with one of our developers. He wants me to > create the following (unique) index on a new table. This just sounds > ridiculous to me. > > Any comments / suggestions ? > > > create unique index "stanley".iu_dao on "stanley".done_and_out > > (dao_scheme_chk,dao_category_chk,dao_operator_chk,dao_status_chk, > dao_dt_from_chk,dao_dt_to_chk,dao_ta_code_chk,dao_rule_chk, > > dao_iteration_chk,dao_rej_code_chk,dao_batch_type_chk,dao_dr_type_chk, > dao_units_chk,dao_clm_amt_chk,dao_tar_amt_chk,dao_ben_amt_chk); > > > > Here is the table schema: > > create table "stanley".done_and_out > ( > dao_pk serial not null , > dao_scheme_chk char(4) not null , > dao_category_chk smallint not null , > dao_operator_chk char(8) not null , > dao_status_chk char(1) not null , > dao_dt_from_chk date not null , > dao_dt_to_chk date not null , > dao_ta_code_chk char(7), > dao_rule_chk char(4), > dao_iteration_chk smallint, > dao_rej_code_chk smallint, > dao_batch_type_chk smallint, > dao_dr_type_chk char(3), > dao_units_chk decimal(10,2), > dao_clm_amt_chk char(20), > dao_tar_amt_chk decimal(10,2), > dao_ben_amt_chk char(20), > dao_operator_ass char(8) not null , > dao_status_ass char(1), > dao_rej_code_ass smallint, > dao_tar_amt_ass decimal(10,2), > dao_ben_amt_ass decimal(10,2), > dao_mem_amt_ass char(20) > ) > > sending to informix-list
Dirk Moolman wrote: > I am having an argument with one of our developers. He wants me to > create the following (unique) index on a new table. This just sounds > ridiculous to me. > > Any comments / suggestions ? I want to ask the same question as TBP... Why? This index would only gain you ANYTHING as far as performance is concerned IFF all 16 columns are a) good filters and b) included in almost every query or where not all are then the included subset must be the first <N> columns in the index. And as TBP says, if it's needed for unique record verification then a) where's the UNIQUE CONSTRAINT to go with it and b) again WHY? That's a very odd table design, or a very odd object being modeled. Far better to create several indexes (unique or not) on subsets of this monstrous key column list that mirror common filter requirements in actual queries, sticking only to those columns that are 'good' filters. Art S. Kagel > create unique index "stanley".iu_dao on "stanley".done_and_out > > (dao_scheme_chk,dao_category_chk,dao_operator_chk,dao_status_chk, > dao_dt_from_chk,dao_dt_to_chk,dao_ta_code_chk,dao_rule_chk, > > dao_iteration_chk,dao_rej_code_chk,dao_batch_type_chk,dao_dr_type_chk, > dao_units_chk,dao_clm_amt_chk,dao_tar_amt_chk,dao_ben_amt_chk); > > > > Here is the table schema: > > create table "stanley".done_and_out > ( > dao_pk serial not null , > dao_scheme_chk char(4) not null , > dao_category_chk smallint not null , > dao_operator_chk char(8) not null , > dao_status_chk char(1) not null , > dao_dt_from_chk date not null , > dao_dt_to_chk date not null , > dao_ta_code_chk char(7), > dao_rule_chk char(4), > dao_iteration_chk smallint, > dao_rej_code_chk smallint, > dao_batch_type_chk smallint, > dao_dr_type_chk char(3), > dao_units_chk decimal(10,2), > dao_clm_amt_chk char(20), > dao_tar_amt_chk decimal(10,2), > dao_ben_amt_chk char(20), > dao_operator_ass char(8) not null , > dao_status_ass char(1), > dao_rej_code_ass smallint, > dao_tar_amt_ass decimal(10,2), > dao_ben_amt_ass decimal(10,2), > dao_mem_amt_ass char(20) > ) > > sending to informix-list