Is a three-column SQL index when the middle column can be anything?

Question

Trying to prove something now to see if an index needs to be added.

If I have an index in columns A, B, C, and I, create a query that explicitly uses A and C in the where clause, will I get an index advantage?

In this scenario, imagine the where clause as follows:

A = 'Q' AND (B is not null OR B is null) AND C='G'

I researched this in Oracle using EXPLAIN PLAN and it doesn't seem to use an index. Also, from my understanding of how indexes are created and used, it will not be able to take advantage, since the index cannot use column B due to lack of specificity.

We are currently considering this in both MSSQL and ORACLE. Not sure if someone is optimizing differently than the other.

Any advice is appreciated! Thank!

Answer 1

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 

SQL> create table t$ (a integer not null, b integer, c integer, d varchar2(100 char));

Table created

SQL> insert into t$ select rownum, rownum, rownum, lpad('0', '1', 100) from dual connect by level <= 1000000;

1000000 rows inserted

SQL> create index t$i on t$(a, b, c);

Index created

SQL> analyze table t$ estimate statistics;

Table analyzed

SQL> explain plan for select * from t$ where a = 128 and c = 128;

Explained

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3274478018
--------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |     1 |    13 |     4   (0)
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T$   |     1 |    13 |     4   (0)
|*  2 |   INDEX RANGE SCAN                  | T$I  |     1 |       |     3   (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"=128 AND "C"=128)
       filter("C"=128)
15 rows selected

Any question?

Answer 2

If you look at the structure of the B + index tree, the answer is as follows: The left side of the index, including the first inequality, will go to Seek Predicate, the rest to Predicate in the request.

For example, read http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys