Shulou(Shulou.com)05/31 Report--

This article mainly introduces the impact of Oracle histogram on the implementation of the plan, the article is very detailed, has a certain reference value, interested friends must read it!

Preface

In the Oracle database, CBO defaults that the data of the target column is evenly distributed between its minimum low_value and maximum high_value, and calculates the target column according to the principle of uniform distribution

The selectable rate after applying the query condition and the cardinality of the result set.

If the histogram is collected for the target, it means that CBO no longer considers the data on the target column to be evenly distributed. CBO calculates the cardinality of the returned result set using the statistics of the histogram on the column.

Verify the impact of the histogram on the execution plan:

1. Create a table T1

2. Insert highly inclined data into the table

3. Create an index on the B field

4. Delete the histogram information in the table

5. View histogram information

HISTOGRAM:NONE represents no histogram information.

6. Query the execution plan of bread2

As can be seen from the execution plan: the query takes a full table scan, because the data in table T1 is considered to be evenly distributed, and CBO thinks that go

A full table is faster than walking an index.

7. Collect histogram information

8. Query histogram information

HISTOGRAM:FREQUENCY frequency histogram

You can see that there is already histogram information in the table at this time.

9. Check the implementation plan of bread2 again

At this time, we can see that the implementation plan has gone to the index, and the result is correct!

The above is all the contents of the article "what is the impact of the histogram on the implementation of the plan in Oracle". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.