Count can only work on one column at a time.
If you are doing this sort of task, check out analyse_idx at www.iiug.org.
This is a 4gl program which does all of this for you except a little
different. It looks at your system catalogues and optionally your source
code and reports a score for each column in each table (or for a table you
select) rating how it would perform as an index. Things it looks at:
Number of unique values per column
number of occurences of this column/table in your where clauses
Possible joins of this column to other columns (same name and type) in
other tables
Existing index information.
As its documentation indicates it is a crowbar not a scalpel, gives you a
starting point for further analysis, not a script you can execute to create
indices.
cheers
j.
At 07:14 PM 10/27/97 -0600, you wrote:
}I have an undocumented database I am trying to reverse-engineer. In
}particular I want to find potential candidate keys. Typically I use the
}following syntax (for keys consisting of a single column):
}
}If select count(*) from t; = select count(distinct c) from t; then c is
}a potential candidate key for table t.
}
}To search for potential composite candidate keys I try:
}
} select count(distinct c1, c2) from t;
}
}This works with a number of other DBMS where the semantics of the query
}is to count the number of distinct (c1,c2) pairs. Informix, however,
}gives me a syntax error. When I omit the count aggregate, however, it
}works as expected.
}
} select distinct c1, c2 from t;
}
}
}Is there something wrong with the way I am trying to find potential
}composite candidate keys? Is there another way to do this in SQL?
}
}Thanks for any help,
}--
}Robert Winkler
}U.S. Army Research Laboratory
}Beckman Institute, Room 4357
}405 North Matthews Ave, Urbana, IL 61801
}
}