Re: Odd restrictions on Temp table with indexes

The hint in the main query should use the query block name set$1, not select$1. (And the parallel(10) hints in the two subqueries are redundant). Here's an execution plan from an SQL monitor report that shows the plan (after a little hacking of your script to get some data into the table) and indications that parallelism has happened:

Global Stats

 0.40 |        |             |


| p000           | Set 1 |       1 |    0.00 |    0.00 |             |


    |        |             |


| p001           | Set 1 |       2 |    0.00 |    0.00 |             |


    |        |             |


| p002           | Set 1 |       3 |    0.00 |    0.00 |             |


    |        |             |


| p003           | Set 1 |       4 |    0.00 |    0.00 |             |


    |      3 |             |


| p004           | Set 1 |       5 |    0.00 |    0.00 |        0.00 |


    |      2 |             |


| p005           | Set 1 |       6 |    0.00 |    0.00 |             |


    |        |             |


| p006           | Set 1 |       7 |    0.00 |    0.00 |             |


    |        |             |


| p007           | Set 1 |       8 |    0.00 |    0.00 |             |


    |        |             |


| p008           | Set 1 |       9 |    0.00 |    0.00 |             |


    |        |             |


| p009           | Set 1 |      10 |    0.00 |    0.00 |             |


    |        |             |
========================================================================================================

  |           |        |    11 |          |          |                 |


|  1 |   PX COORDINATOR                           |          |         |


  |           |        |    11 |          |          |                 |


|  2 |    PX SEND QC (RANDOM)                     | :TQ10000 |       2 |


4 |           |        |    10 |          |          |                 |


|  3 |     VIEW                                   |          |       2 |


4 |           |        |    10 |          |          |                 |


|  4 |      UNION-ALL                             |          |         |


  |           |        |    10 |          |          |                 |


|  5 |       PX SELECTOR                          |          |         |


  |           |        |    10 |          |          |                 |


|  6 |        TABLE ACCESS BY INDEX ROWID BATCHED | X        |       1 |


2 |           |        |     1 |          |          |                 |


|  7 |         INDEX RANGE SCAN                   | INDX11   |       1 |


1 |           |        |     1 |          |          |                 |


|  8 |       PX SELECTOR                          |          |         |


  |           |        |    10 |          |          |                 |


|  9 |        TABLE ACCESS BY INDEX ROWID BATCHED | X        |       1 |


2 |           |        |     1 |          |          |                 |


| 10 |         INDEX RANGE SCAN                   | INDX11   |       1 |


1 |           |        |     1 |          |          |                 |
====================================================================================================================================================

You'll notice that the parallel execution details show that 2 out of the 10 PX processes have visited some buffers, and that the plan shows PX SELECTOR operations.

Each index scan is a serial index scan, but I believe the optimizer has selected a separate PX process for each of the range scans, so that two range scans can run concurrently. I'd want to do a variety of experiments with larger sets of data and varying degrees of parallelism and branches of UNION ALL before making any confident claims for the exact mechanisms used, though.

Regards
Jonathan Lewis

On Sat, 1 Jul 2023 at 05:03, Pap <oracle.developer35_at_gmail.com> wrote:

> 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