Ivan,
if you do the same with your dummy update as is done with set statistics,
then what is the value in doing so via the system tables?
Alan
-----Original Message-----
From: Ivan Prenosil [mailto:prenosil@...]
Sent: Monday, 7 October 2002 7:15
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Free Tool for SET STATISTICS on all Indices in
your DB


Recomputing index statistics, i.e.
SET STATISTICS INDEX MY_INDEX;
can be achieved by direct updating system table, i.e.

UPDATE RDB$INDICES
SET RDB$STATISTICS = -1
WHERE RDB$INDEX_NAME='MY_INDEX';
COMMIT;

I found this trick years ago, when struggling with optimizer
(the original idea was to convince the optimizer to choose better plans
by imposing my own index statistics.)


Example:
========

CREATE TABLE tab (id INTEGER);
CREATE INDEX ix ON tab(id);

INSERT INTO tab(id) VALUES (10);
INSERT INTO tab(id) VALUES (20);
INSERT INTO tab(id) VALUES (30);

COMMIT;

SELECT RDB$STATISTICS
FROM RDB$INDICES
WHERE RDB$INDEX_NAME='IX';

RDB$STATISTICS
====================
0 <---- old statistics value, not reflecting newly
inserted records.


UPDATE RDB$INDICES
SET RDB$INDEX_NAME=RDB$INDEX_NAME <---- just dummy update is enough
WHERE RDB$INDEX_NAME='IX';

SELECT RDB$STATISTICS FROM RDB$INDICES WHERE RDB$INDEX_NAME='IX';

RDB$STATISTICS
====================
0 <---- before commit nothing changes


COMMIT;

SELECT RDB$STATISTICS
FROM RDB$INDICES
WHERE RDB$INDEX_NAME='IX';

RDB$STATISTICS
====================
0.3333333432674408 <---- and after commit the selectivity is
recomputed.


SET AUTO OFF;
SET STATISTICS INDEX IX;

SELECT RDB$STATISTICS FROM RDB$INDICES WHERE RDB$INDEX_NAME='IX';
RDB$STATISTICS
====================
-1 <---- just to see what "real" SET STATISTICS is
doing before commit.



Ivan
http://www.volny.cz/iprenosil/interbase


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]