DETERMINISTIC Function and Function Based Index on Deterministic Function

Posted by HIMANSHU KARKI at 11:39 AM comments (0)

Exploring DETERMINISTIC Function and Function based indexes we found:

Deterministic functions can be created on a function having DML command on it, however you cannot refer such function from SELECT SQL.
You can create Function Based Index on a Deterministic Function only. In other words a function must be deterministic so that Index could be created on it.
Once you create function based index on a deterministic function, all possible value from that function is stored in Index and function body will not be executed at all.
Even if you have written DML in Deterministic function body that DML will execute only when you create function based index on that Deterministic Function and DML will not execute in any call to that function ever.
Even if you re-create function with different arithmetic for return value, referring it will give same result in SQL SELECT statement as of previous function return on which Index was created .
Deterministic function behave same as other functions when we called them in plsql code and not with a SELECT statement. Inside plsql block or Sql*Plus environment the DML body will execute even if it has index created on it.
Table used in SELECT query in Deterministic code and Table on which Function Based Index is created using that Deterministic Function should not be same. Else we will get ORA-04091: table is mutating, trigger/function may not see it error.

1. Index creation failed on Non-Deterministic Function:

SQL> CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

2 RETURN NUMBER

3 IS

4 BEGIN

5 RETURN v_value+1000;

6 END;

7 /

Function created.

SQL> CREATE INDEX deter_ind1 ON EMP2(determinst_test(sal));

CREATE INDEX deter_ind1 ON EMP2(determinst_test(sal))

ERROR at line 1:

ORA-30553: The function is not deterministic

SQL> CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

2 RETURN NUMBER DETERMINISTIC

3 IS

4 BEGIN

5 RETURN v_value+1000;

6 END;

7 /

SQL> CREATE INDEX deter_ind1 ON EMP2(determinst_test(sal));

Index created.

2. DML inside a Deterministic function which is referred in SELECT query is acceptable if it has index created on it else it will through ORA-14551.

SQL> drop index DETER_IND1;

Index dropped.

SQL> CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

2 RETURN NUMBER DETERMINISTIC

3 IS

4 BEGIN

5 INSERT INTO DETER_INSRT

6 VALUES (v_value, 'Value is '||v_value);

7 RETURN v_value+1000;

8 END;

9 /

Function created.

SQL> SELECT EMPNO, ENAME, sal, determinst_test(sal)

2 FROM EMP2

3 WHERE determinst_test(sal) >= 3000;

WHERE determinst_test(sal) >= 3000

ERROR at line 3:

ORA-14551: cannot perform a DML operation inside a query

ORA-06512: at "SCOTT.DETERMINST_TEST", line 5

SQL> CREATE INDEX DETER_IND1 on EMP2(determinst_test(sal));

Index created.

SQL> SELECT EMPNO, ENAME, sal, determinst_test(sal)

2 FROM EMP2

3 WHERE determinst_test(sal) >= 3000;

EMPNO ENAME SAL DETERMINST_TEST(SAL)

---------- ---------- ---------- --------------------

7698 BLAKE 2850 3850

7566 JONES 2975 3975

7788 SCOTT 3000 4000

7902 FORD 3000 4000

3. Change in function return arithmetic will not give different result in SELECT SQL, however Sql*Plus call will make it behave as normal function call.

SQL> CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

2 RETURN NUMBER DETERMINISTIC

3 IS

4 BEGIN

5 RETURN v_value+5000;

6 END;

7 /

Function created.

SQL> SELECT EMPNO, ENAME, sal, determinst_test(sal)

2 FROM EMP2

3 WHERE determinst_test(sal) >= 3000;

EMPNO ENAME SAL DETERMINST_TEST(SAL)

---------- ---------- ---------- --------------------

7698 BLAKE 2850 3850

7566 JONES 2975 3975

7788 SCOTT 3000 4000

7902 FORD 3000 4000

SQL> EXEC :v_deter := determinst_test(300);

PL/SQL procedure successfully completed.

SQL> print v_deter;

