Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

JSON - nv pair in an array

GS613Jun 4 2021

Greetings
On 19c
I am creating views on JSON objects and am struggling to figure out how I can extract the value of key-value pair inside an array when the specific key that I need could be anywhere in the array.
Attached is a file containing 2 JSON objects.
Inside the array is a field called "ga_session_id" which could be in any position of the array.
I want to extract that field always. The SQL should return 2 rows, with a populated ga_session_id key as a column.
Any help would be greatly appreciated!

data.txt (1000 Bytes)
This post has been answered by Solomon Yakobson on Jun 4 2021
Jump to Answer

Comments

SeánMacGC 
Sekar_BLUE4EVER wrote:
Hi I am trying to implement partitioning in a complex environment where there are two many join operations in most of the SQL's. I was reading about the partition wise join operation where join on tables will be paralleled if the keys are the same and the boundaries are same as well. 
I am trying to do a basic test case on a simple table if partition wise join works , while it works when there is no parallel operation the partition wise joins don't seem to work with parallel hint.

On what are you basing this observation, ie, why do you believe that partition wise joins are not in operation when parallelized?

Sekar_BLUE4EVER

This links shows a sample plan in case parallel partition wise joins are used . https://docs.oracle.com/database/121/VLDBG/GUID-36B5A01D-849E-44A3-B6D7-2B82DD23DB73.htm And its completely of different format to what I get

SeánMacGC 
Sekar_BLUE4EVER wrote:
This links shows a sample plan in case parallel partition wise joins are used . https://docs.oracle.com/database/121/VLDBG/GUID-36B5A01D-849E-44A3-B6D7-2B82DD23DB73.htm And its completely of different format to what I get

That's not reliable -- you're comparing two different versions of the optimizer to start with, and you're not comparing like with like (you don't have as much info in your explain plans), and there's nothing I can see in your parallel plan to demonstrate that partition-wise joins are not in play.

JohnWatson2

You can try hacking your statistics. For example, this gives me a parallel partition wise join:

orclx> exec dbms_stats.delete_table_stats(user,'t1h')
PL/SQL procedure successfully completed.
orclx> exec dbms_stats.delete_table_stats(user,'t2h')
PL/SQL procedure successfully completed.
orclx> alter session set optimizer_dynamic_sampling=0;
Session altered.
orclx> set autot trace exp
orclx> select /*+ parallel(2) */ count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x = t2.x;
Execution Plan
----------------------------------------------------------
Plan hash value: 1341648614
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |          |    1 |    52 |  811  (1)| 00:00:01 |      |      |        |      |            |
|  1 |  SORT AGGREGATE          |          |    1 |    52 |            |          |      |      |        |      |            |
|  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            |
|  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    52 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  |
|  4 |    SORT AGGREGATE        |          |    1 |    52 |            |          |      |      |  Q1,00 | PCWP |            |
|  5 |      PX PARTITION HASH ALL|          |  328K|    16M|  811  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |
|*  6 |      HASH JOIN          |          |  328K|    16M|  811  (1)| 00:00:01 |      |      |  Q1,00 | PCWP |            |
|  7 |        TABLE ACCESS FULL  | T1H      |  328K|  8345K|  404  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |
|  8 |        TABLE ACCESS FULL  | T2H      |  328K|  8345K|  404  (1)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  6 - access("T1"."X"="T2"."X")
Note
-----
  - Degree of Parallelism is 2 because of hint
orclx>
Sekar_BLUE4EVER

The plan which i get doesnt have PX PARTITION HASH ALL which means that the partition wise join is not used . Its explained in the oracle blog here https://blogs.oracle.com/datawarehousing/partition-wise-joins-ii

1 person found this helpful
SeánMacGC 
Sekar_BLUE4EVER wrote:
The plan which i get doesnt have PX PARTITION HASH ALL which means that the partition wise join is not used . Its explained in the oracle blog here https://blogs.oracle.com/datawarehousing/partition-wise-joins-ii

