Skip to Main Content
  • Questions

  • LOB columns in global temporary tables

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Lasse.

Asked: January 09, 2023 - 8:02 am UTC

Last updated: January 16, 2023 - 3:27 am UTC

Version: 19

Viewed 1000+ times

You Asked

Hi


We have a table in Oracle 19 database:

CREATE GLOBAL TEMPORARY TABLE GTT_CLOB
(
  PAYLOAD  CLOB
)
ON COMMIT DELETE ROWS
CACHE;


Payload columns can be rather big, more than 50 kb.


select RETENTION, CACHE, LOGGING, RETENTION_TYPE, SECUREFILE from user_lobs where TABLE_NAME='GTT_CLOB';
18000 YES YES YES NO

Initialization parameters are
temp_undo_enabled TRUE


What is the purpose of RETENTION in these cases ? Is there any way to make handling of these kind of table faster?



lh



and Connor said...

Even though cross-session consistent read is not relevant for a GTT, you can still flash it back, eg

SQL> create global temporary table GTT
  2  ( x int )
  3  on commit preserve rows;

Table created.

SQL>
SQL> insert into gtt values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select sysdate from dual;

SYSDATE
-------------------
13/01/2023 10:02:11

SQL> insert into gtt values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into gtt values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into gtt values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into gtt values (2);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from gtt;

         X
----------
         1
         2
         2
         2
         2

SQL> select * from gtt as of timestamp timestamp '2023-01-13 10:02:12';

         X
----------
         1


Now for a GTT that use case is unlikely but much more likely is that you could have an open cursor on a GTT before you do more DML on it, so we need to be able to in-session consistent read.

So this means we need to have undo information for the table, and for a LOB that means we need to keep old copies of the LOB, hence the retention.

What kind of "handling" do you need to make faster?

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Trying to clarify the problem

lh, January 13, 2023 - 9:08 am UTC

Hi

Thank You about the answer and sorry that I couldn't be more clear in asking the question.

I was curious about the meaning of retention attribute while using on commit delete global temporary tables.
Has it any relevance? Has e.g. logging attribute for lob columns any relevance in this case.

We don't have any specific performance problems; just trying to make datastructures to work as efficiently as possible
and avoid unneeded work.


lh
Connor McDonald
January 16, 2023 - 3:27 am UTC

Ah sorry - I missed the "delete" part.

You still need undo for consistent read, eg

SQL> create global temporary table GTT
  2      ( x int )
  3      on commit delete rows;

Table created.

SQL>
SQL>
SQL> insert into gtt values (2);

1 row created.

SQL>
SQL> variable rc refcursor
SQL> exec open :rc for select * from gtt;

PL/SQL procedure successfully completed.

SQL>
SQL> insert into gtt values (3);

1 row created.

SQL> insert into gtt values (4);

1 row created.

SQL>
SQL> select * from gtt;

         X
----------
         2
         3
         4

SQL>
SQL> print rc

         X
----------
         2


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.