JSON and materialized views (Asktom Office hours, Feb 12th 2019)

View All Scripts Login to Run Script

Script Name JSON and materialized views (Asktom Office hours, Feb 12th 2019)
Description JSON and materialized views
Area SQL General / JSON
Contributor Beda Hammerschmidt (Oracle)
Created Tuesday February 12, 2019

Statement 1
some initial cleanup, this may not bee needed if you start the session new.
```
drop table plan_table
```
Statement 2
```
drop materialized view fast_mv
```
Statement 3
```
drop materialized view mat_v1
```
Statement 4
```
drop table zoo
```

Statement 5

we create this table just to show an execution plan.

create table PLAN_TABLE ( 
        statement_id       varchar2(30), 
        plan_id            number, 
        timestamp          date, 
        remarks            varchar2(4000), 
        operation          varchar2(30), 
        options            varchar2(255), 
        object_node        varchar2(128), 
        object_owner       varchar2(30), 
        object_name        varchar2(30), 
        object_alias       varchar2(65), 
        object_instance    numeric, 
        object_type        varchar2(30), 
        optimizer          varchar2(255), 
        search_columns     number, 
        id                 numeric, 
        parent_id          numeric, 
        depth              numeric, 
        position           numeric, 
        cost               numeric, 
        cardinality        numeric, 
        bytes              numeric, 
        other_tag          varchar2(255), 
        partition_start    varchar2(255), 
        partition_stop     varchar2(255), 
        partition_id       numeric, 
        other              long, 
        distribution       varchar2(30), 
        cpu_cost           numeric, 
        io_cost            numeric, 
        temp_space         numeric, 
        access_predicates  varchar2(4000), 
        filter_predicates  varchar2(4000), 
        projection         varchar2(4000), 
        time               numeric, 
        qblock_name        varchar2(30), 
        other_xml          clob 
)

Statement 6
We create a new table 'zoo' to stor JSON data about animals. Please not that each animal (JSON) can be identified by a primary key. This is later important for fast refresh materialized views.
```
create table zoo (id number primary key, jcol varchar2(4000) check (jcol is json))
```

Statement 7

Here comes the 'content' of our zoo: some animals.

insert into zoo values (1, '{"name":"Flipper", "species":"dolphin", "food":"fish"}')

Statement 8

insert into zoo values (2, '{"name":"Lassie", "species":"dog", "food":"Friskies"}')

Statement 9

insert into zoo values (3, '{"name":"Findus", "species":"cat", "food":["Meow","Mix"]}')

Statement 10

insert into zoo values (4, '{"name":"Melman", "species":"giraffe", "height": 4}')

Statement 11

insert into zoo values (5, '{"name":"Leo","species":"Lion","food":["Giraffes", "Meow Mix"]}')

Statement 12

Bu using JSON_TABLE we can un-nest the JSON_ARRAY and see each food item as a separate column value in its own row.

select id, jt.*
from zoo,
     JSON_TABLE(jcol, '$' 
       columns ("name",
                "species",
                NESTED PATH '$.food[*]'
                 columns("food" path '$'))) jt

Statement 13

This is the same statement but be pick column data types instead of relying on the default of VARCHAR2(4000).

select id, jt.*
from zoo,
     JSON_TABLE(jcol, '$' 
       columns ("name"    VARCHAR2(32) path '$.name',
                "species" VARCHAR2(20) path '$.species',
                NESTED PATH '$.food[*]'
                 columns("food" VARCHAR2(20) path '$'))) jt

Statement 14

Now, with the very same query we create out first materialized view.

create materialized view mat_v1 as 
select id, jt.*
from zoo,
     JSON_TABLE(jcol, '$' 
       columns ("name"    VARCHAR2(32) path '$.name',
                "species" VARCHAR2(20) path '$.species',
                NESTED PATH '$.food[*]'
                 columns("food" VARCHAR2(20) path '$'))) jt

Statement 15
Now we can query the materialized views with a food item in the WHERE clause.
```
select "name" from mat_v1 where "food" = 'Meow Mix'
```

Statement 16

