Re: Global temporary tables

Hi,

 

I am very interested in this feature that will conform to the SQL standard and I read that :

 

 

There is a confusion. Sadly it does not work like that at all with Oracle. Their implementation is buggy in my opinion.

Here is a very simple test case to prove it with the latest version (january 2020) :

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.6.0.0.0

 

-- session 1

create global temporary table gtt(x integer);

Table created.

 

-- session 2

insert into gtt SELECT level FROM dual CONNECT BY LEVEL <= 100000;

100000 rows created.

 

-- session 1

create index igtt on gtt(x);

Index created.

 

-- session 2

select * from gtt where x = 9;

 

no rows selected

 

select /*+ FULL(gtt) */ * from gtt where x = 9;

 

         X

----------

         9

 

What happened ? The optimizer (planner) knows the new index igtt can be efficient via dynamic sampling. Hence, igtt is used at execution time...but it is NOT populated. By default I obtained no line. If I force a full scan of the table with a hint /*+ FULL */ you can see that I obtain my line 9. Different results with different exec plans it's a WRONG RESULT bug, the worst kind of bugs.

Please don't consider Oracle as a reference for your implementation. I am 100% sure you can implement and document that better than Oracle. E.g index is populated and considered only  for transactions that started after the index creation or something like that. It would be far better than this misleading behaviour.

Regards,

Phil