OK, have you now, and some more about it here: https://blogs.oracle.com/datawarehousing/partition-wise-joins

Sekar_BLUE4EVER 
SM Join
  SM cost: 116.27     (partition-wise join)

Thanks. I tried enabling 10053 trace and the optimizer doesnt even consider partition-wise-join in the trace where as without parallel hint I can clearly see that the optimizer is considering the PWJ and using it at the end. Could there be some optimizer parameters which are affecting this?

AndrewSayer

It seems to do the expected PWJ when you hint for a DOP equal to the number of partitions:

sql>explain plan for

  2  select /*+parallel(4)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x= t2.x;

Explained.

PLAN_TABLE_OUTPUT

________________________________________________________________________________________________________________________________________

Plan hash value: 1341648614

-----------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT          |          |    1 |    10 |    76  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE          |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    10 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE        |          |    1 |    10 |            |          |      |      |  Q1,00 | PCWP |            |

|  5 |      PX PARTITION HASH ALL|          | 59998 |  585K|    76  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|*  6 |      HASH JOIN          |          | 59998 |  585K|    76  (0)| 00:00:01 |      |      |  Q1,00 | PCWP |            |

|  7 |        TABLE ACCESS FULL  | T1H      | 29999 |  292K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  8 |        TABLE ACCESS FULL  | T2H      | 59998 |  585K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

-----------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

  1 - SEL$1

  7 - SEL$1 / T1@SEL$1

  8 - SEL$1 / T2@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" NONE NONE)

      USE_HASH(@"SEL$1" "T2"@"SEL$1")

      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T1"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

  6 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 4 because of hint

If I double that to 8, I get:

sql>explain plan for

  2  select /*+parallel(8)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x= t2.x;

Explained.

PLAN_TABLE_OUTPUT

_____________________________________________________________________________________________________________________________________________

Plan hash value: 3645041885

----------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT              |          |    1 |    10 |    38  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE                |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR              |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)        | :TQ10001 |    1 |    10 |            |          |      |      |  Q1,01 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE            |          |    1 |    10 |            |          |      |      |  Q1,01 | PCWP |            |

|*  5 |      HASH JOIN                |          | 59998 |  585K|    38  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  6 |      PART JOIN FILTER CREATE  | :BF0000  | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  7 |        PX RECEIVE              |          | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,01 | PCWP |            |

|  8 |        PX SEND BROADCAST LOCAL| :TQ10000 | 29999 |  292K|    19  (0)| 00:00:01 |      |      |  Q1,00 | P->P | BCST LOCAL |

|  9 |          PX BLOCK ITERATOR    |          | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|  10 |          TABLE ACCESS FULL    | T1H      | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  11 |      PX BLOCK ITERATOR        |          | 59998 |  585K|    19  (0)| 00:00:01 |:BF0000|:BF0000|  Q1,01 | PCWC |            |

|  12 |        TABLE ACCESS FULL      | T2H      | 59998 |  585K|    19  (0)| 00:00:01 |:BF0000|:BF0000|  Q1,01 | PCWP |            |

----------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

  1 - SEL$1

  10 - SEL$1 / T1@SEL$1

  12 - SEL$1 / T2@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      PQ_MAP(@"SEL$1" "T2"@"SEL$1")

      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" BROADCAST NONE)

      USE_HASH(@"SEL$1" "T2"@"SEL$1")

      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T1"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

  5 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 8 because of hint

58 rows selected.

Note the different distribution method chosen. You can hint for the initial one though:

sql>explain plan for

  2  select /*+parallel(8) PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" NONE NONE)*/count(t1.y), count(t2.y)    from t1h t1,t2h t2    where t1.x

Explained.

PLAN_TABLE_OUTPUT

________________________________________________________________________________________________________________________________________

Plan hash value: 1341648614

-----------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                | Name    | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT          |          |    1 |    10 |    38  (0)| 00:00:01 |      |      |        |      |            |

|  1 |  SORT AGGREGATE          |          |    1 |    10 |            |          |      |      |        |      |            |