EXPLAIN PLAN SET STATEMENT_ID = 'jsonPlan' FOR  
select "name" from mat_v1 where "food" = 'Meow Mix'

Statement 17

The plan shows that we do a full scan of the entire materialized view - not so good.

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options, object_name, position   
FROM plan_table   
START WITH id = 0 AND statement_id = 'jsonPlan'  
CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 18
But by adding a secondary index on the materialized view's column we can get a better plan.
```
create index mat_view_food_idx on mat_v1("food")
```
Statement 19
```
delete from plan_table
```

Statement 20

EXPLAIN PLAN  SET STATEMENT_ID = 'jsonPlan'  FOR  
select "name" from mat_v1 where "food" = 'Meow Mix'

Statement 21

No we see that the same query is using the index.

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options, object_name, position   
FROM plan_table   
START WITH id = 0 AND statement_id = 'jsonPlan'  
CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 22

Let's insert a new animal and see what the effect on the materialized view is ....

insert into zoo values (6, '{"name":"Laika","species":"Space dog","food":"dehydrated meat"}')

Statement 23
```
commit
```
Statement 24
We do not see the new animal - not good.
```
select "name" from mat_v1
```
Statement 25
we need to explicitly refresh the view - which is expensive because we not only execute JSON_TABLE on the new JSON data but on all rows in the base table - that is too slow, we need to do something better!
```
exec DBMS_MVIEW.REFRESH('MAT_V1', 'C', '', TRUE, FALSE, 0,0,0, FALSE, FALSE)
```
Statement 26
```
select "name" from mat_v1
```
Statement 27
```
drop materialized view mat_v1
```
Statement 28
In order to have a 'fast refresh' materialized view we need a 'log' on the table. This log tells us which rows are new and which have been updated. They rely on the base table's primary key or row id - which must be part of the select list of the JSON_TABLE query that defines the view.
```
create materialized view log on zoo
```

Statement 29

No we build a materialized view with fast refresh ON COMMIT option. 'on commit' requires a commit to make the changes in the base table visible to the materialized view. If you want to make your changes visible immediately then use ON STATEMENT instead of ON COMMIT.

create materialized view fast_mv
build immediate
refresh fast
on commit
as
select id, jt.*
from zoo,
     JSON_TABLE(jcol, '$' 
       columns ("name"    VARCHAR2(32) path '$.name',
                "species" VARCHAR2(20) path '$.species',
                NESTED PATH '$.food[*]'
                 columns("food" VARCHAR2(20) path '$'))) jt

Statement 30
again - we create a b-tree index on the views 'food' column.
```
create index fast_mv_food_idx on fast_mv("food")
```

Statement 31

select "name" from  fast_mv where "food" = 'roots'

Statement 32

Now if we insert a new animal and commit the change then the view should reflect if without us having to refresh the view explicitly.

insert into zoo values (7,
'{"name":"Dumbo", "species":"Elephant", "food":["roots", "tree bark"], 
  "descr":"An elephant has a long nose that serves as straw. Current bans on plastic straws will most likely not apply."}')

Statement 33
```
commit
```

Statement 34

select "name" from  fast_mv where "food" = 'roots'

Statement 35
```
delete from plan_table
```

Statement 36

EXPLAIN PLAN  SET STATEMENT_ID = 'jsonPlan'  FOR  
select "name" from fast_mv where "food" = 'roots'

Statement 37

Plan shows that we're using the index.

SELECT LPAD('............................',2*(LEVEL-1))||operation operation, options, object_name, position   
FROM plan_table   
START WITH id = 0 AND statement_id = 'jsonPlan'  
CONNECT BY PRIOR id = parent_id AND statement_id = 'jsonPlan'

Statement 38
Let's do an update by assigning a food to our giraffe - Melman. We use JSON_MERGEPATCH for this.
```
update zoo set jcol =
JSON_MERGEPATCH(jcol, '{"food":"Acacia"}') 
where id = 4
```
Statement 39
```
commit
```
Statement 40
And we see the the update got reflected in the view.
```
select "name" from  fast_mv where "food" = 'Acacia'
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database