Ann W. Harrison wrote:

>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.
>

Nothing like getting bad information. Thanks for getting me straight on
this.

>>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.
>

I thinks this is where FB should take a que from Oracle. Oracle does
not create the index for
you so you have complete control over making sure the index runs
properly. If you forget
the index, it still works, it just runs dog slow.

>>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.
>
>

I am using the same techniques as I do in Oracle, which is

CREATE UNIQUE INDEX BSS_CONTROL_PK
ON BSS_CONTROL (BSS_MODULE, CONTROL_NO)
;
ALTER TABLE BSS_CONTROL
ADD CONSTRAINT BSS_CONTROL_PK PRIMARY KEY (BSS_MODULE, CONTROL_NO)
;

From what I understand, I shouldn't and don't need to create the index
first, just alter the table.



--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com



[Non-text portions of this message have been removed]