CODEWITHSUNDEEP
databaseoracle-database
mvb13
mvb13

Reputation: 1594

Oracle execution plan changing

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?

Upvotes: 1

Views: 1190

Answers (1)

HAL 9000
HAL 9000

Reputation: 3985

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.

Upvotes: 1

Related Questions