Skip to Main Content
  • Questions
  • Query rewrites on MVIEW using JSON DATA

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajeshwaran.

Asked: January 07, 2021 - 5:21 am UTC

Last updated: January 07, 2021 - 3:32 pm UTC

Version: 19.9.0

You Asked

Team:

This below testcase was run on Oracle 19.9 database.

q1) why the online stats gathering feature doesn't kick in post the direct path load on "twitter_data" table?
q2) could you please help us to understand though we have Mview inplace, why the optimizer is not performing query rewrites?

demo@QES1> select banner_full from v$version;

BANNER_FULL
-------------------------------------------------------------------------
---
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

demo@QES1> create table twitter_data( doc_id number,
  2                  twit clob,
  3                  constraint twitter_data_pk primary key(doc_id),
  4                  constraint twitter_data_chk
  5                  check( twit is json) );

Table created.

demo@QES1> declare
  2  l_sql long;
  3  begin
  4       l_sql := q'# {
  5        "text": "RT @PostGradProblem: In preparation for the NFL lockout, I will be spending twice as much time analyzing my fantasy baseball team during ...",
  6        "truncated": true,
  7        "in_reply_to_user_id": null,
  8        "in_reply_to_status_id": null,
  9        "favorited": false,
 10        "source": "<a href=\"http://twitter.com/\" rel=\"nofollow\">Twitter for iPhone</a>",
 11        "in_reply_to_screen_name": null,
 12        "in_reply_to_status_id_str": null,
 13        "id_str": "54691802283900928",
 14        "entities": {
 15              "user_mentions": [
 16                    {
 17                          "indices": [
 18                                3,
 19                                19
 20                          ],
 21                          "screen_name": "PostGradProblem",
 22                          "id_str": "271572434",
 23                          "name": "PostGradProblems",
 24                          "id": 271572434
 25                    }
 26              ],
 27              "urls": [ ],
 28              "hashtags": [ ]
 29        },
 30        "contributors": null,
 31        "retweeted": false,
 32        "in_reply_to_user_id_str": null,
 33        "place": null,
 34        "retweet_count": 4,
 35        "created_at": "Sun Apr 03 23:48:36 +0000 2011",
 36        "user": {
 37              "notifications": null,
 38              "profile_use_background_image": true,
 39              "statuses_count": 351,
 40              "profile_background_color": "C0DEED",
 41              "followers_count": 48,
 42              "profile_image_url": "http://a1.twimg.com/profile_images/455128973/gCsVUnofNqqyd6tdOGevROvko1_500_normal.jpg",
 43              "listed_count": 0,
 44              "profile_background_image_url": "http://a3.twimg.com/a/1300479984/images/themes/theme1/bg.png",
 45              "description": "watcha doin in my waters?",
 46              "screen_name": "OldGREG85",
 47              "default_profile": true,
 48              "verified": false,
 49              "time_zone": "Hawaii",
 50              "profile_text_color": "333333",
 51              "is_translator": false,
 52              "profile_sidebar_fill_color": "DDEEF6",
 53              "location": "Texas",
 54              "id_str": "##x1##",
 55              "default_profile_image": false,
 56              "profile_background_tile": false,
 57              "lang": "en",
 58              "friends_count": 81,
 59              "protected": false,
 60              "favourites_count": 0,
 61              "created_at": "Tue Oct 06 01:13:17 +0000 2009",
 62              "profile_link_color": "0084B4",
 63              "name": "GG",
 64              "show_all_inline_media": false,
 65              "follow_request_sent": null,
 66              "geo_enabled": false,
 67              "profile_sidebar_border_color": "C0DEED",
 68              "url": "##x2##",
 69              "id": 80177619,
 70              "contributors_enabled": false,
 71              "following": null,
 72              "utc_offset": -36000
 73        },
 74        "id": 54691802283900930,
 75        "coordinates": null,
 76        "geo": null
 77  } #';
 78  insert /*+ append */ into twitter_data(doc_id, twit )
 79  select rownum,replace( replace( l_sql, '"##x1##"', object_id ), '"##x2##"', '"'||substr( object_name,1,20)||'"' )
 80  from all_objects , all_users
 81  where rownum <=1000000;
 82  commit;
 83  end;
 84  /

PL/SQL procedure successfully completed.

demo@QES1> select num_rows,blocks,empty_blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='TWITTER_DATA';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANALYZ
---------- ---------- ------------ -----------


demo@QES1> exec dbms_stats.gather_table_stats(user,'TWITTER_DATA');

PL/SQL procedure successfully completed.

demo@QES1> select num_rows,blocks,empty_blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='TWITTER_DATA';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANALYZ
---------- ---------- ------------ -----------
   1000000       6796            0 21-DEC-2020
   
demo@QES1> create materialized view mv_twit
  2  build immediate
  3  refresh fast on statement
  4  enable query rewrite
  5  as
  6  select doc_id,Jt.*
  7  from twitter_data, json_table( twit, '$.user'
  8      columns(
  9          id_str number path '$.id_str' error on error null on empty,
 10          url varchar2(20) path '$.url' error on error null on empty,
 11          got_geo varchar2(10) path '$.geo_enabled' error on error null on empty,
 12          friends_cnt number path '$.friends_count' error on error null on empty
 13                                  )) jt
 14  /

Materialized view created.

demo@QES1> select num_rows,blocks,empty_blocks,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='MV_TWIT';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS LAST_ANALYZ
---------- ---------- ------------ -----------
   1000000       5937            0 21-DEC-2020

