Overview

The RESULT_CACHE feature stores Functions or SQL in memory and returns the cached result immediately without re-executing the SQL when called again with the same input values.

This document describes how to utilize the RESULT_CACHE feature in Tibero.

Note 

This test was conducted in the following environment.

• Tibero 7 (DB 7.2.3) Build 302874
• Linux bistro-build-centos_8.4 5.15.0-134-generic #145~20.04.1-Ubuntu
• SMP Mon Feb 17 13:27:16 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)

Method

Using RESULT_CACHE Option in SELECT Statements

1. Create Table for Testing

SQL> CREATE TABLE tibero.TB_COMMON_CODE (
    MCA_CODE   VARCHAR2(20),
    CODE       VARCHAR2(20),
    CODE_NAME  VARCHAR2(100),
    CODE_DESC  VARCHAR2(200),
    USE_YN     CHAR(1)
);

Table 'TIBERO.TB_COMMON_CODE' created.

SQL> INSERT INTO tibero.TB_COMMON_CODE VALUES ('SYS01', '001', 'Normal', 'Normal code', 'Y');

1 row inserted.

SQL> INSERT INTO tibero.TB_COMMON_CODE VALUES ('SYS01', '002', 'Stopped', 'Stopped code', 'Y');
1 row inserted.

SQL> COMMIT; 

Commit completed.

2. Check Initial Cache Status

SQL> SELECT * FROM V$RESULT_CACHE_OBJECTS;

0 row selected.

3. SQL Without Using RESULT_CACHE

SQL> SELECT CODE_NAME FROM tibero.TB_COMMON_CODE WHERE MCA_CODE = 'SYS01';

CODE_NAME
------------------------------
Normal
Stopped

2 rows selected.

SQL> SELECT ID, NAME, TYPE, STATUS FROM V$RESULT_CACHE_OBJECTS;

0 row selected.

4. Query Using RESULT_CACHE

SQL> SELECT CODE_NAME FROM tibero.TB_COMMON_CODE WHERE MCA_CODE = 'SYS01';

CODE_NAME
------------------------------
Normal
Stopped

2 rows selected.

SQL> SELECT ID, NAME, TYPE, STATUS,PIN_COUNT  FROM V$RESULT_CACHE_OBJECTS;

        ID NAME                                                         TYPE       STATUS
---------- ------------------------------------------------------------ ---------- ---------
         0 TIBERO.TB_COMMON_CODE                                        Dependency Published
         1 SELECT /*+ RESULT_CACHE */ CODE_NAME                         Result     Published
             FROM TIBERO.TB_COMMON_CODE
            WHERE MCA_CODE = 'SYS01'

5. Inducing Cache Invalidation by Data Change

SQL> UPDATE TIBERO.TB_COMMON_CODE
   SET CODE_DESC = 'Modified description'
 WHERE CODE = '001';
1 row updated.

SQL> COMMIT;


SQL> SELECT ID, NAME, STATUS FROM V$RESULT_CACHE_OBJECTS;

        ID NAME                           STATUS
---------- ------------------------------ ---------
         0 TIBERO.TB_COMMON_CODE          Published
         1 SELECT /*+ RESULT_CACHE */ COD Invalid
           E_NAME
             FROM TIBERO.TB_COMMON_CODE
            WHERE MCA_CODE = 'SYS01'
2 rows selected.            

6. Recreate Cache by Re-executing the Same Query

SQL> SELECT ID, NAME, STATUS FROM V$RESULT_CACHE_OBJECTS;

        ID NAME                           STATUS
---------- ------------------------------ ---------
         0 TIBERO.TB_COMMON_CODE          Published
         1 SELECT /*+ RESULT_CACHE */ COD Published
           E_NAME
             FROM TIBERO.TB_COMMON_CODE
            WHERE MCA_CODE = 'SYS01'
2 rows selected.

Using RESULT_CACHE Option with Functions

1. Create Function Including RESULT_CACHE Option

CREATE OR REPLACE FUNCTION TIBERO.Z_SF_COMM002_GETVALUE
/*****************************************************************************
** Overview : Returns the code value by receiving code and field.
** INPUT : Major category code (IN_MCA_CODE), code (IN_CODE), return field (IN_FIELD)
** OUTPUT : Value corresponding to the return field
** Note : Improves performance by utilizing RESULT CACHE on repeated calls with the same input values
*****************************************************************************/
(
    IN_MCA_CODE IN VARCHAR2,
    IN_CODE     IN VARCHAR2,
    IN_FIELD    IN VARCHAR2
)
RETURN VARCHAR2
RESULT_CACHE
IS
    V_RESULT VARCHAR2(4000);
BEGIN
    SELECT CASE IN_FIELD
               WHEN 'CODE_NAME' THEN CODE_NAME
               WHEN 'CODE_DESC' THEN CODE_DESC
               WHEN 'USE_YN'    THEN USE_YN
               ELSE NULL
           END
      INTO V_RESULT
      FROM TIBERO.TB_COMMON_CODE
     WHERE MCA_CODE = IN_MCA_CODE
       AND CODE     = IN_CODE;

    RETURN V_RESULT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
    WHEN OTHERS THEN
        RETURN NULL;
END Z_SF_COMM002_GETVALUE_bak;
/

2. Execute Function

-- First execution (cache creation)
SELECT TIBERO.Z_SF_COMM002_GETVALUE('SYS01', '001', 'CODE_NAME') AS RESULT FROM DUAL;

RESULT
-------------
Normal

1 row selected.

-- Re-execution with same parameters (cache hit)
SELECT TIBERO.Z_SF_COMM002_GETVALUE('SYS01', '001', 'CODE_NAME') AS RESULT FROM DUAL;

RESULT
-------------
Normal

1 row selected.

3. Check RESULT_CACHE Usage

SELECT NAME, TYPE, STATUS, CREATION_TIMESTAMP, ROW_COUNT, SPACE_USED
  FROM V$RESULT_CACHE_OBJECTS
 WHERE NAMESPACE= 'UDF';

NAME                           TYPE       STATUS    CREATION_TIMESTAMP   ROW_CO SPACE_USED
------------------------------ ---------- --------- -------------------- ------ ----------
TIBERO.Z_SF_COMM002_GETVALUE   Result     Published 2025/11/13                1       1167

1 row selected.

RESULT_CACHE Efficiency Analysis Monitoring

SELECT STATUS,
       COUNT(*) AS OBJECT_COUNT,
       SUM(SPACE_USED) AS TOTAL_SPACE_USED
  FROM V$RESULT_CACHE_OBJECTS
 GROUP BY STATUS;
 
STATUS    OBJECT_COUNT TOTAL_SPACE_USED
--------- ------------ ----------------
Invalid              2             2334
Published            3                0

2 rows selected.

Caution

If the number of Invalid statuses in the STATUS column is high, it indicates that the cache is frequently invalidated due to table changes, and it is necessary to review the SQL using the RESULT_CACHE option.