FILTER的执行次数和驱动表问题
2024-10-15 21:37:20
drop table test1;
create table test1 as select * from dba_objects where rownum<1000; drop table test2;
create table test2 as select * from dba_objects where rownum<1000; insert into test1 select * from test1; update test2 set owner='SCOTT'; BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST1',
estimate_percent => 100,
method_opt => 'for columns owner size 200',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/ BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST2',
estimate_percent => 100,
method_opt => 'for columns owner size 200',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/ SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------
SQL_ID a23r1uchdaafg, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found Plan hash value: 1896454807 --------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1998 |00:00:00.07 | 13763 |
|* 1 | FILTER | | 1 | | 1998 |00:00:00.07 | 13763 |
| 2 | TABLE ACCESS FULL| TEST1 | 1 | 999 | 1998 |00:00:00.01 | 163 |
|* 3 | TABLE ACCESS FULL| TEST2 | 1346 | 1 | 1346 |00:00:00.06 | 13600 |
-------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter(("OWNER"='SCOTT' OR IS NOT NULL))
3 - filter(("OWNER"='SCOTT' AND "OBJECT_ID"=:B1)) 21 rows selected. 查看FILTER的执行次数,此时 被驱动表TEST2执行了1346次,那这个1346次是怎么计算的呢？ SQL> select count(*) from test1; COUNT(*)
----------
1998 SQL> select count(distinct object_id) from test1; COUNT(DISTINCTOBJECT_ID)
------------------------
999 网上有人说是count(distinct join列),看来结果不对,有待研究 FILTER能改变驱动表？
SQL> select /*+ leading(test2)*/ * from test1 where owner='SCOTT' or object_id in(select object_id from test2 where owner='SCOTT')
2 ; 1998 rows selected. Execution Plan
----------------------------------------------------------
Plan hash value: 1896454807 ----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 4420 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 999 | 84915 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEST2 | 1 | 18 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------- FILTER无法改变驱动表 1. 减少扫描 次数 2. 无法减少扫描次数，减少扫描体积
最新文章
- 有时打开myeclipse，部署报错解决方案
- 在 ML2 中 enable local network - 每天5分钟玩转 OpenStack（79）
- IT行业的技术类岗位分为许多种，如何判断自己适合哪种？
- 转载：CDH5.X完全卸载步骤
- 将 project.json 项目转换为 Visual Studio 2015 解决方案
- SAP查询事务、表属于哪个模块
- 转 #ifndef/#define/#endif使用详解
- 我们来八一八阿里云OS的实质和历史
- Mysql学习笔记（四）聊聊数据库索引
- 【leetcode】Substring with Concatenation of All Words (hard) ★
- Android 反编译
- The Magic only works with total devotion of one's heart
- Extjs4.2如何实现鼠标点击统计图时弹出窗口来展示统计的具体列表信息
- leetcode之反转链表
- Adobe Edge Animate –弹性的方块-使用tweenmax缓动效果
- Import Items – Validation Multiple Languages Description
- 输出内容(document.write)
- Oracle 生成指定范围内随机日期
- [国嵌笔记][028][Bootloader设计蓝图]
- CF 741D. Arpa’s letter-marked tree and Mehrdad’s Dokhtar-kosh paths [dsu on tree 类似点分治]