Oracle 11 Index only for part of the data

I'm using a table a table called T and have a column called C_I (Index) and C_D (Data).

Now I would like to only have the row in the index if C_D = null.

CREATE INDEX T_INDEX ON T(C_I) STORAGE(BUFFER_POOL KEEP);

How do I get some WHERE C_D IS NULL clause into this creat开发者_开发问答e statement?

Let me first make sure I understand the question correctly:

• You want to speed-up SELECT .. WHERE C_D IS NULL but you do not want to speed-up any of the queries that search for a non-NULL C_D.
• You also want to make sure no "unnecessary" non-NULL values are in the index, to save space.

If that understanding is correct, then what you need is a functional index. I'e. an index on a function on a field, not a field itself...

CREATE INDEX T_IE1 ON T (CASE WHEN C_D IS NULL THEN 1 ELSE NULL END) COMPRESS

...which you would then query as...

SELECT * FROM T WHERE (CASE WHEN C_D IS NULL THEN 1 ELSE NULL END) = 1

...which is equivalent to...

SELECT * FROM T WHERE C_D IS NULL

...but faster since it uses the index:

This saves space because single-column indexes do not store NULLs. Also, use COMPRESS since index will ever only contain one key so there is no need to waste space on repeating the same key over and over again in the index structure.

NOTE: Under Oracle 11, you could also create a function-based virtual column (based on the CASE expression above), then index and query on that column directly, to save some repetitive typing.

--- EDIT ---

If you are also interested in querying on C_I together with C_D IS NULL, you could...

CREATE UNIQUE INDEX T_IE2 ON T (C_I, CASE WHEN C_D IS NULL THEN 1 ELSE NULL END)

...and query it with (for example)...

SELECT * FROM T WHERE C_I > 'some value' AND (CASE WHEN C_D IS NULL THEN 1 ELSE NULL END) = 1

...which is equivalent of...

SELECT * FROM T WHERE C_I > 'some value' AND C_D IS NULL

...but faster, since it uses the index T_IE2.

This is in fact the only index that you need on your table (it "covers" the primary key, so you no longer need a separate index just on C_I). Which also means a same ROWIDs is never stored in more than one index, which saves space.

NOTE: COMPRESS no longer makes sense for index T_IE2.

--- EDIT 2 ---

If you care about simplicity more than space, you can just create a composite index on {C_I, C_D}. Oracle stores NULL values in composite index as long as there is at least one non-NULL value in the same tuple:

CREATE UNIQUE INDEX T_IE3 ON T (C_I, C_D)

This uses the index:

SELECT * FROM T WHERE C_I > 1 AND C_D IS NULL

As in previous EDIT, this is the only index that you need on your table.

 CREATE INDEX T_INDEX ON T ( CASE WHEN CD IS NULL THEN C_I ELSE NULL END);

This works because Oracle will not put the null values returned by the CASE statement into the index.

Let me "repackage" my original answer.

Create the table like this:

CREATE TABLE T ...;
CREATE INDEX T_PK_IDX ON T (C_I, CASE WHEN C_D IS NULL THEN 1 ELSE NULL END);
ALTER TABLE T ADD CONSTRAINT T_PK PRIMARY KEY (C_I) USING INDEX T_PK_IDX;

And query like this:

SELECT * FROM T
WHERE
    C_I > 'some value'
    AND (CASE WHEN C_D IS NULL THEN 1 ELSE NULL END) = 1

Query plan: