Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

The Index You’ve Added is Useless. Why?

DZone's Guide to

The Index You’ve Added is Useless. Why?

· Java Zone
Free Resource

Navigate the Maze of the End-User Experience and pick up this APM Essential guide, brought to you in partnership with CA Technologies

Recently, at the office:

Bob: I’ve looked into that slow query you’ve told me about yesterday, Alice. I’ve added the indexes you wanted. Everything should be fine now

Alice: Thanks Bob. I’ll quickly check … Nope Bob, still slow, it didn’t seem to work

Bob: You’re right Alice! It looks like Oracle isn’t picking up the index, for your query even if I add an /*+INDEX(...)*/ hint. I don’t know what went wrong!?

And so, the story continues. Alice is frustrated because her feature doesn’t ship on time, Bob is frustrated because he thinks that Oracle doesn’t work right.

True story!

Bob Forgot about Oracle and NULL

Poor Bob forgot (or didn’t know) that Oracle doesn’t put NULL values in “ordinary” indexes. Think about it this way:

CREATE TABLE person (
  id            NUMBER(38)   NOT NULL PRIMARY KEY,
  first_name    VARCHAR2(50) NOT NULL,
  last_name     VARCHAR2(50) NOT NULL,
  date_of_birth DATE             NULL
);
 
CREATE INDEX i_person_dob ON person(date_of_birth);

Now, Bob thinks that his index solves all problems, because he verified if the index worked using the following query:

SELECT *
FROM   person
WHERE  date_of_birth > DATE '1980-01-01';

(of course, you generally shouldn’t SELECT *)

And the execution plan looked alright:

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON       |
|*  2 |   INDEX RANGE SCAN          | I_PERSON_DOB |
----------------------------------------------------

This is because Bob’s predicate doesn’t rely on NULL being part of the I_PERSON_DOB index. Unfortunately, Alice’s query looked more like this (simplified version):

SELECT 1
FROM   dual
WHERE  DATE '1980-01-01' NOT IN (
  SELECT date_of_birth FROM person
);

So, essentially, Alice’s query checked if anyone had their date of birth at a given date. Her execution plan looked like this:

-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|*  1 |  FILTER            |        |
|   2 |   FAST DUAL        |        |
|*  3 |   TABLE ACCESS FULL| PERSON |
-------------------------------------

As you can see, her query made a TABLE ACCESS FULL operation, bypassing the index. Why? It’s simple:

Even if our DATE '1980-01-01' value is or is not in the index, we’ll still have to check the whole table to see whether a single NULL value is contained in the date_of_birth column. Because, if there was a NULL value, the NOT IN predicate in Alice’s query would never yield TRUE or FALSE, but NULL.

Alice can solve this issue with NOT EXISTS

Alice can solve it easily herself, by replacing NOT IN through NOT EXISTS, a predicate that doesn’t suffer from SQL’s peculiar three-valued boolean logic.

SELECT 1
FROM   dual
WHERE  NOT EXISTS (
  SELECT 1
  FROM   person
  WHERE  date_of_birth = DATE '1980-01-01'
);

This new query now again yields an optimal plan:

>------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | SELECT STATEMENT  |              |
|*  1 |  FILTER           |              |
|   2 |   FAST DUAL       |              |
|*  3 |   INDEX RANGE SCAN| I_PERSON_DOB |
------------------------------------------

But the problem still exists, because what can happen, will happen, and Alice will have to remember this issue for every single query she writes.

Bob should just set the column to NOT NULL

The best solution, however is to simply set the column to NOT NULL:

ALTER TABLE person
MODIFY date_of_birth DATE NOT NULL;

With this constraint, the NOT IN query is exactly equivalent to the NOT EXISTS query, and Bob and Alice can be friends again.

Takeaway: How to find “bad” columns?

It’s easy. The following useful query lists all indexes that have at least one nullable column in them.

SELECT
  i.table_name,
  i.index_name,
  LISTAGG(
    LPAD(i.column_position,  2) || ': ' ||
    RPAD(i.column_name    , 30) || ' '  ||
    DECODE(t.nullable, 'Y', '(NULL)', '(NOT NULL)'),
    ', '
  ) WITHIN GROUP (ORDER BY i.column_position)
    AS "NULLABLE columns in indexes"
FROM user_ind_columns i
JOIN user_tab_cols t
ON (t.table_name, t.column_name) =
  ((i.table_name, i.column_name))
WHERE EXISTS (
  SELECT 1
  FROM user_tab_cols t
  WHERE (t.table_name, t.column_name, t.nullable) =
       ((i.table_name, i.column_name, 'Y'       ))
)
GROUP BY i.table_name, i.index_name
ORDER BY i.index_name ASC;

When run against Bob and Alice’s schema, the above query yields:

TABLE_NAME | INDEX_NAME   | NULLABLE columns in indexes
-----------+--------------+----------------------------
PERSON     | I_PERSON_DOB | 1: DATE_OF_BIRTH (NULL)

Use this query on your own schema now, and go through the results, carefully evaluating if you really need to keep that column nullable. In 50% of the cases, you don’t. By adding a NOT NULL constraint, you can tremendously speed up your application!

Thrive in the application economy with an APM model that is strategic. Be E.P.I.C. with CA APM.  Brought to you in partnership with CA Technologies.

Topics:

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}