Is a 3 column SQL index used when the middle column can be anything?

user_2574486 0
Viewed 86

Trying to prove something out currently to see if adding an index is necessary.

If I have an index on columns A,B,C and I create a query that in the where clause is only explicitly utilizing A and C, will I get the benefit of the index?

In this scenario imagine the where clause is like this:

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

I investigated this in Oracle using EXPLAIN PLAN and it doesn't seem to use the index. Also, from my understanding of how indexes are created and used it won't be able to benefit because the index can't leverage column B due to the lack of specifics.

Currently looking at this in either MSSQL or ORACLE. Not sure if one optimizes differently than the other.

Any advice is appreciated! Thank you!

oracle sql-server sql
2 Answers
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?

user_32836440
user_87325660
Related