OraclePL/SQL Analysis of inequality between IF and ELSIF

Source: Internet
Author: User
I have written an article called: OraclePLSQL ldquo from if to then; artistic appreciation rdquo;, address: www. linuxidc. comLinux2013-0177

I have written an article called: Oracle PL/SQL ldquo from if to then; artistic appreciation rdquo;, address: http://www.linuxidc.com/Linux/2013-01/77

I have written an article titled Oracle PL/SQL "Art Appreciation" from if to then. Address:

What should I pay attention to when the range is extended to each parallel IF condition?

When the condition is parallel, can IF be unordered?

Let's take a look at a small experiment:

Hr @ ORCL> ed
Wrote file afiedt. buf

1 create or replace procedure p_test_if (p_num pls_integer)
2
3 v_num PLS_INTEGER: = p_num;
4 begin
5 for I in 1 .. 1000000
6 loop
7 if p_num = 1
8 then
9 v_num: = p_num + 1;
10 elsif p_num = 2
11 then
12 v_num: = p_num + 1;
13 elsif p_num = 3
14 then
15 v_num: = p_num + 1;
16 else
17 v_num: = p_num + 1;
18 end if;
19 end loop;
20 * end p_test_if;
Hr @ ORCL>/

Procedure created.

Hr @ ORCL> set timing on
Hr @ ORCL> exec p_test_if (1 );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 13
Hr @ ORCL> exec p_test_if (2 );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 18
Hr @ ORCL> exec p_test_if (3 );

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 25

The time of the three calls is different. The higher the IF logic, the faster the query.

The principle is actually very simple. The first condition can reduce the number of judgments, because after the conditions are met, the subsequent expressions will not be tried.

Therefore, even IF the IF logic is parallel, the order of IF is determined based on the distribution of input values of PL/SQL processes or functions.

The more frequently the value of the input parameter is called, the more advanced the corresponding IF logic is.

Because Oracle calls the IF function more quickly than before.

This tells us that in PL/SQL development, we 'd better understand the distribution range of input values of commonly called functions or processes.

For frequently entered parameter conditions, the code should be placed before the IF, there will be a certain degree of Performance Improvement

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.