If your index matches better, it will be used.
indices already used cannot be disabled
plans cannot change after some index is enabled.
Of course, this is known.
So I don't see much practical sense in allowing disable/enable for system
indices.
I attached to this issue a database for 2.5 (and it's backup) with 300
existed views, as well as a bat-file that creates 100 views in two ways
(in a copy of the source database):
1) with the disabled system indexes in RDB$DEPENDENCIES
2) with the enabled system indexes in RDB$DEPENDENCIES
Disabled indices are RDB$INDEX_27 (RDB$DEPENDENT_NAME) and RDB$INDEX_28
(RDB$DEPENDED_ON_NAME).
Before these actions, 2 indexes are created in RDB$DEPENDENCIES:
CREATE INDEX SYS$DEPENDENCIES$FOR_DELETE ON RDB$DEPENDENCIES
(RDB$DEPENDENT_NAME,RDB$DEPENDENT_TYPE);
CREATE UNIQUE INDEX SYS$DEPENDENCIES$UNQ ON RDB$DEPENDENCIES
(RDB$DEPENDENT_NAME,RDB$DEPENDED_ON_NAME,RDB$FIELD_NAME,RDB$DEPENDENT_TYPE,RDB$DEPENDED_ON_TYPE);
The results of my tests:
- on the CPU i5-5257U (2015 release)
the difference is almost no ~ 15 seconds, and does not depend
where the database is located (on SSD or external SATA drive)
- on the CPU Xeon E5507 (2010 release)
with the disabled indices: ~ 1 min.
with the enabled indices: ~ 30 min.
I also checked that "new" indexes are actually used when creating views.
Checked after creating some view using isql:
- when deleting the "old" index RDB$INDEX_27 or RDB$INDEX_28 - they
are normally deleted.
- when deleting a "new" index, for example
SYS$DEPENDENCIES$FOR_DELETE - an exception is throw "Statement failed,
SQLSTATE = HY000 index unexpectedly deleted"
What could be the reason for such a big difference in the above tests,
if more optimal indexes should be used?
Also it is not clear why the difference is not visible on modern
processors and is huge on not very new processors?
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
