What is the fastest way to build 2 or more indexes for a table?
Right now I'm using SQL and "Create index ... on table ..." but it looks
like I can only build one index at a time with this one statement. If I
have 3 indexes then I need 3 different Create Index statements, correct?

Also when creating and index or dropping an index, does EDB need to
rebuild the entire table (move data)? Or is it just adding/dropping the
index?

(With MySQL's Alter Table command I can build several indexes at a time.)

TIA

Dave

Dave,

I usually use a procedure for repeated code like creating an index.  Therefore I use a very similar procedure to create indexes.

procedure CreateIndex(const sIndexName, sTableName, sFieldName : String; sAscending : String = 'ASC');
var
         sSQL : String;
begin
         sSQL := 'CREATE INDEX "%s" ON "%s" (FieldName %s)';
         sSQL := Format(sSQL,[sIndexName, sTableName, sFieldName, sAscending]);

         EDBDatabase1.Execute(sSQL);
end;


Example of use:

CreateIndex('ndxAccountNo'    ,'Tabl1','AccountNo');
CreateIndex('ndxAccountName','Tabl1','AccountName','DEC');
CreateIndex('ndxAccountType' ,'Tabl1','AccountType');

Sorry I made a small mistake in the procedure.  Here is the correct one:


procedure CreateIndex(const sIndexName, sTableName, sFieldName : String; sAscending : String = 'ASC');
var
        sSQL : String;
begin
        sSQL := 'CREATE INDEX "%s" ON "%s" (%s %s)';
        sSQL := Format(sSQL,[sIndexName, sTableName, sFieldName, sAscending]);

        EDBDatabase1.Execute(sSQL);
end;

Abdulaziz Jasser wrote:
> Sorry I made a small mistake in the procedure.  Here is the correct one:
>
>
> procedure CreateIndex(const sIndexName, sTableName, sFieldName : String; sAscending : String = 'ASC');
> var
>          sSQL : String;
> begin
>          sSQL := 'CREATE INDEX "%s" ON "%s" (%s %s)';
>          sSQL := Format(sSQL,[sIndexName, sTableName, sFieldName, sAscending]);
>
>          EDBDatabase1.Execute(sSQL);
> end;
>

Abdulaziz,
     I was hoping there was some way in EDB to build more than one
index at a time with the same SQL command. If I have 3 indexes to build,
why traverse the table 3 times?

Dave

Dave,

<< What is the fastest way to build 2 or more indexes for a table? Right now
I'm using SQL and "Create index ... on table ..." but it looks  like I can
only build one index at a time with this one statement. If I have 3 indexes
then I need 3 different Create Index statements, correct? >>

Correct.

<< Also when creating and index or dropping an index, does EDB need to
rebuild the entire table (move data)? Or is it just adding/dropping the
index? >>

It's just adding or dropping the index.

<< (With MySQL's Alter Table command I can build several indexes at a time.)
>>

Unlike DBISAM and some other database engines, ElevateDB does not mix
indexes into the standard SQL 2003 statements because indexes are not really
part of the standard but rather an implementation detail.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dave,

<< I was hoping there was some way in EDB to build more than one index at a
time with the same SQL command. If I have 3 indexes to build, why traverse
the table 3 times? >>

One of the benefits to building each index separately is that the index
space is allocated in such a way that locality is at its highest for each
index.  Building all indexes at the same time involves either writing out
the index data to temporary files to preserve locality, and then moving the
data back into the one index file when done, or losing the benefits of the
locality.  The only way to resolve this in a way that satisfies both is to
have a separate index file per index, which is not in the cards for
ElevateDB currently.  However, it is something that will be present with the
EDB enterprise server, but in a "virtual" way since the enterprise server
will use a single file per database.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>but in a "virtual" way since the enterprise server
>will use a single file per database.

Is that a feature that will be reversed into the other versions of ElevateDB?

Roy Lambert

Roy,

<< Is that a feature that will be reversed into the other versions of
ElevateDB? >>

Unfortunately, no.  Single-file databases that are page-oriented don't do
well in a multi-user set-up due to the locking requirements.

--
Tim Young
Elevate Software
www.elevatesoft.com