> > Using an index may not help: > > > > 1- an index is stored without regard to physical disk location, so using it > > will > create a huge amount of random disk IO. Whereas a NATURAL scan follows > the table. > > It depends. Primary key may be stored more-or-less in regard to physical disk > location. And the measure how good is INDEX vs NATURAL scan can be > available to the optimizer (index clustering factor).
Without the knowledge of way that Primary Keys map to storage order an assumption based on same would be just as bad a assuming that UDF are by default deterministic, no? Further, wouldn't an Index clustering factor really be of little value as the number of exceptions to the physical vs. primary key order increase? Since any exception to key order would result in random IO, which can quickly kill system performance vs. natural scan. > >> Index usage optimization II. > >> IS NOT NULL should use index. It is equivalent with >= min_value > >> or <= max_value based on index direction > > > > I don't think this is possible. > > > > With an MVCC it is possible for all rows to have both a NULL and NOT > > NULL values stored in the field index, so reading each rows is > > required. But as noted in #1 above, reading by index can lead to > > significant disk IO/degradation > > Who cares if the statistics tells us that NULLs are 95% of all keys? Please clarify your context for "Who cares", I don't quite follow your point. Sean ------------------------------------------------------------------------------ "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available. Simple to use. Nothing to install. Get started now for free." http://p.sf.net/sfu/SauceLabs Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
