Need help in understanding a plan- Oracle

Question

I have a table

index_test(id number,empid number,name varchar2(30));

I have made a composite index on columns empid & name. I was testing a concept that says when I use OR clause index is not used.
so I wrote this query

select * from index_test where empid='950604' or name='5OMVXGH6G5';

but I got a result which is unexpected to me. it does use the index but also scans the full table. Please help me understand what's happening here.

Here is the plan -

> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2255565997                                                                                                                                                                                                                                                                                  

--------------------------------------------------------------------------------------------------------                                                                                                                                                                                                     
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                     
--------------------------------------------------------------------------------------------------------                                                                                                                                                                                                     
|   0 | SELECT STATEMENT             |                         |    11 |   231 |  9695   (1)| 00:01:57 |                                                                                                                                                                                                     
|   1 |  CONCATENATION               |                         |       |       |            |          |                                                                                                                                                                                                     
|*  2 |   TABLE ACCESS FULL          | INDEX_TEST              |     1 |    21 |  9682   (1)| 00:01:57 |                                                                                                                                                                                                     
|   3 |   TABLE ACCESS BY INDEX ROWID| INDEX_TEST              |    10 |   210 |    13   (0)| 00:00:01 |                                                                                                                                                                                                     
|*  4 |    INDEX RANGE SCAN          | SAMPLE_INDEX_INDEX_TEST |    10 |       |     3   (0)| 00:00:01 |                                                                                                                                                                                                     
--------------------------------------------------------------------------------------------------------                                                                                                                                                                                                     

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          

   2 - filter("NAME"='5OMVXGH6G5')                                                                                                                                                                                                                                                                           
   4 - access("EMPID"=950604)                                                                                                                                                                                                                                                                                
       filter(LNNVL("NAME"='5OMVXGH6G5'))   

Answer 1

As others have already mentioned, there is an OR clause in the query which means oracle has to look at both the conditions around the OR independently.

Looking at the explain plan, here is how it works -

Step 1. First it will look at all rows which satisfy the condition -

NAME='5OMVXGH6G5'

Step 2. Then it will look for rows that satisfy the condition -

EMPID=950604

(oh and make sure you don't use quotes when comparing a number column to a value)

Step 3. Once it gets these two results, it will join the two to get the final result

You have a composite index on (EMPID, NAME). This will only help you in cases where you are searching for both empid and name together, but not when searching for just one of them separately. As expected, it used the index to get EMPID pretty quick (because that is the leading column in the index) but there was no way other than FULL TABLE SCAN to search the NAME.

If EMPID is the leading column in a composite index, you want to have empid in the search condition. The step1 of your search has just NAME in it and that is why it can't use the index.

What you should do -

1. If the composite index is not being used for other queries and you just created it for this query, then drop it.

2. Create 2 different indexes on the columns empid and name. This way both your step1 and step2 can use the indexes.

Answer 2

If you examine the plan, you will see this line: | 1 | CONCATENATION
This is a sign that Oracle decided to use "OR expansion" optimalization method.

More on this you can find here: https://docs.oracle.com/database/121/TGSQL/tgsql_transform.htm#GUID-0D5B9093-CDEA-45AC-A607-1F0D8F2615DD

In short: in this method Oracle transforms a query with OR condition:

select * from index_test 
where empid='950604' or name='5OMVXGH6G5'

into a form:

SELECT * FROM index_test 
   WHERE name='5OMVXGH6G5'
UNION ALL
SELECT * FROM TABLE
   WHERE empid='950604' and not( name='5OMVXGH6G5' )

Why Oracle did that ? Well - I really don't know.
It looks very strange to me.
Maybe your statistics are stale ?

Answer 3

Because you have an OR condition, the optimiser needs to fetch the result of both the queries: one matching the empid and the one matching the name.

Since you are selecting * - all columns, the optimiser has decided it is more efficient to do a full scan in addition to just using the index INDEX_TEST.

I can give a more helpful answer if you can mention what columns are in the indexes.