question

BI DWH BALA avatar image
BI DWH BALA asked

What is a deterministic function?

What is a deterministic function? What are the advantages if we define a function as a determnisitic?

Thanks in advance Regards

BI DWH BALA

oraclefunction
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Christian13467 avatar image
Christian13467 answered

A deterministic function gives always the same result to given parameters. The result of a deterministic function depends only from the input. It can have more than one parameter.

Samples of deterministic functions: length, to_upper, trunc, substr

Oracle uses deterministic functions building function based indexes. Be deterministic is necessary because oracle stores the function result into a normal index. At query time the optimizer calls the function again for query parameters but not for all table values.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BI DWH BALA avatar image BI DWH BALA commented ·
HI.. Can we define a procedure also deterministic?
0 Likes 0 ·
Christian13467 avatar image Christian13467 commented ·
No. Only plsql functions can be defined to be deterministic. Procedures cannot be used in places where functions should be deterministic.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.