I have a strange situation:
there is a query (names of the objects are diffrent of course)
there is also an index GRANTEE_INDEX_01 on grante[pm, st] (every table has similar index [pm,st]), and I have no idea why - but oracle does both index range scan and fast full scan on same index, and I can't knock it out of his... core.
Actually there is no performance problem,at least no one complains. I just want to know why is this happening?
The clue is this is [Oracle 12c]. What you're seeing here is a manifestation of the adaptive cursor capability. This is a (controversial) feature Oracle added in 12c which allows the optimizer to change to a different plan if it thinks a query is running too slowly using the existing plan.
Oracle has actually come up with two plans for your query, one using hash joins and the other using nested loops. It's executing one plan but is using the STATISTICS COLLECTOR operation to monitor the other plan; this is how the CBO decides whether to switch to the other plan. You're seeing two hits on GRANTEE_INDEX_01 because it's used in both plans.
Incidentally, the explain plan should have some additional information (try the Text or HTML views) which would tell you this.
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
The inactive lines are the alternate plan joins and the STATISTICS COLLECTOR operations.
If you want to find out more, Maria Colgan wrote about in on the official Oracle blog site. Inevitably, Tim Hall also a good article on his Oracle-Base site.
I described adaptive cursors as controversial. This is a feature many DBAs disliked when it first came out and switched it off. Their objections centre mainly around its unpredictability: DBAs like their SQL to have stable execution plans. Also I'm going to include Chris Saxon's comment, because he's closer to the product than I am:
I'd argue it's adaptive statistics, not adaptive plans that caused most the controversy. Which is why this was split into two parameters in 12.2, with adaptive plans on and adaptive stats off by default.
The difference comes from the definition of the index. If you have a composite index, the order of the columns in the index definition matters. In your example, pm column is defined before the st column. This will affect how that index will be organized. Because of this factor, you are seeing two different usages of that index.
" INDEX FAST FULL SCAN is the equivalent of a FULL TABLE SCAN, but for an index"
Could you please paste you grantee table structure and explain plan using
select * from table(dbms_xplan.display)
Index range scan is used to here to filter the rows based on where clause and then index fast full scan is used to access the rows.