XSTORE And CSTORE 2

Just how long is forever? In the case of the “one minute CSTORE cursor” from the previous post it’s as long as the cursor remains parsed. A full discussion of cursor parsing and retention is well beyond the scope of this article so we’ll just define it as forever.

As a matter of fact I kept trying the query for 6 straight days while the database was up! Well, that’s not true – I took an hour off for a delicious dinner. That’s not true either – the dinner was not delicious.

Here are the results 6 days later!

-- That cursor still has the same OPEN date
-- so the error is still raised
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 15463
NOW  20190209 133517

-- A new WHERE clause creates a new cursor so that
-- works...for one minute at least.
SELECT *
  FROM poly_func(table1)
 WHERE col1 = 19;
      COL1       COL2       COL3
---------- ---------- ----------
        19         99          9
        19         98          8
DESCRIBE 09-FEB-19
OPEN 20190209 133553
NOW  20190209 133553

-- The original still fails
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 15463
NOW  20190209 133604

And for the sake of clarity I re-ran the XSTORE cursor on ten thousand rows. Here are the abbreviated results confirming the XSTORE values increasing with each rowset but always starting at 11 because the XSTORE values clean out after the PTF executes.

SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
12
13
14
15
16
17
18
19
20

SELECT *
  FROM poly_func(table1);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
12
13
14
15
16
17
18
19
20

WITH x AS ( SELECT *
              FROM poly_func(table1) )
SELECT *
  FROM poly_func(x);

      COL1       COL2       COL3
---------- ---------- ----------
         1          1          1
         2          2          2
...
      9999       9999       9999
     10000      10000      10000
10000 rows selected.
11
11
12
12
13
13
14
14
15
15
16
16
17
17
18
18
19
19
20
20

Thanks for reading!