Re: Odd restrictions on Temp table with indexes

Thank you so much Stefan.
I see the GTT index behaviour is expected and there the workaround mentioned is converting it into a Union query. But it looks like it will have additional index scans if it does that and that will have additional overhead. However I did try to convert the sample select query into UNION ALL but not seeing a parallel index scan happening in the plan. Not sure what is missing. (
https://gist.github.com/oracle9999/5409f86ebd155fd103d90c30282309fe)

However , I was thinking if there is any other way out, without adding more overhead we can parallelize these queries. Making the table a normal heap help table will help however, will not help in the current scenario where it is populated/accessed simultaneously from multiple sessions for multiple customers. Is there any other way possible here?

Below are the two queries we were looking to parallelize one is INSERT and the other one is SELECT.

https://gist.github.com/oracle9999/ff2073c222398416c8095d109c233765 https://gist.github.com/oracle9999/618251c1e48b315dc70c73e157443773

On Fri, Jun 30, 2023 at 6:21 PM Stefan Koehler <contact_at_soocs.de> wrote:

> 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: _at_OracleSK
>
> > Pap <oracle.developer35_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l