The Wiert Corner – irregular stream of stuff

A while ago, I needed to reset the index statistics for all indices in one our databases: a Firebird database.

I hadn’t done work like this in a while, so I made the cardinal mistake of Googling and using the first matching StackOverflow answer that seemed a perfect fit.

Boy was I wrong (:

In sql – My firebird query plan does not use correct index – Stack Overflow  I found the accepted answer (no less!) has this neat block to update index statistics for all indices:

    EXECUTE BLOCK
    AS
      DECLARE VARIABLE IDX VARCHAR(31);
    BEGIN
      FOR
        SELECT rdb$index_name FROM rdb$indices
        WHERE NOT rdb$index_name LIKE 'RDB$%'
        INTO :idx
      DO BEGIN
        EXECUTE STATEMENT 'update statistics ' || :idx
        WITH AUTONOMOUS TRANSACTION; 
      END
    END

The code consistently failed with this error:

    Script Error:
    
    Dynamic SQL Error
    SQL error code = -104
    Unexpected end of command - line 1, column 19

When I removed the lines inside the do begin … end block, then the outer statements execute fine.

Incidentally, column 19 is right at the space at the end of after the 'update statistics '. Which meant that either the string concatenation was wrong, or the statement itself.

And yes, I should have Googled the Firebird documentation instead, as What is index selectivity? has the correct statement that recalculates statistics for all indexes:

set term !! ;

EXECUTE BLOCK AS
declare variable 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 ; !!

–jeroen

Source: What is index selectivity?