|  2 |  PX COORDINATOR          |          |      |      |            |          |      |      |        |      |            |

|  3 |    PX SEND QC (RANDOM)    | :TQ10000 |    1 |    10 |            |          |      |      |  Q1,00 | P->S | QC (RAND)  |

|  4 |    SORT AGGREGATE        |          |    1 |    10 |            |          |      |      |  Q1,00 | PCWP |            |

|  5 |      PX PARTITION HASH ALL|          | 59998 |  585K|    38  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWC |            |

|*  6 |      HASH JOIN          |          | 59998 |  585K|    38  (0)| 00:00:01 |      |      |  Q1,00 | PCWP |            |

|  7 |        TABLE ACCESS FULL  | T1H      | 29999 |  292K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

|  8 |        TABLE ACCESS FULL  | T2H      | 59998 |  585K|    19  (0)| 00:00:01 |    1 |    4 |  Q1,00 | PCWP |            |

-----------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

  1 - SEL$1

  7 - SEL$1 / T1@SEL$1

  8 - SEL$1 / T2@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      PQ_DISTRIBUTE(@"SEL$1" "T2"@"SEL$1" NONE NONE)

      USE_HASH(@"SEL$1" "T2"@"SEL$1")

      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T2"@"SEL$1")

      FULL(@"SEL$1" "T1"@"SEL$1")

      OUTLINE_LEAF(@"SEL$1")

      ALL_ROWS

      OPT_PARAM('optimizer_index_cost_adj' 50)

      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

      OPT_PARAM('_px_adaptive_dist_method' 'off')

      DB_VERSION('12.1.0.2')

      OPTIMIZER_FEATURES_ENABLE('12.1.0.2.1')

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

  6 - access("T1"."X"="T2"."X")

Note

-----

  - Degree of Parallelism is 8 because of hint

53 rows selected.

Sekar_BLUE4EVER

Thanks Andrew, i tried it and yes I get the same behaviour as well. But it doesnt make much sense if PWJ is only going to be used when the DOP is equal to the number of partitions. Could it be a bug ? What would be the problem in using PWJ when using DOP as 2 for tables with 4 partitions ? Couldn't it do PWJ 2 time once for P1 and P2 and the second time around for P3 and P4?

SUPRIYO DEY

without using hint even you can achieve the same. But you need to enable parallel in table level. Which i don't find in your case.

please look at the demo:-

SQL> alter table t1h parallel;

Table altered.

SQL>  alter table t2h parallel;

Table altered.

SQL> select count(t1.y), count(t2.y)    from t1h t1,t2h t2     where t1.x= t2.x;

Execution Plan

----------------------------------------------------------

Plan hash value: 593443938

--------------------------------------------------------------------------------

---------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time

     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

---------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |    52 |    29   (0)| 00:0

0:01 |       |       |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    52 |            |

     |       |       |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |

     |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |    52 |            |

     |       |       |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    52 |            |

     |       |       |  Q1,00 | PCWP |            |

|   5 |      PX PARTITION HASH ALL|          | 26732 |  1357K|    29   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWC |            |

|*  6 |       HASH JOIN           |          | 26732 |  1357K|    29   (0)| 00:0

0:01 |       |       |  Q1,00 | PCWP |            |

|   7 |        TABLE ACCESS FULL  | T2H      | 26731 |   678K|    14   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

|   8 |        TABLE ACCESS FULL  | T1H      | 26833 |   681K|    14   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

--------------------------------------------------------------------------------

---------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("T1"."X"="T2"."X")

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         57  recursive calls

          0  db block gets

        514  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         11  sorts (memory)

          0  sorts (disk)

          1  rows processed

in this case automatic degree of parallelism is used by Oracle.

even if you go for parallel hint of DOP 2 still it happen

SQL> select /*+ parallel(2) */ count(t1.y), count(t2.y)    from t1h t1,t2h t2     where t1.x = t2.x;

Execution Plan

