create table olym_medals_json as
select json_object (
'city' is city,
'edition' is edition,
'sport' is sport,
'discipline' is discipline,
'athlete' is athlete,
'noc' is noc,
'gender' is gender,
'event' is event,
'event_gender' is event_gender,
'medal' is medal
) jdoc
from olym.olym_medals_view
where rownum <= 10000Table created.
alter table olym_medals_json add constraint j_ck check (jdoc is json)Table altered.
create search index olym_medals_json_i on olym_medals_json (jdoc) for jsonIndex created.
exec dbms_stats.gather_table_stats(sys_context('userenv', 'current_user'), 'olym_medals_json')Statement processed.
select /*+ gather_plan_statistics JSONGW */*
from olym_medals_json
where json_value ( jdoc, '$.medal') = 'Gold'
and json_value ( jdoc, '$.gender') = 'Women'
and json_value ( jdoc, '$.edition') = 2000
and rownum <= 10| JDOC | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Diving","athlete":"ILYINA, Vera","noc":"RUS","gender":"Women","event":"synchronized diving 3m springboard","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Diving","athlete":"PAKHALINA, Ioulia","noc":"RUS","gender":"Women","event":"synchronized diving 3m springboard","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"MOCANU, Diana","noc":"ROU","gender":"Women","event":"100m backstroke","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"QUANN, Megan","noc":"USA","gender":"Women","event":"100m breaststroke","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"DE BRUIJN, Inge","noc":"NED","gender":"Women","event":"100m butterfly","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"DE BRUIJN, Inge","noc":"NED","gender":"Women","event":"100m freestyle","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"MOCANU, Diana","noc":"ROU","gender":"Women","event":"200m backstroke","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"KOVACS, Agnes","noc":"HUN","gender":"Women","event":"200m breaststroke","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"HYMAN, Misty","noc":"USA","gender":"Women","event":"200m butterfly","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"O'NEILL, Susan","noc":"AUS","gender":"Women","event":"200m freestyle","event_gender":"W","medal":"Gold"} |
|---|
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text like '%JSONGW%'
and s.sql_text not like '%not this%'| PLAN_TABLE_OUTPUT | SQL_ID d2cy04jmc0zzr, child number 0 | ------------------------------------- | select /*+ gather_plan_statistics JSONGW */* from | olym_medals_json where json_value ( jdoc, '$.medal') = 'Gold' | and json_value ( jdoc, '$.gender') = 'Women' and json_value ( | jdoc, '$.edition') = 2000 and rownum <= 10 | Plan hash value: 2807262565 | ------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 120 | | |* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 120 | | |* 2 | TABLE ACCESS BY INDEX ROWID| OLYM_MEDALS_JSON | 1 | 1 | 10 |00:00:00.01 | 120 | | |* 3 | DOMAIN INDEX | OLYM_MEDALS_JSON_I | 1 | | 121 |00:00:00.01 | 94 | | ------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter(ROWNUM<=10) | 2 - filter((JSON_VALUE("JDOC" FORMAT JSON , '$.medal' RETURNING VARCHAR2(4000) NULL ON | ERROR)='Gold' AND JSON_VALUE("JDOC" FORMAT JSON , '$.gender' RETURNING VARCHAR2(4000) NULL ON | ERROR)='Women' AND TO_NUMBER(JSON_VALUE("JDOC" FORMAT JSON , '$.edition' RETURNING VARCHAR2(4000) | NULL ON ERROR))=2000)) | 3 - access("CTXSYS"."CONTAINS"("OLYM_MEDALS_JSON"."JDOC",'({Gold} INPATH (/medal)) and ({Women} | INPATH (/gender))')>0) | SQL_ID f1ks9yjggx5rd, child number 0 | ------------------------------------- | select /*+ gather_plan_statistics JSONGW */* from olym_medals_json | where json_value ( jdoc, '$.medal') = 'Gold' and json_value ( | jdoc, '$.gender') = 'Women' and json_value ( jdoc, '$.edition') = | 2000 | Plan hash value: 602444116 | ------------------------------------------------------------------------------------------------------------ | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ------------------------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1589 | | |* 1 | TABLE ACCESS BY INDEX ROWID| OLYM_MEDALS_JSON | 1 | 1 | 0 |00:00:00.01 | 1589 | | |* 2 | DOMAIN INDEX | OLYM_MEDALS_JSON_I | 1 | | 0 |00:00:00.01 | 1589 | | ------------------------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter((JSON_VALUE("JDOC" FORMAT JSON , '$.medal' RETURNING VARCHAR2(4000) NULL ON |
|---|
select /*+ gather_plan_statistics JSONSEARCH */*
from olym_medals_json
where json_textcontains ( jdoc, '$', 'Gold and 2000 and Women')
and rownum <= 10| JDOC | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Diving","athlete":"ILYINA, Vera","noc":"RUS","gender":"Women","event":"synchronized diving 3m springboard","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Diving","athlete":"PAKHALINA, Ioulia","noc":"RUS","gender":"Women","event":"synchronized diving 3m springboard","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"MOCANU, Diana","noc":"ROU","gender":"Women","event":"100m backstroke","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"QUANN, Megan","noc":"USA","gender":"Women","event":"100m breaststroke","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"DE BRUIJN, Inge","noc":"NED","gender":"Women","event":"100m butterfly","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"DE BRUIJN, Inge","noc":"NED","gender":"Women","event":"100m freestyle","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"MOCANU, Diana","noc":"ROU","gender":"Women","event":"200m backstroke","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"KOVACS, Agnes","noc":"HUN","gender":"Women","event":"200m breaststroke","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"HYMAN, Misty","noc":"USA","gender":"Women","event":"200m butterfly","event_gender":"W","medal":"Gold"} | {"city":"Sydney","edition":2000,"sport":"Aquatics","discipline":"Swimming","athlete":"O'NEILL, Susan","noc":"AUS","gender":"Women","event":"200m freestyle","event_gender":"W","medal":"Gold"} |
|---|
select p.*
from v$sql s, table (
dbms_xplan.display_cursor (
s.sql_id, s.child_number, 'ALLSTATS LAST'
)
) p
where s.sql_text like '%JSONSEARCH%'
and s.sql_text not like '%not this%'| PLAN_TABLE_OUTPUT | SQL_ID ckwy5n5mn13u2, child number 0 | ------------------------------------- | select /*+ gather_plan_statistics JSONSEARCH */* from | olym_medals_json where json_textcontains ( jdoc, '$', 'Gold and 2000 | and Women') and rownum <= 10 | Plan hash value: 2807262565 | ------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 49 | | |* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 49 | | | 2 | TABLE ACCESS BY INDEX ROWID| OLYM_MEDALS_JSON | 1 | 10 | 10 |00:00:00.01 | 49 | | |* 3 | DOMAIN INDEX | OLYM_MEDALS_JSON_I | 1 | | 10 |00:00:00.01 | 39 | | ------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter(ROWNUM<=10) | 3 - access("CTXSYS"."CONTAINS"("OLYM_MEDALS_JSON"."JDOC",'Gold and 2000 and Women')>0) | SQL_ID 4tkd6fnpg61tx, child number 0 | ------------------------------------- | select /*+ gather_plan_statistics JSONSEARCH */* from | olym_medals_json where json_textcontains ( jdoc, '$', 'Gold and 2000 | and Women') | Plan hash value: 602444116 | ------------------------------------------------------------------------------------------------------------ | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ------------------------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 1166 | | | 1 | TABLE ACCESS BY INDEX ROWID| OLYM_MEDALS_JSON | 1 | 13 | 0 |00:00:00.01 | 1166 | | |* 2 | DOMAIN INDEX | OLYM_MEDALS_JSON_I | 1 | | 0 |00:00:00.01 | 1166 | | ------------------------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - access("CTXSYS"."CONTAINS"("OLYM_MEDALS_JSON"."JDOC",'Gold and 2000 and Women')>0) |
|---|