V_DETER

----------

5300

4. DML inside Deterministic function is executed either when we create index on it or when we call it from plsql or sql*plus Exec command. DML is not executed when it is referred in SELECT.

SQL> SELECT * FROM DETER_INSRT;

no rows selected

SQL> CREATE INDEX DETER_IND1 on EMP2(determinst_test(sal));

Index created.

SQL> SELECT * FROM DETER_INSRT;

C1 C2

---------- --------------------

300 Value is 300

800 Value is 800

1600 Value is 1600

1250 Value is 1250

2975 Value is 2975

2850 Value is 2850

3000 Value is 3000

1500 Value is 1500

1100 Value is 1100

950 Value is 950

However DML executes if we call this function not from SELECT SQL.

SQL> EXEC :VAL := determinst_test(1000);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM DETER_INSRT;

        C1 C2

---------- --------------------

       300 Value is 300

       800 Value is 800

      1600 Value is 1600

      1250 Value is 1250

      2975 Value is 2975

      2850 Value is 2850

      3000 Value is 3000

      1500 Value is 1500

      1100 Value is 1100

       950 Value is 950

      2000 Value is 2000

5. It has been observed that if we create a deterministic function and then a function based index using that DETERMINISTIC function, we cannot update values of table on which it has been referred.
Conclusion: Table used in SELECT query in Deterministic code and Table on which Function Based Index is created using that Deterministic Function should not be same.

We created following DETERMINISTIC function:

 CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

  RETURN NUMBER  DETERMINISTIC

IS

 v_sal NUMBER;

 BEGIN

 SELECT SAL into v_sal FROM emp2 where empno=v_value;

  RETURN v_value+1000+v_sal;

  END;

/

Then Index on table, using this deterministic function:

SQL>  CREATE INDEX  DETER_IND1 on EMP2(determinst_test(sal));

Index created.

Then we tried to update value in this table:

SQL> update emp2 set sal=sal+10000 where empno=7876;

update emp2 set sal=sal+10000 where empno=7876

*

ERROR at line 1:

ORA-04091: table SCOTT.EMP2 is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.DETERMINST_TEST", line 6

Solution: If we drop the index or create the index on some different table then we can update the record.

DETERMINISTIC Functions

Posted by HIMANSHU KARKI at 1:31 AM comments (0)

Problem - Searching for data records by a function result

There are different real life problems where you have to use a function in the WHERE statement of your query which will often slow it down to a level where it's not acceptable anymore.

Go through the script below and you'll probably understand in which situations you'd want to use a deterministic function.

Recipe #1 - Using a deterministic function to improve your query

First, a quick word about deterministic. A deterministic function always returns the same value if the input parameters are identical. 1+1 is always equal to 2 but a function called Get_Customer_Name(4711) won't return the same value because it fetches data from the database which changes.

Let's create an example including a custom function to show this possible improvement.

CREATE TABLE deterministic_test (column_a NUMBER, column_b VARCHAR2(100));

After you've created that table, use the query below to insert some example data. 100'000 records took about one second on my server, if you're running it on a slow computer you'll have to wait for a bit more.

INSERT INTO deterministic_test
SELECT ROWNUM N, DBMS_RANDOM.STRING ('X', 16) FROM dual  
CONNECT BY LEVEL <= 100000;

Now that we have our test able including some data in it, we have to create our test function. In this case we're going to create a simple function which will return the average letter of a string. For example: If you use AC it will return B since the average ASCII code of A and C is B. Probably not very useful but simple enough to show you the concept of deterministic functions.

CREATE OR REPLACE FUNCTION Get_Average_Char(input_ VARCHAR2) RETURN VARCHAR2
IS
  i_   NUMBER;
  sum_ NUMBER := 0;
BEGIN
  FOR i_ IN 1..LENGTH(input_) LOOP
     sum_ := sum_ + ASCII(SUBSTR(input_,i_,1));
  END LOOP;
 
  RETURN CHR(sum_/LENGTH(input_));
END Get_Average_Char;

