1

I created a copy of the employees table in the HR schema. Then added a non-unique index on the employee_id column. 

create table employees1 as select * from employees;
create index temp_idx on employees1(employee_id);

Then checked its execution plan of "select employee_id from employees;", it performed full table scan and the cost was 3. However, when I created a unique index, it performed index full scan and the cost was 1.

As far as I know, how oracle creates the unique and non-unique indexes are identical. So the leaf count etc should be the same in both indexes. So in this case, while it could select index full scan with the non-unique index and decrease the cost to 1, why it chose full table scan and resulted in a worse plan? Btw, I did not delete or inserted any rows after creating the table.

oramas
  • 881
  • 7
  • 12
  • 1
    It depends on a lot of factors. How many rows in the table are there? How much storage (how many byes) does each row consume on disk? – Bohemian May 22 '20 at 23:39
  • 2
    @Bohemian - it shouldn't depend on anything. The OP is selecting `all` the rows, so the question isn't to use the index for fast access. A full scan will be performed either way. The OP's question (a bit misguided, as I explain in my Answer) is why Oracle doesn't read all the `employee_id` values from the index, which should in all cases be smaller than the entire table (full rows). –  May 23 '20 at 00:44

1 Answers1

1

What you said about execution plan in the presence of unique index is simply not true. (I just checked on my machine - Oracle still performs a full scan even with unique index.)

This makes perfect sense. Regardless of whether the index is unique or not, it does NOT store anything when EMPLOYEE_ID is NULL. On the other hand, if there were any rows with null EMPLOYEE_ID, they SHOULD be returned by the query - so the query can't look at the index alone.

If you want Oracle to do an index scan, you must either tell Oracle that the column is NOT NULL (that will happen automatically when the column is primary key - perhaps you are confused between that and "unique index"), or you must "select employee_id ... WHERE EMPLOYEE_ID IS NOT NULL". You may know that the "not null" condition is true for all rows anyway, but Oracle won't know that until after it reads all the data from the table - or if you put a not null constraint on the column. If you state explicitly that you only want the non-null values, Oracle knows it can use the index. (And, again, whether the index is unique or not doesn't matter!)