I have been reading an oracle document about optimezer paths, I came across very interesting sentences.It is written the following
A unique or primary key constraint is insufficient by itself to produce an index unique scan. Consider the following example, which creates a primary key constraint on a column with a non-unique index, resulting in an index range scan:
Primary key with non-unique index? Thıs should be impossible, isn't it? I am wondering your thoughts, the link is below.
Optimizer Access Paths
My second question is, when the document explains the B-Tree indexes and Nulls. It gives an example. However, it is written the following.
An example helps illustrate. The hr.employees table has a primary key index on employee_id, and a unique index on department_id.
It is said that, department_id column in employee table is unique. However, normally if you look at the HR schema, you can see that it is non-unique column. At the same time, it is obvious this column doesn't wanted to be unique because different employees can work same department. Also, after saying that department_id is unique the example shows that it is not because of index range scan. Can I say that I am right because of following example verify this situation?
SQL> EXPLAIN PLAN FOR SELECT department_id FROM employees WHERE department_id=10;
Explained.
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 67425611
--------------------------------------------------------------------------------
| Id| Operation | Name | Rows |Bytes| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:0 0:01 |
|*1 | INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:0 0:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - access("DEPARTMENT_ID"=10)
When a spesific department id (10) is queried. Explain plan show that index range. Because there might be more than one value, because it is non-unique????
The source is here: Optimizer Access Paths
I am very wondering your remarkable thoughts and opinions. Thank you in advance.