问题描述
团队:
下面的测试用例是在Oracle 19.9数据库上运行的。
q1) 为什么在线统计信息收集功能没有在 “twitter_data” 表中发布直接路径加载？
q2) 您能否帮助我们了解尽管我们有Mview，但优化器为什么不执行查询重写？
下面的测试用例是在Oracle 19.9数据库上运行的。
q1) 为什么在线统计信息收集功能没有在 “twitter_data” 表中发布直接路径加载？
q2) 您能否帮助我们了解尽管我们有Mview，但优化器为什么不执行查询重写？
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": "Twitter for iPhone",
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> 专家解答
1.插入件处于常规模式，而不是直接路径!
JSON检查约束的存在导致这您可以通过尝试查询插入后的表来验证这一点。如果它使用直接路径，你会得到一个错误; 与传统的工作原理:
2.点符号查询有这个where子句
JSON_table表达式将id_str声明为一个数字。所以有一个字符 <> 数值比较 => 隐式转换!
这导致重写在某个地方失败。使用正确的数据类型它确实使用MV:
JSON检查约束的存在导致这您可以通过尝试查询插入后的表来验证这一点。如果它使用直接路径，你会得到一个错误; 与传统的工作原理:
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.点符号查询有这个where子句
t.twit."user".id_str ='360'
JSON_table表达式将id_str声明为一个数字。所以有一个字符 <> 数值比较 => 隐式转换!
这导致重写在某个地方失败。使用正确的数据类型它确实使用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 | |
------------------------------------------------------------------ 文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
