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

Editor to share with you how to use the function DETERMINISTIC, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

Deterministic function

The main reason for Oracle to introduce deterministic functions is to improve the performance of the index of the function and minimize the cost. Therefore, if you want to use the pl/sql function as the basis for the index given to the function, you need to make sure that the function is deterministic.

The performance benefit of a deterministic function is that if the function is called twice with the same input, Oracle can remember the result of the first call and thus avoid calling it again on the second execution. But this feature was not really implemented until 10g Release 2 [@ more@]

Classic examples are as follows:

CREATE TABLE test02 (a NUMBER,b VARCHAR2)

INSERT INTO test02 VALUES (1)

INSERT INTO test02 VALUES (2)

INSERT INTO test02 VALUES (2)

INSERT INTO test02 VALUES (3dd')

CREATE SEQUENCE seq_test02

CREATE OR REPLACE FUNCTION uf_getseq (p_number NUMBER) RETURN NUMBER DETERMINISTIC IS

V_seq NUMBER

BEGIN

Select seq_test02.nextval INTO v_seq from dual

RETURN v_seq

END

/

SQL > select t. From test02 from test02 t

A BUF_GETSEQ (T.A)

1 aa17

2 bb18

2 cc18

3 dd19

SQL >

SQL > CREATE OR REPLACE FUNCTION uf_getseq (p_number NUMBER) RETURN NUMBERIS

2v_seq NUMBER

3BEGIN

4select seq_test02.nextval INTO v_seq from dual

5RETURN v_seq

6END

7/

Function created

SQL > CREATE INDEX ix_a_test02 ON test02 (uf_getseq (a))

CREATE INDEX ix_a_test02 ON test02 (uf_getseq (a))

ORA-30553: the function cannot be determined

SQL >

SQL > CREATE OR REPLACE FUNCTION uf_getseq (p_number NUMBER) RETURN NUMBER DETERMINISTIC IS

2v_seq NUMBER

3BEGIN

4select seq_test02.nextval INTO v_seq from dual

5RETURN v_seq

6END

7/

Function created

SQL > CREATE INDEX ix_a_test02 ON test02 (uf_getseq (a))

Index created

SQL >

After DETERMINISTIC, you can create indexes based on custom functions.

The above is all the content of this article "how to use function DETERMINISTIC". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.