Archive for select

Indexing NULL column values

Posted in Computación with tags , , , , , , , , on 1/August/2010 by Alex Pérez

After reading some great articles from Burleson Consulting about the “is null/is not null” predicates I decided to make some additional tests. It’s a very common mistake to write SQL with this kind of predicates since the NULL values aren’t included in regular indexes (B*Tree, not function-based), so almost every “IS NULL” results in a TABLE FULL SCAN.

I want to test how much a index can improve a IS [NOT] NULL predicate measuring the #requests per minute that a legacy box can serve. In the old days this server was a mission-critical server, but nowadays is retired as test server and can be compared to a very low end box (P3-512M ram).
Well, I measure the time that the select statement takes to execute (using JMeter)  instead of cost because a 30% reduction cost does not mean a 30% time reduction, and usually the end-user speak in terms of time, and there are many “tricks” like moving a table to the KEEP_POOL that can speed-up SQL execution although the cost is the same.
Continue reading