12C标量子查询中的BUG
来源:互联网 发布:武汉网络推广外包 编辑:程序博客网 时间:2024/06/09 14:02
在12C中,cbo会自动改写标量子查询了:
create table test as select * frm dba_objects;create table test3 as select * from test;
select (select max(DATA_OBJECT_ID) from test b where b.object_id=a.object_id ) from test3 a;
该语句在11G中的plan为
Plan Hash Value : --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 94599 | 1229787 | 347 | 00:00:05 || 1 | SORT AGGREGATE | | 1 | 26 | | || * 2 | TABLE ACCESS FULL | TEST | 870 | 22620 | 347 | 00:00:05 || 3 | TABLE ACCESS FULL | TEST3 | 94599 | 1229787 | 347 | 00:00:05 |--------------------------------------------------------------------------Predicate Information (identified by operation id):------------------------------------------* 2 - filter("B"."OBJECT_ID"=:B1)
而在12C中被CBO改写成了left join
如果把max改成rownum <=1,cbo不会进行改写
如果同时用了max与rownum<=1 就会出现错误的plan和结果
SQL> explain plan for select (select max(b.DATA_OBJECT_ID) from test b where b.object_id=a.object_id and rownum=1) from test3 a;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------Plan hash value: 730023338-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 91583 | 2772K| 412 (1)| 00:00:01 ||* 1 | HASH JOIN RIGHT OUTER | | 91583 | 2772K| 412 (1)| 00:00:01 || 2 | VIEW | VW_SSQ_1 | 1 | 26 | 2 (50)| 00:00:01 || 3 | HASH GROUP BY | | 1 | 7 | 2 (50)| 00:00:01 ||* 4 | COUNT STOPKEY | | | | | || 5 | TABLE ACCESS INMEMORY FULL| TEST | 1 | 7 | 1 (0)| 00:00:01 || 6 | TABLE ACCESS FULL | TEST3 | 91583 | 447K| 410 (1)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("ITEM_1"(+)="A"."OBJECT_ID") 4 - filter(ROWNUM=1)19 rows selected.
当然,几乎没人同时用max和rownum的
0 0
- 12C标量子查询中的BUG
- 标量子查询
- 标量子查询
- 标量子查询
- 标量子查询
- sql 标量子查询
- 标量子查询
- oracle标量子查询
- 标量子查询改写
- 经典的标量子查询
- 优化mysql标量子查询
- 标量子查询SQL改写
- 用分析函数优化标量子查询
- 用分析函数去掉标量子查询
- 标量子查询优化外连接
- 不等值标量子查询改写
- SQL优化-标量子查询的改写
- MySQL:内联视图与标量子查询
- zoj3878Convert QWERTY to Dvorak(打表)
- iOS将数组中的内容分拼接成字符串
- 访问者模式
- Linux下xargs命令详解
- HDU 5410 CRB and His Birthday(01背包+完全背包)
- 12C标量子查询中的BUG
- 简单封装的httpclient
- 二叉树遍历求差值绝对值最大值
- C++中虚函数与晚绑定的编译器实现
- MySQL 调优/优化的 100 个建议
- 单例设计模式
- asoundrc配置文件简单介绍
- KM算法板子2
- Hoeffding不等式的证明