I've a doubt and i'n sure you can help me :)
Can Oracle use the PK in where clause if all columns of PK aren't be used?
The PK are defined by 4 columns, but in the WHERE there are only 3 columns of index.
The first column of the index (the column that appears in the leading edge of the index) has a great importance for the optimizer when it decide to use the index or not. Here your index starts with SCO_ID_HR column which appears in the predicate part
select ....... FROM M4SCO_H_HR_WUNIT_TUM A , M4SCO_HR_ROLE B WHERE A.ID_ORGANIZATION = B.ID_ORGANIZATION AND A.SCO_ID_HR = B.SCO_ID_HR AND A.SCO_OR_HR_PERIOD = B.SCO_OR_HR_PER AND B.SCO_DT_START <= A.SCO_DT_END AND B.SCO_DT_END >= A.SCO_DT_START; And the index of 2 tables are: TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION M4SCO_H_HR_WUNIT_TUM PK_O_H_HR_WUNIT SCO_ID_HR 1 M4SCO_H_HR_WUNIT_TUM PK_O_H_HR_WUNIT SCO_DT_START 2 M4SCO_H_HR_WUNIT_TUM PK_O_H_HR_WUNIT SCO_OR_HR_ROLE 3 M4SCO_H_HR_WUNIT_TUM PK_O_H_HR_WUNIT ID_ORGANIZATION 4
This leading column is not only present in the predicate part but it also appears in an equality predicate which plays also an important role in the choice of the index or not.
AND A.SCO_ID_HR = B.SCO_ID_HR