Introduction In this article, we are going to see how Index Selectivity works in relational database systems and why the database Optimizer might choose to avoid using an index if the number of matching records is large. Index selectivity is inversely proportional to the number of index entries matched by a given value. So, a unique index has the highest…
#indexing
7 posts
22 Nov 2023
1 Jun 2017
The following two indexes are redundant in most SQL databases: It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is the first column of the composite index I_ACTOR_2 (it would be a different … Continue reading How to…
1 Nov 2016
I’ve recently discovered a rather significant performance issue on a productive Oracle 11g customer database. And I’m sure you have this issue too, which is why I’m documenting it here. This is a simplified representation of the setup at the customer site: ID PAYMENT_DATE TEXT ---------- ------------ ----------------------------------- 33803 21.05.16 DcTNBOrkQIgMtbietUWOsSFNMIqGLlDw... 29505 09.03.16 VIuPaOAQqzCMlFBYPQtvqUSbWYPDndJD... 10738 … Continue reading Why You…
7 Oct 2016
Adding the right index to speed up your queries is essential. But after a while, as your system grows, you may find yourself with tons of indexes, which all slow down writing to the database – as with each write to the table, the index needs to be updated as well in the same transaction. … Continue reading Does Your…
15 Jul 2016
It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove. – Antoine de Saint Exupéry in Terre des Hommes As SQL developers, we keep adding more and more indexes to our tables. Every time we run new queries that are potentially slow, a … Continue reading How to…
28 Apr 2015
I keep encountering situations where RDBMS users think that one second for query execution is anything near fast. Most recently, in this Stack Overflow question: Hibernate SQL In clause making CPU usage to 100% The poster’s original question was why a similar query executes in one second when executed in SQL Server Management Studio whereas … Continue reading Do Not…
11 Nov 2014
When writing DDL in SQL, you can specify a couple of constraints on columns, like NOT NULL or DEFAULT constraints. Some people might wonder, if the two constraints are actually redundant, i.e. is it still necessary to specify a NOT NULL constraint, if there is already a DEFAULT clause? The answer is: Yes! Yes, you … Continue reading Have You…