XSTORE And CSTORE

This post continues the discussion of XSTORE values with a demonstration of their life span and compares it to Compile State Management – aka CSTORE – values.

Here’s a simple FETCH_ROWS procedure to demonstrate the life span of XSTORE values.

PROCEDURE fetch_rows IS
  xstore_number NUMBER := 0;
BEGIN
  -- if any values are defined for the X1 key
  -- then retrieve the numeric one and increment
  -- it by 1
  -- if none are defined then add a numeric value
  -- of 11
  IF DBMS_TF.XSTORE_EXISTS('X1') THEN
    DBMS_TF.XSTORE_GET('X1',xstore_number);
    DBMS_TF.XSTORE_SET('X1',xstore_number + 1);
  ELSE
    DBMS_TF.XSTORE_SET('X1',11);
  END IF;
  DBMS_TF.XSTORE_GET('X1',xstore_number);
  DBMS_OUTPUT.PUT_LINE(xstore_number);
END;

And some queries:

-- query 1
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
11

-- query 2
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
11

-- query 3
WITH x AS ( SELECT *
              FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);
COL1
----
  19
  19
11
11

Query 1 ran as expected – the X1 XSTORE did not exist so its numeric value was set to 11 and displayed. Query 2 also found that X1 did not exist because it was a whole new execution of the PTF.

The result of Query 3 shows that even WITH clauses retain their own distinct XSTORE because they are separate executions.

Lets compare that with Compile State Execution (CSTORE) values. Here is some example code to introduce CSTORE workings.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
  RETURN DBMS_TF.DESCRIBE_T AS
  v_dat DBMS_TF.CSTORE_DAT_T;
BEGIN
  --
  -- Save the current date with the cursor in a compile
  -- state variable (CSTORE) named PARSE_DATE which is
  -- attached directly to cursor
  --
  v_dat('PARSE DATE') := SYSDATE;
  DBMS_OUTPUT.PUT_LINE('DESCRIBE ' || 
                        v_dat('PARSE DATE'));
  RETURN DBMS_TF.DESCRIBE_T( cstore_dat => v_dat );
END;

PROCEDURE open IS
  v_parse_date DATE;
BEGIN
  DBMS_TF.CSTORE_GET('PARSE DATE',v_parse_date);
  -- output cursor PARSE_DATE and current date
  DBMS_OUTPUT.PUT_LINE('OPEN ' || 
       TO_CHAR(v_parse_date,'YYYYMMDD HH24MISS'));
  DBMS_OUTPUT.PUT_LINE('NOW  ' || 
       TO_CHAR(SYSDATE ,'YYYYMMDD HH24MISS'));
  -- if cursor was parsed more than 1 minute ago
  -- then fail
  IF v_parse_date < sysdate - ( 1 / 24 / 60 ) THEN
    RAISE_APPLICATION_ERROR(-20000,
                 'Cursor is too old');
  END IF;
END;

The important line to note is this:

RETURN DBMS_TF.DESCRIBE_T( cstore_dat => v_dat );

The DESCRIBE function returns the CSTORE values as attributes of the parsed cursor. Thus they stay with that cursor forever. Let’s run some queries to verify that.

-- Query 1 (works fine)
SELECT *
  FROM poly_func(table1);
COL1
----
  19
  19
DESCRIBE 03-FEB-19
OPEN 20190203 154635
NOW  20190203 154635

-- Query 2 (10 seconds after Query 1 works fine)
SELECT *
  FROM poly_func(table1)
COL1
----
  19
  19
OPEN 20190203 154635
NOW  20190203 154645

-- Query 3 (1 minute and 13 seconds after Query 1)
-- fails because we arbitrarily deemed the cursor
-  to be too old
SELECT *
  FROM poly_func(table1)
SELECT *
*
ERROR at line 1:
ORA-20000: Cursor is too old
ORA-06512: at "D.POLY_PKG", line 34
OPEN 20190203 154635
NOW  20190203 154748

The CSTORE value of PARSE DATE stayed with the cursor through each execution. After 1 minute the query will not run again.

The next few articles will discuss State Management (CSTORE and XSTORE) with examples that are more realistic than this one.

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *

80 ÷ = 16