Oracle execution plan changing

Question

I have a query:

             SELECT COUNT(1)
             FROM registration r,
                  proxy p
             WHERE r.participant_code = p.participant_code
               AND r.proxy_type = p.proxy_type
               AND r.proxy_value = p.proxy_value
               AND r.proxy_sequence= p.proxy_sequence

All four fields used in join conditions for table 'proxy' are in 'proxy' primary key. Execution plan of the query has been using primary key to make a scan, but since one moment it suddenly changed index scan on full table scan.

After that query was changed a little(joins 'r.proxy_value = p.proxy_value' and 'r.proxy_type = p.proxy_type' where swapped):

             SELECT COUNT(1)
             FROM registration r,
                  proxy p
             WHERE r.participant_code = p.participant_code
               AND r.proxy_value = p.proxy_value                   
               AND r.proxy_type = p.proxy_type
               AND r.proxy_sequence= p.proxy_sequence

Actually both queries are equivalent. However after this change execution plan of the second query starts using index scan instead of full scan.

Now I have a very specific question:

Will Oracle recompile the second query and will it lead to query execution plan changing?

The two queries are apparently equivalent/identical except for the order of the conditions within the WHERE clause. Though intuitively one may think they are the same, I would make them both identical and re-test (yes, I agree with you that this seams to be a very amateur-like comment, but I was surprised too many times in my life with this type of things). — FDavidov
You know actually I've tested this scenario(yes behavior has been changed, also please note it's very hard to re-test because I don't have control over Oracle execution plan management mechanism), what I need is detailed explanation of how Oracle treats this situation. Any references on Oracle documentation will be appreciated. — mvb13
I'm not sure I get you. Did you make both select identical (by changing the order of the conditions in the WHERE clause)? If yes, did you observe different execution plans for them? — FDavidov
First step: on clean database I insert some test data execute both queries, both queries are identical. Second step: on clean database I execute from java application process that makes bulk inserts ans selects. At a point of time I realize that process became too slow. I check excecution plan using dbms tool and find that the query makes full scan instead of index. I replace the query as mentioned above and after that it starts using index(note I don't clean database and it already has approximately 1 million of records) — mvb13
When you explain plan for both, does Oracle keep generating different plans or you observe this situation only in first execution and later obtain same plan for both queries? If second is your case I would say first was generated with full scan because there was no stats for table. — Kacper

HAL 9000 HAL 9000 · Accepted Answer · 2016-11-03T12:18:40

Oracle now uses the cost-based optimizer (CBO) by default, versus the former rule-based optimizer which was more predictable but way less capable in data warehouse scenarios.

In general, CBO introduced "plan stability" issues, whereas the RBO's plans always remained the same.

Using the CBO means that execution plans can and will be changing on their own, based on changes in the statistics and/or dynamic sampling data. There are actually tools that allow to monitor the plan switch history (e.g. lab128).

Another point I noticed when looking at your query: there are no filters, so depending an what your actual primary keys are, there might be little usage for an index scan.

Oracle execution plan changing

1 Answers