Hello Pap,
I guess this blog post describes everything you need to know (and how to
possibly work around).
https://iusoltsev-wordpress-com.translate.goog/2019/07/10/index-on-temp-table-is-not-parallelized/?_x_tr_sl=auto&_x_tr_tl=en&_x_tr_hl=en&_x_tr_pto=wapp
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Pap <oracle.developer35@xxxxxxxxx> hat am 30.06.2023 13:14 CEST geschrieben:--
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 forrepresentationpurpose.
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
