--- In IBObjects@yahoogroups.com, Hans <hhoogstraat@...> wrote:

>
> I don't think you can use 'SET STATISTICS INDEX indexname' in a stored
> procedure
>
> >

In the devel-list of FB, I've received this answer:
"As SET STATISTICS is DDL statement you can't use it in SP directly.
You can use EXECUTE STATEMENT for it but don't forget to commit
transaction as all DDL statements runs on commit."
So I've created a stored procedure in IBO, with a start transactino
and a commit transaction at the end of the code, and created a stored
procedure with EXECUTE STATEMENT in it:

procedure TfrmMain.actInxSetStatisticsExecute(Sender: TObject);
begin
with dmConnessione.stpInxSetStatistics,
dmConnessione.stpInxSetStatistics.IB_Transaction do
begin
if InTransaction then
Commit;
StartTransaction;
try
Execute;
Commit;
except
RollBack;
end;
end;
end;


CREATE PROCEDURE MM_INX_SET_STATISTICS
AS
DECLARE VARIABLE w_INDEX_NAME VARCHAR(31);
BEGIN
/* ricalcola le statistiche di selettività di tutti gli indici */
FOR SELECT RDB$INDEX_NAME
FROM RDB$INDICES
WHERE NOT (RDB$RELATION_NAME STARTING WITH 'RDB$')
INTO
:w_INDEX_NAME
DO BEGIN
EXECUTE STATEMENT 'SET STATISTICS INDEX ' || w_INDEX_NAME;
END
END

The problem I've faced is that you can't recreate the indexes using
alter index indexname INACTIVE / ACTIVE
because FB complains that turning off primary key is against database
integrity (for unique keys or foreign references, don't remember the
exact error message).
Any idea about how to solve it?
thanks
Marco Menardi