Now let's try to find all rows where the average character of column B is equal to G:

SELECT COUNT(*) FROM deterministic_test dt WHERE Get_Average_Char(column_b) = 'G';

Even on a fast computer this is going to take a while. Let's look at the explain plan:

Description	Object Name	Cost	Cardinality	Bytes
SELECT STATEMENT, GOAL = ALL_ROWS		566	1	202
SORT AGGREGATE			1	202
TABLE ACCESS FULL	DETERMINISTIC_TEST	566	862	174124

Let's create that function again but this time using the magic keyword DETERMINISTIC:

CREATE OR REPLACE FUNCTION Get_Average_Char(input_ VARCHAR2)
   RETURN VARCHAR2 DETERMINISTIC
IS
  i_   NUMBER;
  sum_ NUMBER := 0;
BEGIN
  FOR i_ IN 1..LENGTH(input_) LOOP
     sum_ := sum_ + ASCII(SUBSTR(input_,i_,1));
  END LOOP;
 
  RETURN CHR(sum_/LENGTH(input_));
END Get_Average_Char;

Now that we have a deterministic function we can tell our database to put an index on the result of that function. It's pretty straight forward - works exactly the same as it does for a normal table column:

CREATE INDEX DETERMINISTIC_TEST_IX1
   ON DETERMINISTIC_TEST (Get_Average_Char(column_b));

Run the same query from above again:

SELECT COUNT(*) FROM deterministic_test dt WHERE Get_Average_Char(column_b) = 'G';

The query returns in no time and when we look at the explain plan we can clearly see why:

Description	Object Name	Cost	Cardinality	Bytes
SELECT STATEMENT, GOAL = ALL_ROWS		1	1	202
SORT AGGREGATE			1	202
INDEX RANGE SCAN	DETERMINISTIC_TEST_IX1	1	862	174124

We were able to replace the FULL SCAN with a much better INDEX RANGE SCAN and suddenly the cost dropped from 566 to 1. In this case Oracle doesn't have to execute the function at all after it created the index. It simply looks for the value in the index and returns the data records matching the requested value.

This works as well for built Oracle methods like UPPER when you want to create a case insensitive query on a database which is set up to do case sensitive matches.

It's not something you'll need on a daily basis but if there's a chance to add the keyword DETERMINISTIC, do it and you'll get a much better result in no time!

MERGE Statement Enhancements in Oracle Database 10g

Posted by HIMANSHU KARKI at 8:11 AM comments (0)

Oracle 10g includes a number of amendments to the MERGE statement making it more flexible.

Test Table
Optional Clauses
Conditional Operations
DELETE Clause

Test Table

The following examples use the table defined below.

CREATE TABLE test1 AS
SELECT *
FROM   all_objects
WHERE  1=2;

Optional Clauses

The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status);

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status;

Conditional Operations

Conditional inserts and updates are now possible by using a WHERE clause on these statements.

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';

DELETE Clause

An optional DELETE WHERE clause can be added to the MATCHED clause to clean up after a merge operation. Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted. Depending on which table the DELETE WHERE references, it can target the rows prior or post update. The following examples clarify this.

Create a source table with 5 rows as follows.

CREATE TABLE source AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 5;

SELECT * FROM source;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5

5 rows selected.

SQL>

Create the destination table using a similar query, but this time with 10 rows.

CREATE TABLE destination AS
SELECT level AS id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 10
         ELSE 20
       END AS status,
       'Description of level ' || level AS description
FROM   dual
CONNECT BY level <= 10;

SELECT * FROM destination;

         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

10 rows selected.

SQL>

The following MERGE statement will update all the rows in the destination table that have a matching row in the source table. The additional DELETE WHERE clause will delete only those rows that were matched, already in the destination table, and meet the criteria of the DELETE WHERE clause.

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET d.description = 'Updated'
    DELETE WHERE d.status = 10;

5 rows merged.

SQL>

SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Updated
         3         20 Updated
         5         20 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

8 rows selected.

SQL>