demo@QES1> set autotrace traceonly exp
demo@QES1> select t.twit."user".id_str id_str,
  2         t.twit."user".url url,
  3         t.twit."user".geo_enabled got_geo,
  4         t.twit."user".friends_count cnt
  5  from twitter_data t
  6  where t.twit."user".id_str ='360';

Execution Plan
----------------------------------------------------------
Plan hash value: 2996362279

---------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |    81M|    10G|    27M  (1)| 00:17:45 |
|   1 |  NESTED LOOPS          |              |    81M|    10G|    27M  (1)| 00:17:45 |
|   2 |   TABLE ACCESS FULL    | TWITTER_DATA |  1000K|   126M|  1849   (1)| 00:00:01 |
|*  3 |   JSONTABLE EVALUATION |              |       |       |            |          |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("P"."C_04$"='360')

demo@QES1> select doc_id,Jt.*
  2  from twitter_data, json_table( twit, '$.user'
  3      columns(
  4          id_str number path '$.id_str' error on error null on empty,
  5          url varchar2(20) path '$.url' error on error null on empty,
  6          got_geo varchar2(10) path '$.geo_enabled' error on error null on empty,
  7          friends_cnt number path '$.friends_count' error on error null on empty
  8                                  )) jt
  9  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2996362279

---------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |  8168M|  1110G|    27M  (1)| 00:17:42 |
|   1 |  NESTED LOOPS          |              |  8168M|  1110G|    27M  (1)| 00:17:42 |
|   2 |   TABLE ACCESS FULL    | TWITTER_DATA |  1000K|   131M|  1849   (1)| 00:00:01 |
|   3 |   JSONTABLE EVALUATION |              |       |       |            |          |
---------------------------------------------------------------------------------------

demo@QES1> set autotrace off
demo@QES1>
demo@QES1>
demo@QES1> begin
  2      dbms_mview.Explain_Rewrite(query=>q'# select t.twit."user".id_str id_str,
  3         t.twit."user".url url,
  4         t.twit."user".geo_enabled got_geo,
  5         t.twit."user".friends_count cnt
  6  from twitter_data t
  7  where t.twit."user".id_str ='360' #');
  8  end;
  9  /

PL/SQL procedure successfully completed.

demo@QES1> select sequence,message,pass from rewrite_table;

  SEQUENCE MESSAGE                        PASS
---------- ------------------------------ ----------
         1 QSM-01150: query did not rewri NO
           te

         2 QSM-01263: query rewrite not p YES
           ossible when query references
           a dictionary table or view

demo@QES1> show parameter query_rewrite_enab

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
query_rewrite_enabled                string      TRUE
demo@QES1>

and we said...

1. The insert is in conventional mode, not direct-path!

The presence of the JSON check constraint is causing this. You can validate this by trying to query the table after the insert. If it used direct-path, you'll get an error; with conventional it works:

create table twitter_data ( 
  doc_id number,
  twit clob,
  constraint twitter_data_pk primary key(doc_id)
);

insert /*+ append_values */into twitter_data 
  values ( 1, '{ test : "test" }' );
  
select * from twitter_data;

ORA-12838: cannot read/modify an object after modifying it in parallel

commit;

alter table twitter_data add
  constraint twitter_data_chk
  check ( twit is json );
  
insert /*+ append_values */into twitter_data 
  values ( 2, '{ test : "test" }' );
  
select * from twitter_data;

DOC_ID   TWIT                
        1 { test : "test" }    
        2 { test : "test" } 

commit;


2. The dot-notation query has this where clause

t.twit."user".id_str ='360'


The JSON_table expression declares id_str as a number. So there's a character <> numeric comparison => implicit conversion!

This is causing the rewrite to fail somewhere. Use the correct data types it does use the MV:

set serveroutput off

select t.twit."user".id_str id_str,
       t.twit."user".url url,
       t.twit."user".geo_enabled got_geo,
       t.twit."user".friends_count cnt
from twitter_data t
where t.twit."user".id_str = '360'; -- implicit conversion

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

-----------------------------------------------                             
| Id  | Operation              | Name         |                             
-----------------------------------------------                             
|   0 | SELECT STATEMENT       |              |                             
|   1 |  NESTED LOOPS          |              |                             
|   2 |   TABLE ACCESS FULL    | TWITTER_DATA |                             
|   3 |   JSONTABLE EVALUATION |              |                             
-----------------------------------------------

select t.twit."user".id_str id_str,
       t.twit."user".url url,
       t.twit."user".geo_enabled got_geo,
       t.twit."user".friends_count cnt
from twitter_data t
where t.twit."user".id_str = 360;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

------------------------------------------------------------------          
| Id  | Operation                                | Name          |          
------------------------------------------------------------------          
|   0 | SELECT STATEMENT                         |               |          
|   1 |  NESTED LOOPS                            |               |          
|   2 |   NESTED LOOPS SEMI                      |               |          
|   3 |    TABLE ACCESS FULL                     | TWITTER_DATA  |          
|   4 |    MAT_VIEW ACCESS BY INDEX ROWID BATCHED| MV_TWIT       |          
|   5 |     INDEX RANGE SCAN                     | I_OS$_MV_TWIT |          
|   6 |   JSONTABLE EVALUATION                   |               |          
------------------------------------------------------------------

Rating

  (1 rating)

Comments

Thanks.

Rajeshwaran, Jeyabal, January 08, 2021 - 12:12 pm UTC

Thanks that helps.
1) was aware the presece of FK constaints, will silently supress direct path load, nice to know the even check constaint does that.
2) thanks for pointing out that implicit conversion.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.