DETERMINISTIC and deterministic in Oracle

DETERMINISTIC and deterministic in Oracle

I have been confused when I saw DETERMINISTIC for many times. I did an experiment today. We assume that ORACLE is a DETERMINISTIC function, which is called only once in SQL. If DETERMINISTIC is not used, the values are different. After DETERMINISTIC is used, different sessions have the same value.

SQL> create or replace function f_t (I _p int) return number DETERMINISTIC is

I _rtn number;
Begin
I _rtn: = I _p * dbms_random.value (1, 10 );
Return I _rtn;
End;
/
The function has been created.

Session1:
SQL> select LEVEL, f_t (1) FROM DUAL CONNECT BY LEVEL <= 10;
LEVEL F_T (1)
--------------------
1 2.55732959
2 2.55732959
3 2.55732959
4 2.55732959
5 2.55732959
6 2.55732959
7 2.55732959
8 2.55732959
9 2.55732959
10 2.55732959
10 rows have been selected.

Session2:
SQL> select LEVEL, f_t (1) FROM DUAL CONNECT BY LEVEL <= 10;
LEVEL F_T (1)
--------------------
1 2.55732959
2 2.55732959
3 2.55732959
4 2.55732959
5 2.55732959
6 2.55732959
7 2.55732959
8 2.55732959
9 2.55732959
10 2.55732959
10 rows have been selected.

SQL> create or replace function f_t (I _p int) return number is
I _rtn number;
Begin
I _rtn: = I _p * dbms_random.value (1, 10 );
Return I _rtn;
End;
/
The function has been created.


SQL> select LEVEL, f_t (1) FROM DUAL CONNECT BY LEVEL <= 10;
LEVEL F_T (1)
--------------------
1 8.48649118
2 8.9396978
3 2.2786135
4 5.29205905
5 5.32847713
6 8.70095819
7 6.20471031
8 2.00101537
9 3.53814265
10 3.64991086
10 rows have been selected.
What is the difference between: = and = in oracle?

Variable binding refers to using variables rather than constants in the conditions of SQL statements. For example, there are two SQL statements in the shared pool,
Select * from tab1 where col1 = 1;
Select * from tab1 where col1 = 2;
For oracle databases, this is two completely different SQL statements, which both require hard parse. Because oracle calculates the hash value of each character in the memory based on the SQL statement text, although the preceding two SQL statements have only one character, oracle uses the hash algorithm to obtain different hash addresses in the memory. Therefore, oracle considers this statement to be two completely different statements. If you rewrite the preceding SQL statement to select * from tab1 where col1 =: var1; and then query the variable var1 by assigning a value, oracle will perform hard parse for this statement for the first time, in the future, only soft parse will be performed. Assuming that a statement has been repeatedly executed for several 100,000 times, the benefits of using bind var are enormous. If bind var is not fully used for an application, it will be accompanied by severe performance problems.

Variable binding is relative to text variables. The so-called text variables refer to the SQL statement that directly writes query conditions. Such SQL statements need to be parsed repeatedly under different conditions, to bind a variable is to use a variable to replace the direct writing condition, query the bind value and pass it at runtime, and then bind it for execution. The advantage is that hard Parsing is reduced, CPU contention is reduced, and shared_pool is saved. The disadvantage is that histogram cannot be used, which is difficult for SQL optimization.

What is $ in Oracle?

$ Common in oracle:

1. Regular Expression:
It indicates the end of a row, such:
SQL> SELECT description, REGEXP_INSTR (description, 'ing $ ') where_it_is
2 FROM testTable
3 WHERE REGEXP_INSTR (description, 'ing $ ')> 0;

DESCRIPTION WHERE_IT_IS
---------------------------------------------------
2003 Movie showing 16

2. System View/pseudo table:
Example: sys. tab $
X $ ktfbue
V $ session

These are all maintained by the system and cannot be modified by the user.