----------------------------------------------------------

Plan hash value: 593443938

--------------------------------------------------------------------------------

---------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time

     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------

---------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |    52 |    57   (0)| 00:0

0:01 |       |       |        |      |            |

|   1 |  SORT AGGREGATE           |          |     1 |    52 |            |

     |       |       |        |      |            |

|   2 |   PX COORDINATOR          |          |       |       |            |

     |       |       |        |      |            |

|   3 |    PX SEND QC (RANDOM)    | :TQ10000 |     1 |    52 |            |

     |       |       |  Q1,00 | P->S | QC (RAND)  |

|   4 |     SORT AGGREGATE        |          |     1 |    52 |            |

     |       |       |  Q1,00 | PCWP |            |

|   5 |      PX PARTITION HASH ALL|          | 26732 |  1357K|    57   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWC |            |

|*  6 |       HASH JOIN           |          | 26732 |  1357K|    57   (0)| 00:0

0:01 |       |       |  Q1,00 | PCWP |            |

|   7 |        TABLE ACCESS FULL  | T2H      | 26731 |   678K|    29   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

|   8 |        TABLE ACCESS FULL  | T1H      | 26833 |   681K|    28   (0)| 00:0

0:01 |     1 |     4 |  Q1,00 | PCWP |            |

--------------------------------------------------------------------------------

---------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   6 - access("T1"."X"="T2"."X")

Note

-----

   - dynamic sampling used for this statement (level=2)

   - Degree of Parallelism is 2 because of hint

Statistics

----------------------------------------------------------

         47  recursive calls

          0  db block gets

        514  consistent gets

          0  physical reads

          0  redo size

        609  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         11  sorts (memory)

          0  sorts (disk)

          1  rows processed

From the DOC its mentioned that

https://docs.oracle.com/cd/B28359_01/server.111/b32024/part_avail.htm#CIHCDBIF

Considerations for full partition-wise joins also apply to partial partition-wise joins:

  • The degree of parallelism does not need to equal the number of partitions. In Figure 4-3, the query executes with two sets of 16 query servers. In this case, Oracle assigns 1 partition to each query server of the second set. Again, the number of partitions should always be a multiple of the degree of parallelism.
Sekar_BLUE4EVER

Strange enough I replicated the exact same steps as you have given but it doesnt work for me . I tried enabling parallel 2 for the tables and the result is the same (PWJ not used). The only case when PWJ was used was when I set parallel for the tables to same as number of partitions. I am  testing it in version 11.2.0.3 and 11.2.0.1  and the result is the same. Practically it wouldnt be possible to have the same number of parallel processes as number of partitions. Can you tell me the version you tested on?

SUPRIYO DEY

11.2.0.4

as per doc

Considerations for full partition-wise joins also apply to partial partition-wise joins:

  • The degree of parallelism does not need to equal the number of partitions. In Figure 4-3, the query executes with two sets of 16 query servers. In this case, Oracle assigns 1 partition to each query server of the second set. Again, the number of partitions should always be a multiple of the degree of parallelism.
Mohamed Houri 

Sekar_BLUE4EVER wrote:
Thanks Andrew, i tried it and yes I get the same behaviour as well. But it doesnt make much sense if PWJ is only going to be used when the DOP is equal to the number of partitions. Could it be a bug ? What would be the problem in using PWJ when using DOP as 2 for tables with 4 partitions ? Couldn't it do PWJ 2 time once for P1 and P2 and the second time around for P3 and P4?

You know that "partition-wise" join has been used when you see that the partition operation is above the join operation.

But one important requirement for the partition-wise join to occur is that it requires from Oracle to read the partitioned object via Partition granule i.e. you see PX PARTITION in the execution plan. If a Block granule is used (PX BLOCK ITERATOR) instead of Partition granule (PX PARTITION) then partition wise join will not kick in.

Best regards

Mohamed Houri

1 - 13

Post Details

Added on Jun 4 2021
#sql
4 comments
476 views
1 mention