Commented by: @AlexPeshkoff

Rebuild of index is done using
alter index <name> inactive
alter index <name> active

In order to do it (or set statistics) for all indices is easily done in stored procedure, using select from rdb$indices and execute statement.

Taking it into an account, changed the priority.

Commented by: @livius2

>> Rebuild of index is done using
>> alter index <name> inactive
>> alter index <name> active

yes but :

1. we can not inactive system indexes
2. this are two comands but should be one
3. this can do some malfunction (degradate performance) if user run some query when index is inactive

this is good :)
In order to do it (or set statistics) for all indices is easily done in stored procedure, using select from rdb$indices and execute statement.

but can be included in SET STATISTIC statement
this could be more intuitive to use

Commented by: @pcisar

Except rebuild of system indices, this really could be easily done in stored procedure. But this is not single case when useful "higher level" actions could be performed or interesting information could be gathered from system tables using SP's or Views. I think that we could provide such useful extensions as build-in database metadata (another layer on top of system tables considered as standard part of the database) or at least as standard enhancement scripts that could be executed on new database to enhance it's capabilities.

Commented by: Smirnoff Serg (wildsery)

> I think that we could provide such useful extensions as build-in database metadata (another layer on top of system tables considered as standard part of the database) or at least as standard enhancement scripts...

Maybe some execute blocks in \examples\execute_blocks\ will be sufficient?

Commented by: @dyemanov

Why would you need to rebuild the indices at all?

Commented by: @pcisar

To make them dense (use index pages in full) again after mass update ?

Commented by: @cincuranet

AFAIK if you need to rebuild index "structure" you don't need to inactive it. Just calling active is good. So it works for PK too.

Commented by: @livius2

>>Jiri Cincura - [13/Nov/07 04:31 PM ]
>>AFAIK if you need to rebuild index "structure" you don't need to inactive it. Just calling active is good. So it >>works for PK too.

can this be used on working database (when users are connected)
or this can corrupt database?

Commented by: @cincuranet

Yes, you can use it on active database.

Commented by: @livius2

Really? - what with FireBird 1.5

I do this on working database
and now i can not create any new procedure
i get error something like "index was deleted ..."

Commented by: Diogo Andre Loff (dloff)

como baixa

Commented by: @luronumen

You can recalculate statistics for all indexes using this simple SQL statement:

SET TERM ^ ;
EXECUTE BLOCK
AS
DECLARE INDEX_NAME VARCHAR(31);
BEGIN
FOR SELECT RDB$INDEX_NAME FROM RDB$INDICES INTO :INDEX_NAME DO
EXECUTE STATEMENT 'SET STATISTICS INDEX ' || :INDEX_NAME || ';';
END^
SET TERM ; ^
COMMIT;