Notice there are rows with a status of "10" that were not deleted. This is because there was no match between the source and destination for these rows, so the delete was not applicable.

The following example shows the DELETE WHERE can be made to match against values of the rows before the update operation, not after. In this case, all matching rows have their status changed to "10", but the DELETE WHERE references the source data, so the status is checked against the source, not the updated values.

ROLLBACK;

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET  d.description = 'Updated',
                d.status = 10
    DELETE WHERE s.status = 10;

5 rows merged.

SQL>
  
SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         10 Updated
         3         10 Updated
         5         10 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

8 rows selected.

SQL>

Notice, no extra rows were deleted compared to the previous example.

By switching the DELETE WHERE to reference the destination table, the extra updated rows can be deleted also.

ROLLBACK;

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET  d.description = 'Updated',
                d.status = 10
    DELETE WHERE d.status = 10;

5 rows merged.

SQL>
  
SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

5 rows selected.

SQL>

Invalid column type: 16

Posted by HIMANSHU KARKI at 7:46 PM comments (1)

You might have encountered with Error: Invalid column type: 16 or java.sql.SQLException: Invalid column type: 16 when calling a procedure/function in in Java Environment.

Cause of this Error:

We get this error while executing an oracle DB stored function which has return type as "BOOLEAN", in Java Environment:

CREATE OR REPLACE FUNCTION Check_Available_Money (v_card_no VARCHAR2, v_amount NUMBER)
RETURN BOOLEAN
IS
v_cur_Amount NUMBER(5);
BEGIN
SELECT Balance into v_cur_Amount
FROM Card_Details
WHERE cardno = v_card_no;

IF v_cur_Amount> v_amount THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;

/

Solution:

https://forums.oracle.com/message/10572948

BOOLEAN is a PL/SQL type and cannot be used in SQL.

You will need to write you own wrapper function that takes an integer and converts it to a boolean and calls your function.

Or you can use the 'sys.diutil.int_to_bool' which does that conversion.

BOOLEAN Data Type

The PL/SQL data type BOOLEAN stores logical values, which are the Boolean values TRUE and FALSE and the value NULL. NULL represents an unknown value.

Because SQL has no data type equivalent to BOOLEAN, you cannot:

* Assign a BOOLEAN value to a database table column

* Select or fetch the value of a database table column into a BOOLEAN variable

* Use a BOOLEAN value in a SQL statement, SQL function, or PL/SQL function invoked from a SQL statement.

According to Oracle's own FAQ, Oracle's JDBC driver doesn't support boolean datatypes sent/returned from the database. As the FAQ says the solution is:

* For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN, or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC.

* Obviously if you can't change the database you've a problem with this raw JDBC approach. Maybe have a look to JPublisher to see what it can do for you in this case.

Workaround for such situation is:

Creating a another function that uses that and return String:

CREATE OR REPLACE FUNCTION Check_Withdraw_Amount(v_card_no VARCHAR2, v_amount NUMBER)

RETURN VARCHAR2

Exist BOOLEAN;

BEGIN

Exist := Check_Available_Money(v_card_no, v_amount);

IF Exist THEN

RETURN 'TRUE';

ELSE

RETURN 'FALSE';

END IF;

END;

It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

DETERMINISTIC Function and Function Based Index on Deterministic Function

2. DML inside a Deterministic function which is referred in SELECT query is acceptable if it has index created on it else it will through ORA-14551.

DETERMINISTIC Functions

Problem - Searching for data records by a function result

Recipe #1 - Using a deterministic function to improve your query

MERGE Statement Enhancements in Oracle Database 10g

Test Table

Optional Clauses

Conditional Operations

DELETE Clause

Invalid column type: 16

You Might Also Like

Pages

Recent Comments

Translate

CLOCK

Search This Blog

About this blog

Oracle the number one Business solution company also It is most powerful database management system.

In this Blog I will be covering topics on oracle database management system, Working and developing Oracle Application, its Database Administration and Database Development concepts, also bit on Unix OS.

About me

Labels

Blog Archive

My Other Blog