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

This article mainly introduces "how Oracle 11g collects multi-column statistical information". In the daily operation, I believe that many people have doubts about how Oracle 11g collects multi-column statistical information. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how Oracle 11g collects multi-column statistical information". Next, please follow the editor to study!

Typically, when we submit a SQL statement to an Oracle database, Oracle chooses the best way to execute it, which is done by querying the optimizer Query Optimizer. CBO (Cost-Based Optimizer) is the query optimizer mode that Oracle uses by default. In CBO, the generation of SQL execution plan is a goal-oriented implementation plan exploration process to find the optimal cost (Cost). The so-called Cost is a quantitative indicator that integrates CPU and IO consumption, and the cost of each execution plan is the numerical value estimated by the internal formula of the optimizer.

When we write SQL statements, we often encounter the situation that there are multiple conditions after the where clause, that is, we filter the data according to the conditions of multiple columns. By default, oracle multiplies the selectivity of multiple columns to get the selectivity of the where statement, which may cause the selection rate to be inaccurate and cause the optimizer to make a wrong judgment. In order to enable the optimizer to make an accurate judgment and generate an accurate execution plan, oracle introduced the collection of multi-column statistics in the 11g database. Through the multi-conditional query of the test table, this paper introduces the importance of collecting multi-column statistical information.

1. Environmental preparation

We carried out experiments in Oracle 11g.

Here) collapse or open

SQL >

SQL > explain plan for select * from hoegh where employee_id=110 and email=\ 'JCHEN\'

Explained.

SQL >

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 774871165

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 69 | 8 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | HOEGH | 1 | 69 | 8 (0) | 00:00:01 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1-filter (\ "EMPLOYEE_ID\" = 110 AND\ "EMAIL\" =\ 'JCHEN\')

13 rows selected.

SQL >

From the execution plan, you can see that one row of records has been returned, and what is the fact? Let's execute this sql statement.

Click (here) to collapse or open

SQL > select count (*) from hoegh where employee_id=110 and email=\ 'JCHEN\'

COUNT (*)

-

sixteen

SQL >

From this, we can see that the test table hoegh has 16 rows of data that meet the query criteria, while the execution plan prompts only 1 row, which is an error. What's going on? there's something wrong with the selectivity that we mentioned at the beginning.

In this multi-column conditional query statement, the selection rate = 1 Universe 107, the number of rows returned = 1 picks 107, the number of returned rows is 107, the number of returned rows is 107, and the number of returned rows is 107.

SQL > exec dbms_stats.gather_table_stats (\'HR\',\ 'HOEGH\', method_opt= >\ 'for columns (employee_id,email)\')

PL/SQL procedure successfully completed.

SQL >

SQL > explain plan for select * from hoegh where employee_id=110 and email=\ 'JCHEN\'

Explained.

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 774871165

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 16 | 1152 | 8 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | HOEGH | 16 | 1152 | 8 (0) | 00:00:01 |

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1-filter (\ "EMPLOYEE_ID\" = 110 AND\ "EMAIL\" =\ 'JCHEN\')

13 rows selected.

SQL >

From the result of the execution plan, after collecting multi-column statistics in the same sql query statement, the selectivity of Oracle changes from error to right, this is because the two conditions in the sql statement are related, that is, employee_id and email are unique in the employees table, and both can uniquely identify a row of records. Before collecting multi-column statistics, Oracle did not know that the two query conditions were related, so when calculating the selection rate (selectivity), it simply adopted the multiplication method.

At this point, the study on "how Oracle 11g collects multi-column statistics" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.