At 11:25 AM 9/30/2002 -0400, Thomas Miller wrote:

>We currently write our software for Oracle and I am very familiar with
>how Oracle uses indecies for speed. We are currently converting our
>software to
>also run on FB.

Great! One step in the right direction.

>Back in the 5.x days of IB, I was told never to create indecies made up
>of multiple columns. Is that still true?

Never was. When you create multi-segment indexes, you should put the
least selective column(s) first, and the most selective last. That
will help compression, thus reduce the size of the index.

>Any other insights would also be welcome.

Be careful with foreign key constraints. They automatically create
indexes and some of those indexes are pretty dreadful. Consider that
you have a product that you sell to men, women, and dogs. You don't
want any children or cats as purchasers. So, you create a table of
customer types with three entries and a foreign key constraint that
insures that all customer records are associated with men, women, and
dogs. That creates an index on the customer record that contains only
three distinct values - it gets pretty slow when you have more than
a couple hundred thousand customers.

>When I create a primary key, the engine makes two indexes. One as a primary
>key and one that it not. They both are indecies of the primary key. Is
>this a bug?

Are you sure you didn't create the other index. Primary and
foreign key constraints automatically create indexes, so there
is no need to declare indexes on those columns.




Regards,

Ann
www.ibphoenix.com
We have answers.