Index statistics update

[Ertan Küçükoglu]

Hello,

I am using FirebirdSQL 5 latest.

I found the script below and have been using it for a while.

I just started to wonder if it needs to be run in a transaction and if that transaction needs to be committed.

  SET TERM ^ ;
  EXECUTE BLOCK
  AS
  DECLARE INDEX_NAME VARCHAR(63);
  BEGIN
    FOR SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG=0 INTO :INDEX_NAME DO
      EXECUTE STATEMENT 'SET STATISTICS INDEX ' || '"' || :INDEX_NAME || '"' || ';';
  END^
  SET TERM ; ^

Thanks & Regards,

Ertan

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 02.07.2025 12:44:
> I just started to wonder if it needs to be run in a transaction and if that
> transaction needs to be committed.

(Almost) no statement in Firebird can be executed outside of transaction.

Yes, real statistic update happen only on commit.

--
WBR, SD.

[Mark Rotteveel]

Almost everything you execute in Firebird requires a transaction, and
you should end transactions as soon as you're done. Given this modifies
information in the system tables, I think you'll need to end with a
commit, I think (though I haven't verified if a rollback would retain
the modified statistics, I would be surprised if it did).

Mark
--
Mark Rotteveel