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

Oracle how to optimize complex views, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Optimization method

If the predicate conditions commonly used in this kind of view can be written directly into the view and written dynamically, and the commonly used query columns are selectively created from the largest to the smallest, the optimizer can use the index to obtain data directly as much as possible. and then filter calculation and other operations, so as to achieve the purpose of optimization. The specific steps are as follows:

(1) create an auxiliary package and create two functions. In practice, if you pass multiple conditions at the same time, you can expand the method in the package by yourself, and two methods need to be added to each condition.

(2) modify the view definition, write the predicate condition to the base table condition, and recompile the view.

Performance comparison

The following is a concrete example to illustrate how to optimize complex views.

By comparing the original view with the optimized view, it is found that the execution plan cost of the optimized view is reduced from 349 to 86, and the optimization effect is very obvious. Special attention should be paid to the red box in the table.

The execution plan of the optimized front view query is a full table scan, which is inefficient. The execution plan of the optimized view query is an index range scan. The data that needs to be accessed is extracted directly through the index, and then processed later, the performance is greatly improved compared with that before optimization.

It should be pointed out that this method is only effective when accessing a small amount of data in the view, and it is not applicable if the amount of data accessed is large.

Table 4.1 optimize front view definitions, query statements, and execution plans

Table 4.2 View definition, query statement and execution plan after optimization

Through the above analysis and comparison, we can clearly see that the optimized view can use the index and the performance has been significantly improved. To sum up, the idea of optimization for complex queries should be: if a small amount of data is accessed in the view, the data that needs to be processed is extracted as far as possible through the index, and then subsequent processing is carried out. This method applies if various conventional optimization methods for complex views, such as adding indexes, adding prompts, and so on, are ineffective.

However, this method also has some disadvantages, that is, compared with other optimization methods, this method needs to do more preliminary work, such as establishing auxiliary packages, modifying and recompiling the original view. In addition, we also need to test whether the query results are consistent before and after optimization. We need to call the auxiliary package before the query to transfer the query conditions into the view, but the optimized view does not need to add query conditions.

After reading the above, have you mastered how to optimize complex views in Oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.