Oracle with as (hint inline materialize)
发表于:2025-02-23 作者:千家信息网编辑
千家信息网最后更新 2025年02月23日,测试数据:SQL> create table test1 as select * from dba_objects;Table created.SQL> create table test2 as
千家信息网最后更新 2025年02月23日Oracle with as (hint inline materialize)
测试数据:
SQL> create table test1 as select * from dba_objects;Table created.SQL> create table test2 as select * from user_objects;Table created.SQL> create table test3 as select * from dba_objects;Table created.
收集统计信息:
BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', tabname => 'TEST1', estimate_percent => 100, method_opt => 'for columns owner size repeat', no_invalidate => FALSE, degree => 4, granularity => 'ALL', cascade => TRUE); END; /PL/SQL procedure successfully completed.SQL> with t as(select t1.* from test1 t1,test2 t2 where t1.object_id=t2.object_id) select * from t,test3 t3 where t.object_id=t3.object_id;11 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2878150729-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 86999 | 16M| | 1605 (1)| 00:00:20 ||* 1 | HASH JOIN | | 86999 | 16M| | 1605 (1)| 00:00:20 || 2 | TABLE ACCESS FULL | TEST2 | 13 | 65 | | 3 (0)| 00:00:01 ||* 3 | HASH JOIN | | 86999 | 16M| 9352K| 1602 (1)| 00:00:20 || 4 | TABLE ACCESS FULL| TEST1 | 86997 | 8325K| | 347 (1)| 00:00:05 || 5 | TABLE ACCESS FULL| TEST3 | 86999 | 8326K| | 347 (1)| 00:00:05 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 3 - access("T1"."OBJECT_ID"="T3"."OBJECT_ID")Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 2491 consistent gets 2484 physical reads 0 redo size 3736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 11 rows processed 加 materialize hint,强制oracle生成临时表SQL> with t as(select /*+ materialize */t1.* from test1 t1,test2 t2 where t1.object_id=t2.object_id) select * from t,test3 t3 where t.object_id=t3.object_id;11 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3288461629----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 582M| 165G| | 3963 (40)| 00:00:48 || 1 | TEMP TABLE TRANSFORMATION | | | | | | || 2 | LOAD AS SELECT | SYS_TEMP_0FD9D661A_155646 | | | | | ||* 3 | HASH JOIN | | 86997 | 8750K| | 351 (1)| 00:00:05 || 4 | TABLE ACCESS FULL | TEST2 | 13 | 65 | | 3 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | TEST1 | 86997 | 8325K| | 347 (1)| 00:00:05 ||* 6 | HASH JOIN | | 582M| 165G| 9352K| 3613 (44)| 00:00:44 || 7 | TABLE ACCESS FULL | TEST3 | 86999 | 8326K| | 347 (1)| 00:00:05 || 8 | VIEW | | 86997 | 17M| | 332 (1)| 00:00:04 || 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661A_155646 | 86997 | 8750K| | 332 (1)| 00:00:04 |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID") 6 - access("T"."OBJECT_ID"="T3"."OBJECT_ID")Statistics---------------------------------------------------------- 55 recursive calls 8 db block gets 2525 consistent gets 2485 physical reads 656 redo size 3736 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 11 rows processed
信息
数据
强制
测试
生成
统计
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发项目交付不了
玩中国服务器都能叫国服吗
未未互联网科技有限公司
中专学校网络技术试卷答案
网络安全词语是什么
obd软件开发是啥
数据库in是什么
阿里云服务器怎么下载mysql
数据库开启到用户模式
网络服务器需要考虑哪些问题
黑马程序员讲的数据库怎么样
高中网络技术选修课时分部
网络安全手抄报小图画
简单的网络安全的手抄报
数据库安全审计软件
上海java软件开发平台
澳客网络技术有限公司
软件开发需要买mac吗
终结者手游服务器搭配
服务器刷矿机
软件测试的面试数据库
郓城 网络安全
郧阳区信息软件开发学习
描述软件开发阶段的工具
数据库in是什么
南宁千里传网络技术有限公司
荆门网络技术联系方式
大白菜给服务器装系统
广东版式文件服务器地址
wpf连接数据库