Odd restrictions on Temp table with indexes

From: Pap <oracle.developer35_at_gmail.com>
Date: Fri, 30 Jun 2023 16:44:29 +0530
Message-ID: <CAEjw_fj7v4GE49VofWybbfsBZTjPAaCWmS9sXnhcPmrjnbYkCw_at_mail.gmail.com>



Hello Listers,

There was another thread in which i found this restriction on global temporary tables. Wanted to have this put separately so as to make it bit clearer. And also to ensure, i am not assuming things wrong here. As this behaviour is little odd and i never heard of or read anywhere. So wanted to check with you all if this behaviour is correct that the Index access in Global temporary table can not be parallelized either for read or write operation? And if any workaround possible in such scenarios to make the operation run in parallel?

And also we have a customer application in which global temporary tables are used extensively and indexes are used to query those. I agree that in an ideal scenario we should not scan GTTs using indexes. However as i mentioned as part of the current design we have common procedures gets executed for different customers at same time from different sessions and populate same GTT for each of them. And then data is being fetched by each of the customer from the GTT then. And for some the index access is what is optimal for querying and grouping in certain way for representation purpose.

Below is the test case which i have created for both Normal heap table VS global temporary table. It shows the index access cant be parallelized while reading the GTT. And also you cant write on the GTT if it has the indexes present in it.

https://gist.github.com/oracle9999/909b1477a321cd2b4e49eb81327687eb

Regards

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 30 2023 - 13:14:29 CEST

Original text of this message