Firebird: reset statistics for all indices using `set statistics index :index_name` – via: What is index selectivity?
Posted by jpluimers on 2017/07/12
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?
The Wiert Corner - irregular stream of stuff 
Leave a comment