Execution time of oracle custom functions

OracleThe pl/SQL code in involves two execution engines, one dedicated to processing standard SQL statements, and the other Process Code for processing pl/SQL, cpu overhead is usually caused by engine switching. For example, the difference between a foreach loop statement and a common for loop is that foreach eliminates engine switching and stays in the engine that executes standard SQL, thus shortening the execution time.

Now we have a customer information table tacustomer, which contains fields such as birthday, certificatetype, and certificateno. Now we want to know the customer's birthday information. Because the user's registration date and other fields are optional, most of them are empty. Therefore, you must extract them from the certificate number certificateno. certificatetype is the document type, in addition to the ID card, there are enumerated values such as the military officer's ID card, the secret card, and the account book. The input value is also unreliable. Originally, '0' represents the ID card, but due to incorrect input, so that a non-'0' value may also refer to the ID card, while a '0' value may not be an ID card. Based on these considerations, a function is written. The input parameter is the preceding three fields and the output is the birthday year. I wrote two versions. On the author's windows2003 database server, Intel (R) Xeon (R) CPU 5140 @ 2.33 GHZ, 4 CPU, GB memory ), execute the following statement: the number of rows of tacustomer is about 200 million.) -- test the execution time of simple string connection.

Select count (t. certificatetype | t. certificateno | t. birthday)

FROM tacustomer t

WHERE 1 = 1

AND rownum< 2000000

-- Test the execution time of the custom function

Select count (f_extract_birthday (t. certificatetype, t. certificateno, t. birthday ))

FROM tacustomer t

WHERE 1 = 1

AND rownum< 2000000

The execution time is as follows ):

8.563

19.844 version 1)

57.953 version 2)

We can see that the above condition is changed to ROWNUM <1000000, which also satisfies the relationship of 3 times.) The difference between version 1 and Version 2 is that internal implementation is different. The first generation of ID cards must contain 0-9 digits, the first 17 digits of the second generation must be digits, and the last digit must be digits or 'X '. Versions 1 and 2 use different methods to verify the number. Version 1 checks one by one. Version 2 uses cast (certno as numeric) and captures exceptions. as a result, version 2 is three times faster than Version 1. According to the conclusion, the performance of user-defined functions is generally inferior to that of built-in system functions.

Two versions of code are provided:

-- Version 1

Create or replace FUNCTION f_extract_birthday (id in VARCHAR, birthday in VARCHAR)

RETURN VARCHAR

IS

I integer;

-- Id VARCHAR (18 );

-- Birthday VARCHAR (8 );

Yyyy VARCHAR (4 );

Len SMALLINT;

Trans BOOLEAN;

C SMALLINT;

Ret VARCHAR (4 );

Val NUMERIC (18, 0 );

BEGIN

I: = 1;

-- Id: = '000000 ';

-- Id: = '201730219810315405x ';

-- Birthday: = '20140901 ';

Trans: = FALSE;

Len: = length (id );

-- Val: = CAST (id as numeric );

-- Dbms_output.put_line (val );

The execution time of oracle user-defined functions is described as follows, now I have a general understanding of the execution time of oracle user-defined functions. I hope the content mentioned above will be helpful to you.